Re: Influence Optimzer to merge 'IN' subquery

Matt wrote:

> 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

One possiblity ... replace IN with EXISTS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)