Is this SQL transformation possible...?

Oracle 10.2.0.2 on Windows 2003

Is it possible for Oracle to transform the following SQL so that it can drive off an index on ST_INSTANCE on PS_TL_RPTD_TIME...?

UPDATE ps_tl_rptd_time
SET reported_status = 'NA'
WHERE EXISTS

     (
     SELECT 'X'
     FROM ps_tl_trc_tbl b
     WHERE ps_tl_rptd_time.st_instance = 587241
     AND ps_tl_rptd_time.trc = b.trc
     AND b.trc_aprvl_opt IN ('2', '4')
     AND b.effdt =
          (
          SELECT MAX (bb.effdt)
          FROM ps_tl_trc_tbl bb
          WHERE bb.trc = b.trc AND bb.effdt <= ps_tl_rptd_time.dur
          )
     );

Plan:

UPDATE STATEMENT

 UPDATE                            PS_TL_RPTD_TIME
  FILTER
   TABLE ACCESS FULL               PS_TL_RPTD_TIME

     INDEX RANGE SCAN              PS_TL_TRC_TBL
      SORT AGGREGATE
       FIRST ROW
        INDEX RANGE SCAN (MIN/MAX) PS_TL_TRC_TBL

ST_INSTANCE is very selective (only a handful of rows from a table containing over 9 Million). I want to be able to drive off this index if possible. Do I need to add an extra WHERE ST_INSTANCE=587241 on the outer query to use the index...?

Thanks

Matt Received on Tue Oct 17 2006 - 03:43:35 CDT