11gR2 : Different use of PARALLELISM
In 11gR2, I would like to optimize a well-tuned query like:
SQL > INSERT INTO T1 SELECT ... others SELECT ...;
=> 30 min.
1/ By using parallelism in table DDL (table level).
SQL > ALTER TABLE T1 PARALLEL ;
SQL > INSERT INTO T1 SELECT ... others SELECT ...;
=> 5 min !
1/ By using hint in INSERT and in all SELECT of the query :
SQL> ALTER session ENABLE PARALLEL DML;
SQL > INSERT /*+ PARALLEL(AUTO) */ INTO T1 SELECT /*+ PARALLEL(AUTO) */ ... others SELECT /*+ PARALLEL(AUTO) */ ...;
=> 20 min only !
Why this difference with parallelism at table level ?
Best regards.
GD.