NewsCustom SearchContact
Usage of SUBQUERY_PRUNING undocumented hint
    by Ludek Bob Jankovsky, 20-Sep-2011 (ORACLE STUFFS)
There are cases when you want to merge data from small table to large one, where partitioning of target table allows to merge data just into last few partitions. Anyway, to know what partitions should be used you need query didstinst partition keys from source table. Oprimizer usually works itself, but if it doesn't, following hint can help. No more words, just an example:

merge /*+ use_hash(src trg) SUBQUERY_PRUNING(trg PARTITION)*/ 
 into TARGET_TABLE trg
 using SOURCE_TABLE src
    on (trg.PART_KEY = src.PART_KEY
    and trg.ENT_KEY = src.ENT_KEY
 when matched then update set


SUBQUERY_PRUNING(<pruned table alias> PARTITION)



Initial parameters related to the theme (hiden also):

ALTER SESSION SET "_subquery_pruning_enabled"=TRUE;
ALTER SESSION SET "_subquery_pruning_cost_factor"=1;
ALTER SESSION SET "_subquery_pruning_reduction"=100;
ALTER SESSION SET "_bloom_pruning_enabled"=TRUE;

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky