Influence Optimzer to merge 'IN' subquery

Hi everyone, (Oracle 9.2.0.4 Windows 2K)

I have a query which performs a join between a very large table (> 8 Million rows) with a very small table.

Oracle is retrieving the large table rows via an appropriate index and then going to the table to find the rows. The table fetch (by rowid) takes over 99% of the execution time (according tkprof). Finally it restricts these rows based on the rows in the small table (subselect).

What I would like to do is use the values returned from the subselect to retrict the rows in the very large table before the table access. If this is possible I will reduce the table block viists by 90%.

Here is the query:

select sum(tl_quantity)
from
 ps_tl_payable_time where emplid ='006938' and
trc in

   (
   select valuegroup from ps_tl_val_list_dtl    where list_id ='F-130HOURS'
   and list_type = 2
   )
and dur >=

   TO_DATE('2004-01-01','YYYY-MM-DD')
and dur <=

   TO_DATE('2004-12-03','YYYY-MM-DD') The number of rows returned where emplid = 006938 and dur between 01/01/04 and 03/12/04 is approx 2000.

But the number of rows returned where emplid = 006938 and trc = valuegroup (from subquery) and dur between 01/01/04 and 03/12/04 is approx 200.

So how can I force Oracle to evaluate the subquery first and use the value as part of the index range scan of the large table.

Any help appreciated..

Cheers

Matt Received on Wed Feb 09 2005 - 10:29:50 CST