Re: Influence Optimzer to merge 'IN' subquery

Matt wrote:
>
> 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')
>

Does this help

select sum(tl_quantity)
from
 ps_tl_payable_time where emplid ='006938',    (
   select distinct valuegroup from ps_tl_val_list_dtl    where list_id ='F-130HOURS'
   and list_type = 2
   and rownum > 0
   ) x
where

   x.valuegroup = trc
and dur >=

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

   TO_DATE('2004-12-03','YYYY-MM-DD') The rownum will force the in line view to be processed first as the psuedo column means it cannot be merged.

I have also heard that the /*+ leading */ hint can be used to specify the driving table, but I have not tried it. That would become

select /*+ leading (x) */ sum(tl_quantity) from
 ps_tl_payable_time where emplid ='006938',    (
   select distinct valuegroup from ps_tl_val_list_dtl    where list_id ='F-130HOURS'
   and list_type = 2
   ) x
where

   x.valuegroup = trc
and dur >=

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

   TO_DATE('2004-12-03','YYYY-MM-DD') Probably both worth a try.

-- 
MJB