Influence Optimzer to merge 'IN' subquery
Matt
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
Sybrand Bakker
>
>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.
There is PUSHQ or a PUSHD hint (don't exactly remember) to accomplish
this.
However, is there any chance value_group is null allowed?
Tom Kyte in his book shows this influences the way NOT IN subqueries
are handled, this might also apply to IN subqueries.
Guess you'd better post the full plan, and information on the indexes
present on the main table.
Usually, btw, transforming an IN subquery to an EXISTS correlated
subquery still works.
--
Sybrand Bakker, Senior Oracle DBA
DA Morgan
One possiblity ... replace IN with EXISTS.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Matt
> One possiblity ... replace IN with EXISTS.
That would achieve the exact opposite of the order I want. I want to
drive from the subquery into the outer query, but exists implicitly
drives from the outer to the inner.
Matt
pobo...@bebub.com
>
> 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
xho...@gmail.com
> >
> > One possiblity ... replace IN with EXISTS.
>
> That would achieve the exact opposite of the order I want.
How do you know that?
> I want to
> drive from the subquery into the outer query, but exists implicitly
> drives from the outer to the inner.
The Oracle optimizer does what it does, regardless of your beliefs.
For some reason, it often makes better choices with exists queries
than it does with in-list queries. And often that choice includes
swapping the join order.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
Jonathan Lewis
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" <mc...@hotmail.com> wrote in message
news:cfee5bcf.05020...@posting.google.com...