Re: What index access path does INDEX_SS yield ?

"John K. Hinsdale" <hin_at_alma.com> wrote in message news:1168294944.547565.319760_at_s34g2000cwa.googlegroups.com...
> Well, I tried, but I had surprising difficulty coming up
> w/ such a case, and gave up. I think it was hard because
> subqueries get transformed into anti-joins and semi-joins
> which are not as readily eliminated as simpler equijoins?
>
> I did notice something interesting w/ the straightforward
> elimination of equijoins on tables whose columns are unused.
> Paraphrasing the example used on the demo "HR" (Human
> Resources) schema in Sec. 2.1.2, "Join Elimination" of
> Oracle's paper[1]:
>
> http://portal.acm.org/ft_gateway.cfm?id=1164215&type=pdf&coll=&dl=ACM&CFID=15151515&CFTOKEN=6184618
>
> I considered the plans produced by the simple three-way
> join:
>
> SELECT E.first_name, E.last_name, E.email, E.salary,
> D.department_name, D.manager_id,
> L.city, L.state_province, L.country_id
> FROM employees E, departments D, locations L
> WHERE E.department_id = D.department_id
> AND D.location_id = L.location_id
>
> The base query cannot have its joins optimized out since
> needed data resides in all three tables:
>
> http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=24
>
> After removing the SELECT'ed columns from LOCATIONS, while
> leaving the table joined in:
>
> SELECT E.first_name, E.last_name, E.email, E.salary,
> D.department_name, D.manager_id
> FROM employees E, departments D, locations L
> WHERE E.department_id = D.department_id
> AND D.location_id = L.location_id
>
> Oracle does indeed eliminate the join on LOCATIONS
> completely from its plan, see:
>
> http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=25
>
> Now the interesting part: you would expect after removing
> the SELECT'ed columns from DEPARTMENTS that Oracle could
> eliminate DEPARTMENTS as well, but it does not:
>
> http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=26
>
> ... while at the same time Oracle _is_ able to optimize
> DEPARTMENTS out as long as there is no other join:
>
> http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=27
>
> It's as if, after optimizing out LOCATIONS, the optimizer
> doesn't "start over" with what is left and recursively try
> to look for more eliminations, so that it looks only down
> one "level." Maybe Oracle has to choose between spending
> time in the optimizer vs. actually executing the query and
> when it gets down below some small threshold just "calls it
> a day." Perhaps with larger tables, and larger cost savings
> the optimizer would forge ahead and do (or try to do) more
> elimination.
>
> [1] "Cost-Based Query Transformation in Oracle", Ahmed, R.,
> Lee, A., Witkowski, A., et. al. CACM SIGMOD, 2006, Vol. 32,
> p. 1027
>
> Cheers,
>
> John Hinsdale
>

John,

Nice little investigation on the 2-table elimination. I would guess that your "heuristic" comment is probably correct - although (without reading the pdf again) I have a vague idea that join elimination is considered to be a guaranteed performance benefit so it is a heuristic transformation, not a cost-based one.

Possibly the code is just a little too simplistic - viz - "we can't eliminate D because it has location in the projection - and we don't realise that we only have it in the projection because we wanted to join to L".

I had a quick shot at eliminating a subquery - same sort of problem (as you surmise) - I have Oracle transform an IN subquery to a join which could be eliminated, but isn't. Write the same join by hand and it is eliminated. Complexity of recursion (or simple ordering of types of operation - as the pdf suggests) seems likely.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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