Adjuk meg azon B katergóriás vevők nevét, akik egy adott időszakban vettek valamit.

Futási idő: 0,9 s   use_merge: 1,4 s, use_nl: sok
-----------------
SELECT /*+ full(c) full(s) use_hash(c s) */   
  DISTINCT c.cust_first_name||' - '||c.cust_last_name, cust_income_level
FROM     sh.customers c, sh.sales s
WHERE    c.cust_income_level LIKE 'B%'
AND      s.cust_id = c.cust_id
AND      s.time_id + 1 BETWEEN to_date('1998.01.01', 'yyyy.mm.dd')  AND to_date('2002.01.01', 'yyyy.mm.dd')
ORDER BY 1;

Nested loop-pal kivárhatatlan. Ha az intervallumot 10 napra csökkentjük: NL -> 36 s
Ha az s.time_id + 1 helyett s.time_id szerepel: NL -> 21 s (nem kell az összes partíciót néznie)


Ugyanez semi-join-nal: 
----------------------
SELECT /*+ full(c) */   DISTINCT c.cust_first_name||' - '||c.cust_last_name, cust_income_level
FROM  sh.customers c 
WHERE c.cust_income_level LIKE 'B%'
AND   EXISTS
  (SELECT /*+ Hash_SJ full(s) */ 1 FROM sh.sales s WHERE s.cust_id = c.cust_id
   AND s.time_id + 1 BETWEEN to_date('1998.01.01', 'yyyy.mm.dd') AND to_date('2002.01.01', 'yyyy.mm.dd'))
ORDER BY 1;

Nested Loop-pal ez is kivárhatatlan.


Másik lekérdezés AntiJoin-ra:
-----------------------------

explain plan set statement_id='NL_AJ' for
SELECT /*+ full(c) */   DISTINCT c.cust_first_name||' - '||c.cust_last_name, cust_income_level
FROM  sh.customers c 
WHERE c.cust_income_level LIKE 'B%'
AND   c.cust_id + 1 NOT IN
  (SELECT /*+ nl_aj full(s) */ s.cust_id + 1 FROM sh.sales s 
   WHERE s.time_id + 1 BETWEEN to_date('1998.01.01', 'yyyy.mm.dd') AND to_date('2000.05.10', 'yyyy.mm.dd'))
ORDER BY 1;


SELECT SUBSTR(LPAD(' ', 2*(LEVEL-1))||operation||' + '||options||' + '||object_name, 1, 50) terv,
  cost, cardinality, bytes, io_cost, cpu_cost
FROM plan_table
START WITH ID = 0 AND STATEMENT_ID = 'NL_AJ'                 -- az utasítás neve szerepel itt
CONNECT BY PRIOR id = parent_id AND statement_id = 'NL_AJ'   -- meg itt
ORDER SIBLINGS BY position;


TERV                                               COST   CARDINALITY BYTES    IO_COST CPU_COST               
-------------------------------------------------- ------ ----------- -------- ------- -------------- 
SELECT STATEMENT +  +                              467353 8849        477846   428311  8141437119259          
  SORT + ORDER BY +                                467353 8849        477846   428311  8141437119259          
    HASH + UNIQUE +                                467352 8849        477846   428311  8141411038504          
      NESTED LOOPS + ANTI +                        467352 8849        477846   428311  8141384957748          
        TABLE ACCESS + FULL + CUSTOMERS            406    8939        366499   404     39997460               
        PARTITION RANGE + ALL +                    523    23          299      479     910766860              
          TABLE ACCESS + FULL + SALES              523    23          299      479     910766860              


Ugyanez HASH_AJ hinttel:

TERV                                               COST   CARDINALITY BYTES    IO_COST CPU_COST               
-------------------------------------------------- ------ ----------- -------- ------- -------------- 
SELECT STATEMENT +  +                              934    8849        477846   885     1014591000             
  SORT + ORDER BY +                                934    8849        477846   885     1014591000             
    HASH + UNIQUE +                                932    8849        477846   885     988510245              
      HASH JOIN + RIGHT ANTI +                     931    8849        477846   885     962429490              
        PARTITION RANGE + ALL +                    525    2297        29861    481     910766860              
          TABLE ACCESS + FULL + SALES              525    2297        29861    481     910766860              
        TABLE ACCESS + FULL + CUSTOMERS            406    8939        366499   404     39997460               



A fenti két tervből látható, hogy a költségek legnagyobb részét az IO költségek adják.
Az is látható, hogy a NL join nagyon rossz a HJ-hoz képest.