INSERT versus CTAS
- From: Alexander Gorbachev <gorbyx@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 11 Dec 2004 16:41:23 +0100
Hi,
What are the differencies between "create ... table as select" and
"create table" + "insert /*+ parallel(...) append(...) */ into" from
performance point of view?
I am interested in various tables:
- non-partitioned
- partitioned
- non pratitioned IOT
- partitioned IOT
Currently, I use create + insert with parallel append hints. It works
reasonable fast for us - we are able to fill 100+ Gb of data within
1,5 hour.
However, inserting in partitioned IOT's is quite different. There are
few problems:
- slow
- append doesn't work (and, consequently, parallel).
- lot's of redo (coz no append).
- lot's of undo (I guess because no append as well, since with append
you need only revert HWM) and, consequently, ora-1555.
- lot's of temporary space required.
We are able to overcome some problems iterating through each
partition, but it's not a clean solution and performance is still far
from what we target.
I want to change INSERT to CTAS, but it requires some efforts to amend
the script. I assume that CTAS would be more performant for IOT since
it will create index as temporary segment in table's tablespace and
then just amend dictionary (object definition + hwm). So I expect to
eleminate UNDO generation, reduce temporary space requirements, reduce
redo, and.... well, boost performance.
Any comments/links are appreciated.
It's 9.2.0.5 on 64 bit HP-UX.
Thanks in advance.
Alex
--
Best regards,
Alex Gorbachev
--
//www.freelists.org/webpage/oracle-l
Other related posts:
