mccmx_at_hotmail.com wrote:
> 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
> FILTER
> TABLE ACCESS BY INDEX ROWID PS_TL_TRC_TBL
> 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
Collect current statistics
Rerun a real explain plan using DBMS_XPLAN and post it.
Needless to say Jonathan Lewis has given sage advice on how to force a
specific execution plan. A copy of his book on the CBO would be invaluable.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Oct 17 2006 - 10:21:23 CDT
