Re: Influence Optimzer to merge 'IN' subquery

The operation you want to see is subquery unnesting - which 9.2 usually does whenever possible. However, a necessary pre-condition of this appearing is a guarantee that the columns at both ends of the IN test are not null. So you either need a couple of 'is not null's or a pair of not null constraints.

If the columns are already mandatory, you could try the /*+ unnest */ hint which has to be put in the subquery.

If that fails, do manual unnesting - move the subquery into the main query as an inline view (with distinct). You may then find that you need to use other hints (such as ordered, use_nl, index()) to avoid (e.g.) a hash join with full scan on the ps_tl_payable_time table.

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005






"Matt" <mccmx_at_hotmail.com> wrote in message 
news:cfee5bcf.0502090829.72c70d92_at_posting.google.com...


> 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