Translate

Search This Blog

deciding execution plan when query is transformed.

Oracle 10g brought cost based query transformation. Consider two below cases and decide what plan of COST based optimizer is good.  Is transformation it is considering is good.

INDEX IDX_HIST_ID ON HISTDATA(ID)

exec :b1:=1

SELECT  /*+ use_nl (A B ) */  MAX (id)
                         FROM histdata A ,  sustained_feed b
                      WHERE A.id=b.id
                        AND b. data_bucket = :B1;

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   1779                    
  SORT AGGREGATE 1   10                    
    NESTED LOOPS 610 K 5 M 1779                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      FIRST ROW 35 K 105 K 104                    
        INDEX RANGE SCAN (MIN/MAX) MT_RPT.IDX_HIST_ID 35 K 105 K 104                    


SELECT    MAX (id)
                          FROM histdata
                         WHERE id IN (SELECT  id
                                                   FROM sustained_feed
                                                  WHERE data_bucket = :B1);


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1   872                    
  SORT AGGREGATE 1   10                    
    HASH JOIN 610 K 5 M 872                    
      TABLE ACCESS FULL MT_RPT.sustained_feed 17   119   3                    
      INDEX FAST FULL SCAN MT_RPT.IDX_HIST_ID 933 K 2 M 759                

Follow by Email