Odd execution plan

I came across an execution plan earlier that struck me as a little odd.
Maybe I’ve seen this plenty of times before but never noticed.

Here’s the query:

SELECT /*+ dom2 */ * 
FROM   xxxxxxx opcl
WHERE  opcl.cal_date BETWEEN TO_DATE('20100901', 'YYYYMMDD' )
                     AND     TO_DATE('20100902', 'YYYYMMDD' )
AND    opcl.active_flag = 'Y'
AND    opcl.cont_tag   != 0
AND    NOT EXISTS (SELECT 'X'
                   FROM   yyyyyyy  r
                   WHERE  r.inst_num    = opcl.inst_num
                   AND    r.status      = 'R'
                   AND    opcl.cal_date < r.conv_date);

Here’s the plan:

-----------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                    | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |  5597 |   333K|  1034   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID  | XXXXXXXXXXXXXXXXXXX      |  5597 |   333K|  1034   (1)|
|*  2 |   INDEX RANGE SCAN            | XXXXXXXXXXXXXXXXXXX_IDX  |  5597 |       |  1710   (1)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| YYYYYYYYYYYY             |     1 |    16 |     2  (50)|
|*  4 |     INDEX UNIQUE SCAN         | YYYY_PK                  |     1 |       |     2  (50)|
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
                                                   
   2 - access("SYS_ALIAS_2"."CAL_DATE">=TO_DATE(' 2010-09-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "SYS_ALIAS_2"."ACTIVE_FLAG"='Y' AND "SYS_ALIAS_2"."CAL_DATE"<=TO_DATE('
              2010-09-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("SYS_ALIAS_2"."ACTIVE_FLAG"='Y' AND "SYS_ALIAS_2"."CONT_TAG"<>0 AND  NOT EXISTS
              (SELECT /*+ */ 0 FROM "INST_REDENOM" "R" WHERE "R"."INST_NUM"=:B1 AND "R"."CONV_DATE">:B2 AND
              "R"."STATUS"='R'))                                                                                              
   3 - filter("R"."CONV_DATE">:B1 AND "R"."STATUS"='R')
   4 - access("R"."INST_NUM"=:B1)

Where’s the join mechanism?
It has what looks like four standalone operations.

It took me a few minutes to figure out what it really meant.

I think if it had been presented like this, it would have been more obvious:

-----------------------------------------------------------------
| Id  | Operation                     |  Name                    
-----------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          
|   1 |  TABLE ACCESS BY INDEX ROWID     | XXXXXXXXXXXXXXXXXXX      
           FILTER
|*  2 |      INDEX RANGE SCAN            | XXXXXXXXXXXXXXXXXXX_IDX  
|*  3 |      TABLE ACCESS BY INDEX ROWID | YYYYYYYYYYYY            
|*  4 |        INDEX UNIQUE SCAN         | YYYY_PK                  
-----------------------------------------------------------------

And now I’ve just re-read the section on Filtering (pages 211-214) in Cost-based Oracle Fundamentals where Jonathan mentions that the line in the execution plan can be suppressed. Seems to tally.

About these ads