What index access path does INDEX_SS yield ?

"John K. Hinsdale" wrote in message
news:1168294944.547565.319760-at-s34g2000cwa.googlegr oups.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...FTOKEN=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=sq...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=sq...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=sq...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=sq...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

"Spendius" wrote in message
news:1168330632.241005.59010-at-i15g2000cwa.googlegro ups.com...
Jonathan asked me two questions in our direct
communications:
>>Is "TRTP_ID" also a column in the primary key of TF ?
>>
>>I would raise this with Oracle - but for curiosity sake,
>>you might see what happens with
>> TF.TRTP_ID + 0 = tt.trtp_id

His assumption as to TRTP_ID being a member of the
PK was right; as to his suggestion to add a pseudo-condition
to the join between TF and TT it worked perfectly as it
caused the optimizer to eventually comply with my
hint instruction...
Here is what I answered him:
> 1/ yes, TRPT_ID is another field of the PK,
> 2/ and if I add the "+ 0" to the 2nd join condition
> I get my so boundlessly expected SKIP SCAN !

Thanks, and meilleures salutations à tous.
Sp




Spendius,

Thanks for posting the closing details.

I'll try to write this up as a page on my blog
(better chance of keeping the output readable)
some time next week.

It was an interesting little example of how a
10053 can help pin down a bug.


--
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

"Spendius" wrote in message
news:1168330632.241005.59010-at-i15g2000cwa.googlegro ups.com...
Jonathan asked me two questions in our direct
communications:
>>Is "TRTP_ID" also a column in the primary key of TF ?
>>
>>I would raise this with Oracle - but for curiosity sake,
>>you might see what happens with
>> TF.TRTP_ID + 0 = tt.trtp_id

His assumption as to TRTP_ID being a member of the
PK was right; as to his suggestion to add a pseudo-condition
to the join between TF and TT it worked perfectly as it
caused the optimizer to eventually comply with my
hint instruction...
Here is what I answered him:
> 1/ yes, TRPT_ID is another field of the PK,
> 2/ and if I add the "+ 0" to the 2nd join condition
> I get my so boundlessly expected SKIP SCAN !

Thanks, and meilleures salutations à tous.
Sp




Spendius,

Thanks for posting the closing details.

I'll try to write this up as a page on my blog
(better chance of keeping the output readable)
some time next week.

It was an interesting little example of how a
10053 can help pin down a bug.


--
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

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=sq...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=sq...ser=HR2&qid=35

When ONLY the EMPLOYEES and DEPARTMENTS tables are joined, it can
eliminate DEPARTMENTS:
http://otb.alma.com/otb.fcgi?func=sq...ser=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...FTOKEN=6184618

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=sq...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=sq...ser=HR2&qid=35

When ONLY the EMPLOYEES and DEPARTMENTS tables are joined, it can
eliminate DEPARTMENTS:
http://otb.alma.com/otb.fcgi?func=sq...ser=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...FTOKEN=6184618