SQL Tuning

Hi,
[Quoted] Please suggest me a better way to implement the query.   

     select sa_qty_rate, sa_ex_date
            into ----
            from sc_scty_act
            where sa_scty = 123
              and sa_activity = 'P'
              and sa_ex_date  = (select max(sa_ex_date)
                                        from sc_scty_act
                                        where sa_scty  = 123
                                         and   sa_activity = 'P'
                                         and  sa_ex_date <= sysdate);

  The table sc_scty_act has approx. 11 million records. The table is indexed as follows ----

  Index_name SA_PRIME

                       which is a combination of
                      SA_SCTY 
                      SA_ACTIVITY 
                      SA_EX_DATE 
                    

The relevant fields are as ----
     sa_scty    not null number(15)
     sa_activity not null varchar2(7)
     sa_ex_date   not null date
     
The explain_plan gives me the following output ---     
     
     rows       execution plan
     
     0          select statment hint:rule
     21         table access (by Rowid) of 'SC_SCTY_ACT'
     21         index (unique scan) of 'SA_PRIME' (unique)
     18544      sort (aggregate)
     18565      index (range scan) of 'SA_PRIME' (unique)
     
     
     In Tkprof the only concern is --- 
     execute    query   current rows
                        126     0         21  

Please e-mail me at binny_at_voicenet.com for any suggestions.

Thanks

Vaneet Gupta Received on Sun Jan 12 1997 - 00:00:00 CET