Re: why CTAS faster than Insert select?

linda wrote:
> Hi,
>
> I've been testing the performance between Create Table As Select
(CTAS)
> vs Insert Select for large volume of data (40 million records or so).
> Both in nologging mode, parallel, etc. It consistently shows that
CTAS
> faster than Insert Select. My understanding is that, if you parallel
> both create/insert process and select process, then theoratically
they
> should perform about the same (same amount of work, same number of
> parallel processes, minimum redo and undo...). Could anybody explain
> why this is the case?

I've consistently seen this, even across platforms, versions and links (ie CTAS from unix O8i to Windows O9i vs. ftp + exp direct/imp). I speculate the reason is Oracle can figure out a lot of structural things (like, "Which block do I use? Do I have enough room in this block? Do I have to extend the segment? Are there dirty buffers?") from the original table, that becomes much more overhead when inserting. Sometimes even orders of magnitude differences.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20050329/news_1b29kay.html