Re: What index access path does INDEX

From: John K. Hinsdale <hin_at_alma.com>
Date: 8 Jan 2007 14:22:24 -0800
Message-ID: <1168294944.547565.319760@s34g2000cwa.googlegroups.com>

Jonathan Lewis wrote:

> I was thinking ... that 10gR2 can eliminate joins - and
> since subqueries can be transformed to join there will be
> examples you could construct where a subquery disappears
> because it is first transformed and then eliminated. (I
> haven't yet done this - but given the clue I'm sure you or
> Dan will be able to create a case very quickly -

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 Received on Mon Jan 08 2007 - 16:22:24 CST

Re: What index access path does INDEX_SS yield ?