Today I had to speed up a huge single unloading (in my case it was “insert/*+append*/ select”, but it is not that important in this context): a big non-partitioned table, fairly good selectivity by index ( ~1.2%), over 95% of time is spent on the “lookup” from index to the table. Index size is 44Gb…
The initial query was:
select t_big.* from t_big where t_big.a=:a
Naturally enough I wanted to get it paralleled, but “index range scan” on non-partitioned index and table does not work in parallel, that’s why I had to use a little trickery, which allowed to increase the execution speed by more than 13 times with DOP = 16.
The method is simple: as index reading is quite fast, we can just read and materialize rowids through IRS, and after that we go paralleled to the table using TABLE ACCESS BY USER ROWID.
The final query:
with rids as( select--+ materialize rowid rid from t_big t1 where t1.a=:a ) select/*+ use_nl(rids t_big) parallel(16) */ t_big.* from t_big, rids where t_big.rowid=rids.rid
Creating constrains in parallel:
The newly created constraints are validated unparalleled by default, which significantly slows the process. Especially considering that this is usually happening during technological breaks when lots of resources are available. This problem is solved easily: you have to create it with “enable novalidate”, and then turn on “enable validate” separately:
UPD: I have made a replacement with “enable novalidate”, for as Timur Akhmadeev fairly noted, “disable” would not work with “unique” and “foreign key”, but only with “check constraints”.