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 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
However, inserting in partitioned IOT's is quite different. There are
- 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 18.104.22.168 on 64 bit HP-UX.
Thanks in advance.