Re: What index access path does INDEX_SS yield ?

Just wanted to give an update on my mystery as to why the Oracle optimizer did not seem to be applying the join elimination optimization "recursively" to eliminate multiple joins. Mystery solved, but another one crops up (see below).

Jonathan Lewis wrote:
> 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".

My example:

    SELECT E.first_name, E.last_name, E.email, E.salary     FROM employees E, departments D, locations L     WHERE E.department_id = D.department_id       AND D.location_id = L.location_id

was not a good one, since the join on "D" is not "eliminatable". This is because in the HR demo schema, both the E.department_id and D.location_id may be NULL. And in fact, the join above does eliminate one of the 107 example rows of data from EMPLOYEES. Oracle is nonetheless able to eliminate the join on LOCATIONS, by inserting a predicate

    D.LOCATION_ID IS NOT NULL into the scan of DEPARTMENTS. See:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=26

So the join elimination is not a "pure" elimination. This behavior is documented in a more careful reading of [1], which in Sec. 2.1.2 says:

    "If in Q4 [the original query], E.dept_id can return nulls, a     predicate "e.dept_id is not null" must be added to the where     clause of Q6 [the transformed query]."

Note this is an example of where the ACCESS_PREDICATES column introduced into the EXPLAIN PLAN output back in V9.2 is very useful, since the predicate is not part of the original query.

I got curious and made a slighly altered version of HR ("HR2"), making all of E.department_id, D.department_id, D.location_id and L.location_id all NOT NULL. (I had to remove the one rows from EMPLOYEES with NULL department ID to satisfy).

But try as I might, I simply cannot get Oracle to eliminate multiple, unnecessarily joined tables from the above query. In the new optimization, Oracle no longer accesses the DEPARTMENTS table, nor adds in the "IS NOT NULL" predicate, which is no longer needed. But it retains the join, using the PK index of DEPARTMENTS to do it:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR2&qid=35

When ONLY the EMPLOYEES and DEPARTMENTS tables are joined, it can eliminate DEPARTMENTS:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR2&qid=38

So, I'm back where I started: wondering if Oracle ever applies the join elimination "recursively" to a query which has been already transformed.

    John Hinsdale

[1] "Cost-Based Query Transformation in Oracle", Ahmed, R., Lee, A., Witkowski, A., et. al. CACM SIGMOD Vol. 32, p. 1027, in VLDB '06, Seoul, Korea, September 12-15, 2006, http://portal.acm.org/ft_gateway.cfm?id=1164215&type=pdf&coll=&dl=ACM&CFID=15151515&CFTOKEN=6184618 Received on Tue Jan 16 2007 - 09:47:24 CST