Re: why CTAS faster than Insert select?

Connor,

Yes, the Insert is in /*+ APPEND */ mode. When you force session PDML, it seems that it does INSERTs in APPEND mode automatically. I did test manually putting /*+ APPEND */ as well, it doesn't make a difference in terms of timing when comparing to CTAS. I used the runstats_pkg to measure the latch difference, it's very comparable (96%), but response time almost double. The execution plan also the same (hash joins, index FFS). Some how I guess Oracle is executing different code path for CTAS and Insert /*+ APPEND */ as select. Even though it's all parallelized and nologging, one path is significantly faster than the other. If it's really true, then I think Oracle should optimize the path for Insert Select as well.

Thanks,
Linda Received on Wed Mar 30 2005 - 14:06:05 CST