Usage of SUBQUERY_PRUNING undocumented hint

DATA ARCHITECTURE

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

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