HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Usage of SUBQUERY_PRUNING undocumented hint
ORACLE STUFFS
20-Sep-2011
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

Syntax:

SUBQUERY_PRUNING(<pruned table alias> PARTITION)

or 

SUBQUERY_PRUNING(<pruned table alias> SUBPARTITION)

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