Re: Is this SQL transformation possible...?

<mccmx_at_hotmail.com> wrote in message
news:1161074615.523942.302080_at_f16g2000cwb.googlegroups.com...
> 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
> )
> );
>
>Do I need to add an extra WHERE ST_INSTANCE=587241 on the
> outer query to use the index...?
>
> Thanks
>
> Matt
>

The lines

> WHERE ps_tl_rptd_time.st_instance = 587241

on the INNER query is rather counter-intuitive, and possibly the reason for the plan you get. Code like the following is easier to interpret, and may give you the plan you want -:

> UPDATE ps_tl_rptd_time
> SET reported_status = 'NA'
> WHERE ps_tl_rptd_time.st_instance = 587241

       AND EXISTS


>     (

>     SELECT 'X'

>     FROM ps_tl_trc_tbl b

>     WHERE

>               b.trc = ps_tl_rptd_time.trc

>     AND b.trc_aprvl_opt IN ('2', '4')

>     AND b.effdt =

Is it possible that this then leaves you with a missing

    b.st_instance = ps_tl_rptd_time.st_instance in the subquery ?

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html