Friends,
I have a question regarding tuning "insert .. as select" statment on 12.1.
source table (paritioned) has 80million records . The above statment
selects the records from source table ,does some little formatting/massaging
and then inserts into target table which again is partitioned . Finally , close
to 80million records will need to be inserted into the target table.
we tuned the SELECT stmt and that alone gets done in 15 mins. we are okay with
that. But when it works along with "INSERT..as SELECT" , the INSERT happens
slow. Takes about 2~3 hours (Disbaled all the indexes , added nologging etc.).
The target table is partitoined such that all these 80M recs go into 1 single
partition.This is 1 day's data. Every day 80m recs will go into other partition
(as it is partitioned on date). FYI : the machine has 50 CPUs and enough CPU is
available for parallel processes.
Tried "insert /*+ append parallel */ hint too . But still takes 2 hrs. Checked
the explain plan . The final "LOAD as SELECT" line does not fall under a "PX
co-ordinator" makes me think , INSERT does not happen parllely. My question,
with just one partition can append happen in parallel ? Would sub-parition help
and make insert happen in parallel ? Or it does not even matter !!!
I am going to try it anyway.But would like hear your expert opinion and the
best way to do it.
Regards,
JP
