What index access path does INDEX_SS yield ?
Spendius
I have a query where the skip scan hint keeps the optimiser
choosing an index full scan: will the use of this hint always
have this effect ? This is a 10g DB.
Thanks.
Spendius
sybrandb
Generic questions without any details as to the specific statement,
without running the statement with event 100053 set: it is always the
same with you, isn't it?
Do you really think such sweeping generic answers are possible?
If not, why do you continue to post questions without any information?
Or are you just searching for silver bullets?
Hints are just what they are: HINTS.
There must be a specific reason why CBO choose to ignore the hint.
Set event 10053 (as explained here many times before) and find out the
reason.
--
Sybrand Bakker
Senior Oracle DBA
Jonathan Lewis
news:1167474399....@48g2000cwx.googlegroups.com...
Sybrand,
It has also been pointed out to you several times that
Oracle "hints" are not just hints in the English-language
sense of the word. In the absence of bugs, and provided
they are syntactically correct and legal, they must be
obeyed.
The last time you made your erroneous claim, I asked you
for an example that demonstrated your point - I am still
waiting for an answer.
Spendius -
Which version of Oracle, 10g is not a sufficient.indicator.
I have seen some odd behaviour with index_ss hints in
10.1.0.2.
Apart from that, I do have one case (last tested 10.2.0.1)
where a query switches from a tablescan to an index full scan
when given the index skip scan hint.
But the index is a reverse key index and I think this is an optimizer
bug, as the index full scan cost is less than the tablescan cost and
should have been chosen by default. Moreover, the skip scan cost
is not calculated when the skip scan hint is used.
--
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
Version 10.2.0.2 64-bit on Sun/Solaris.
We are in a DWH star schema (TABLE_1 is a fact, TABLE_2 a
dimension).
Table TABLE_1 is partitioned and contains 13 millions records.
Its ID field (used in the WHERE clause) is the 2nd column of
its primary key, but it's not indexed by itself (this is why
I'd like to see the response time with a SKIP SCAN access).
TABLE_2 is very small (about 50 rows), TABLE_3 too (51 rows).
The result set is 6-row big.
SQL> l
1 select *
2 from (
3 SELECT [--+ index_ss(TF TABLE_1_PK)]
4 ...
5 TF.ID id,
27 OT....,
34 ...
35 from TABLE_1 TF,
36 TABLE_2 TT,
37 TABLE_3 OT
38 WHERE TF.OTHE_ID = TT.OTHE_ID
39 AND OT.DSCR = TT.DSCR)
40* WHERE ID = 5485186
Spontaneous optimizer solution (response time = about 1 mn when rset
not yet in the buffer):
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=29093 Card=57 Bytes=8037)
1 0 HASH JOIN (Cost=29093 Card=57 Bytes=8037)
2 1 MERGE JOIN (Cost=7 Card=51 Bytes=3672)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3' (TABLE) (Cost=2
Card=51 Bytes=2346)
4 3 INDEX (FULL SCAN) OF 'TABLE_3_I01' (INDEX (UNIQUE)) (Cost=1
Card=51)
5 2 SORT (JOIN) (Cost=5 Card=51 Bytes=1326)
6 5 TABLE ACCESS (FULL) OF 'TABLE_2' (TABLE) (Cost=4 Card=51
Bytes=1326)
7 1 PARTITION RANGE (ALL) (Cost=29085 Card=57 Bytes=3933)
8 7 TABLE ACCESS (FULL) OF 'TABLE_1' (TABLE) (Cost=29085 Card=57
Bytes=3933)
When hinted, Oracle switches from a FTS on TABLE_1 to a full scan
of its PK (only index on this table, global) (resp. time = 10 mn):
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2476880 Card=57
Bytes=8037)
1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TABLE_1' (TABLE)
(Cost=48572 Card=1 Bytes=69)
2 1 NESTED LOOPS (Cost=2476880 Card=57 Bytes=8037)
3 2 MERGE JOIN (Cost=7 Card=51 Bytes=3672)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_3' (TABLE) (Cost=2
Card=51 Bytes=2346)
5 4 INDEX (FULL SCAN) OF 'TABLE_3_I01' (INDEX (UNIQUE))
(Cost=1 Card=51)
6 3 SORT (JOIN) (Cost=5 Card=51 Bytes=1326)
7 6 TABLE ACCESS (FULL) OF 'TABLE_2' (TABLE) (Cost=4 Card=51
Bytes=1326)
8 2 INDEX (FULL SCAN) OF 'TABLE_1_PK' (INDEX (UNIQUE))
(Cost=48565 Card=6)
Thanks.
Spendius
***************************************
SINGLE TABLE ACCESS PATH
Column (#2): ID(NUMBER)
AvgLen: 5.00 NDV: 228728 Nulls: 0 Density: 4.3720e-06 Min: -1 Max:
5766027
Table: TABLE_1 Alias: TF
Card: Original: 12956500 Rounded: 57 Computed: 56.65 Non
Adjusted: 56.65
Access Path: TableScan
Cost: 29085.35 Resp: 29085.35 Degree: 0
Cost_io: 28450.00 Cost_cpu: 5144333483
Resp_io: 28450.00 Resp_cpu: 5144333483
kkofmx: index filter:"TF"."ID"=5485186
AND "TF"."OTHE_ID"="TT"."OTHE_ID"
AND "OT"."DSCR"="TT"."DSCR"
Access Path: index (skip-scan)
SS sel: 4.3720e-06 ANDV (#skips): 4378945
SS io: 4378945.00 vs. table scan io: 28450.00
Skip Scan rejected
Access Path: index (FullScan)
Index: TABLE_1_PK
resc_io: 48164.00 resc_cpu: 3060392722
ix_sel: 1 ix_sel_with_filters: 4.3720e-06
Cost: 48621.98 Resp: 48621.98 Degree: 1
Best:: AccessPath: TableScan
Cost: 29085.35 Degree: 1 Resp: 29085.35 Card: 56.65 Bytes:
0
***************************************
Jonathan Lewis
"Spendius" <spen...@muchomail.com> wrote in message
news:1167562699.1...@i12g2000cwa.googlegroups.com...
Judging from the costing of the skip-scan in the
other posting
>> SS sel: 4.3720e-06 ANDV (#skips): 4378945
the number of distinct value for the first
column is huge - so the cost of a skip scan
would be prohibitive - which is why Oracle has
fallen back on the full scan.
I don't know why the optimizer has decided
to do this - but perhaps part of the algorithm
for skip scans converts them to full scans if the
cost exceeds some limit. I'd take this up with
Oracle.
To simplify your test case, it looks as if a sime
select from table1 where id = constant
should show the same behaviour.
John K. Hinsdale
> I don't know why the optimizer has decided to do this - but perhaps
> part of the algorithm for skip scans converts them to full scans if
> the cost exceeds some limit. I'd take this up with Oracle.
Yah, Oracle seems to do some weird things when one attempts via hints
to "force" it to use skip-scan (an approach that strikes me as
dubious; see below).
For example, consider the small 10-row table HR.JOB_HISTORY in the
example "HR" schema that ships w/ Oracle 10.2.0.1.0:
http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=HR&table=JOB_HISTORY
This tables comes with a compound index (the PK actually) on the key
(EMPLOYEE_ID, START_DATE), as well as some other single-column
indexes:
http://otb.alma.com/otb.fcgi?func=tinfo&server=orcl&user=HR&table=JOB_HISTORY
A simple query that filters on START_DATE does the full scan on the
tiny table, as expected:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=21
Adding the INDEX_SS hint, fully qualified with the exact index to use,
causes Oracle to perform the skip-scan on the index as instructed:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=19
Now for the weird part: if you give the INDEX_SS hint, to include the
table range alias (which is unambiguous here -- only one table in the
FROM clause), but not specifying the exact index, Oracle constructs an
execution plan that does an index full-scan, but on one of the single
column indexes (on EMPLOYEE_ID) which is a column completely
irrelevant to this query, at least as far as row selection and
selectivity is concerned:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=HR&qid=20
Very strange.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Stepping back, it strikes me that the very idea of forcing a skip-scan
via a hint is somewhat dubious in general. Skip-scan seems like the
sort of obscure access method the optimizer might employ on its own,
when it is very sure of what it is doing (i.e., that the number if
"skips" is known, and small) but that, when forced upon the optimizer
by the end user can, potientally, anyway, lead to unpredictable
results -- while predictiability is precisely the thing hints are good
for, in their place as a last resort.
I wonder if Oracle includes the INDEX_SS hint more as a matter of
completeness, so that it can guarantee that every access method that
could be used by the optimizer on its own is also available for
forcible use (and abuse) by a query writer.
As another example, the top hit on a Google search for INDEX_SS gives
an example provided by the PSOUG organization:
http://www.psoug.org/reference/hints.html
When I EXPLAIN the example query (again, w/ Oracle's tiny test data as
shipped), the hint increases the estimated cost from 1 (for the full
table scan) to 102 (w/ the index skip-scan), and when I run the query
in the two forms, the actual cost rises from 2 consistent gets to 4.
I've read elsewhere that INDEX_SS can be good for leveraging a
compound index when one is very, very tight for space, but I have
trouble envisioning a situation when it's space savings would be worth
it.
Happy optimizing,
John Hinsdale
Jonathan Lewis
"John K. Hinsdale" <h...@alma.com> wrote in message
news:1167677424.4...@48g2000cwx.googlegroups.com...
John,
Summarizing your example:
no hint gives full tablescan
index_ss() with named index gives skip scan on right index
index_ss() with no index named gives full scan on 'wrong' index
I think this goes back to my original point - but only after some
refinement. Bottom line - we need to know the logic behind the
implementation before we can understand the symptoms we see,
but how about this (as a guess):
A skip scan hint HAS to allow the optimizer to do a range
or full scan on the names index. After all, if the first column
has only one value to it, the skip scan is (in principle) a range
scan.
Therefore, as soon as you use the index_ss() hint, it forces Oracle
to cost for a skip scan on every single index - even the ones where
a 'real' skip scan is impossible (and that last clause may be a bug) -
because a range/full scan is only a special case of a skip scan.
If, after following this directive, the cost for an index full scan on
a 'silly' index is less than the cost of using an index with a
"genuine"
skip scan capability, then Oracle has to use the "silly" index.
In other words, maybe the index_ss() hint doesn't mean "do a skip scan",
maybe it means "use an index - even if you have to use a skip scan to make
it usable".
This type of thing can lead to other problems - in recent versions of
Oracle
I've had queries crash (ORA-00600 and ORA-07445) because I've been
trying to set up a combination like "use index X but don't do an
index_combine
with it"
Bear in mind that, by the way, that the optimizer is only trying to model
your database, so any comments like "the cost of this path was huge,
but the query ran twice" is largely a reflection on the quality of the
MODEL.
The path is (by definition) the right one for the model - even if it's
clearly
the wrong one for the real world.
Personally I agree with the argument about reducing the number of
indexes required by making the skip-scan possible. But I would
only expect it to be relevant in a few special cases for very large tables
with some fairly obvious candidates and with a known user-requirement
in mind - and even then there may be smarter strategies to use, such as
list partitioning.
As far as hints in general are concerned, see the footnote on
http://jonathanlewis.wordpress.com/2006/12/09/how-many-ways/
John K. Hinsdale
> ... as soon as you use the index_ss() hint, it forces Oracle
> to cost for a skip scan on every single index - even the
> ones where a 'real' skip scan is impossible ... because a
> range/full scan is only a special case of a skip scan.
>
> If, after following this directive, the cost for an index
> full scan on a 'silly' index is less than the cost of using
> an index with a "genuine" skip scan capability, then Oracle
> has to use the "silly" index.
This explanation is both plausible and enlightening, thanks!
-- JH
Jonathan Lewis
I can't reproduce the case of
index_ss(alias index_name)
becoming a full tablescan.
However, thinking further about this piece of trace file,
it looks to me as if Oracle hasn't see your hint at all.
This suggests that there is a syntax error somewhere
in your hint.
Can you show us:
The COMPLETE set of hints you have included
and
An example of the SQL statement with the
output from dbms_xplan - including the
filter_predicates section.
--
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" <spen...@muchomail.com> wrote in message
news:1167563904....@v33g2000cwv.googlegroups.com...
Spendius
> it looks to me as if Oracle hasn't see your hint at all.
> This suggests that there is a syntax error somewhere
> in your hint.
reacts the wrong way but does react anyway: when
I remove the hint I get an FTS on TABLE_1.
Jonathan Lewis
True,
I was fooled by the presence of the calculation of
the full tablescan - which doesn't appear in any of
my attempts to reproduce the problem when I use
the hint.
Jonathan Lewis
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:b96dnZlGYbfNdAHY...@bt.com...
>
> "Spendius" <spen...@muchomail.com> wrote in message
> news:1167908823.8...@51g2000cwl.googlegroups.com...
>>> However, thinking further about this piece of trace file,
>>> it looks to me as if Oracle hasn't see your hint at all.
>>> This suggests that there is a syntax error somewhere
>>> in your hint.
>> I don't think so as the optimizer reacts to my hint, it
>> reacts the wrong way but does react anyway: when
>> I remove the hint I get an FTS on TABLE_1.
>>
>
>
> True,
>
> I was fooled by the presence of the calculation of
> the full tablescan - which doesn't appear in any of
> my attempts to reproduce the problem when I use
> the hint.
>
Unless, of course, a syntax error is causing
some of your hints to become "invisible", so that
the net effect of the "visible" hints forces
the optimizer into an execution path that
has to do the full tablescan
Do you have any hints AFTER the index_ss() ?
What's the exact list of hints as it appears in
your query ?
Jonathan Lewis
>
> I can't reproduce the case of
> index_ss(alias index_name)
> becoming a full tablescan.
>
>
My earlier comment should have been
I can't reproduce the case of
index_ss(alias index_name)
becoming a __index full scan__.
rather than the
becoming a full tablescan.
that I typed.
joel garry
Jonathan Lewis wrote:
> Sybrand,
>
> It has also been pointed out to you several times that
> Oracle "hints" are not just hints in the English-language
> sense of the word. In the absence of bugs, and provided
> they are syntactically correct and legal, they must be
> obeyed.
Bugs. That kind of negates this whole paragraph, doesn't it?
We can't explicate all of the internal processes that oracle
goes through to determine a path, only marvel at what a trace
tells us. So even though the mere fact that it is binary code
makes it deterministic, the fact that we can't know all of the code
adds an element of chaos. Which drives "hints" to the English
meaning of the word, since we can only use heuristics to figure
it out. And of course, you, Jonathan, are the best at that! Kinda
makes me wonder what I'm missing if we disagree here.
As many simple demonstrations have shown, chaos doesn't
require many variables.
http://www.physics.lsa.umich.edu/demolab/demo.asp?id=60
>
> The last time you made your erroneous claim, I asked you
> for an example that demonstrated your point - I am still
> waiting for an answer.
>
I believe this thread answers that, and the answer to Spendius'
original question is "yes, changing the relative volumes of data
can affect the path chosen even with a hint."
Somehow I wonder if we are stumbling around a strangeness
with global indices here. Something like "expect sparseness
from the point of view of a field within a global index so increase
the cost of a skip scan."
jg
--
@home.com is bogus.
"The Saints Are Coming... and their quarterback used to play for us." -
San Diego DJ introducing a U2 song.
Spendius
> What's the exact list of hints as it appears in
> your query ?
INDEX_SS is the only one, I would've detailed
them if there had been more; anyway I've created
an index on this specific field to get rid of both
these FTS and FULL INDEX SCAN accesses.
Thanks.
Jonathan Lewis
"joel garry" <joel-...@home.com> wrote in message
news:1167952224....@38g2000cwa.googlegroups.com...
>
> Jonathan Lewis wrote:
>> Sybrand,
>>
>> It has also been pointed out to you several times that
>> Oracle "hints" are not just hints in the English-language
>> sense of the word. In the absence of bugs, and provided
>> they are syntactically correct and legal, they must be
>> obeyed.
>
> Bugs. That kind of negates this whole paragraph, doesn't it?
Not at all.
Would you say "Oracle ignores SQL" because there are bugs
in the SQL which give you incorrect answers ?
I would prefer people to say "There are a few bugs with hints"
rather than saying "Oracle ignores hints". If they adopt the former
belief, then they might pause to thing "Maybe I've made a mistake
in my hints" a little more readily.
Think how many times people on this newsgroup have said
"Oracle has a bug" and then produced a piece of (unhinted)
SQL that shows they don't understand how SQL works.
Very few of them said things like "Oracle is ignoring my
subquery" - and I don't think I've ever seen anyone reply
"that's because Oracle can ignore subqueries". (Though
they may be true in 10gR2 occasionally).
>>
>> The last time you made your erroneous claim, I asked you
>> for an example that demonstrated your point - I am still
>> waiting for an answer.
>>
>
> I believe this thread answers that, and the answer to Spendius'
> original question is "yes, changing the relative volumes of data
> can affect the path chosen even with a hint."
>
A) The thread doesn't answer it - Spendius hasn't supplied
enough data for a complete analysis - it may be a bug, it
may be a typing error - it may be expected behaviour.
B) That wasn't his question - but your answer is wrong.
Apart from bugs, changes in volume, relative volume, and
distribution of data will NOT change the path in the face
of a complete set of hints.
> Somehow I wonder if we are stumbling around a strangeness
> with global indices here.
It's quite common for bits of code to play catch-up in Oracle.
New features are quite complete when launched - so it's quite
possible that some pieces of optimizer code don't respond
correctly to new optimisation strategies or their associated
hints. However, this was one of the options I hacked through
when trying to check what was going on - and nothing in the
trace file suggested a missing code path.
Spendius -
If you care to email me the full trace 10053 file of the
misbehaving execution, I'll see if I can determine what
the problem is.
DA Morgan
> Think how many times people on this newsgroup have said
> "Oracle has a bug" and then produced a piece of (unhinted)
> SQL that shows they don't understand how SQL works.
> Very few of them said things like "Oracle is ignoring my
> subquery" - and I don't think I've ever seen anyone reply
> "that's because Oracle can ignore subqueries". (Though
> they may be true in 10gR2 occasionally).
Well said.
Your last comment caught my interest. Would you have an example
of Oracle ignoring a subquery you could share?
Thanks.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
John K. Hinsdale
> Jonathan Lewis wrote:
> > Very few of them said things like "Oracle is ignoring my
> > subquery" - and I don't think I've ever seen anyone reply
> > "that's because Oracle can ignore subqueries".
DA Morgan wrote:
> Your last comment caught my interest. Would you have an example
> of Oracle ignoring a subquery you could share?
I doubt this is the interesting example Dan is looking
for, but a very trivial degenerate case, using the demo
"SH" (Sales History) schema shipped w/ 10g:
SELECT *
FROM customers
WHERE 1 = 1
OR cust_id IN ( SELECT cust_id
FROM customers
WHERE cust_id = 100
)
will do a full scan and ignore the subquery completely.
I imaginge there is a more interesting case where Oracle
will decide a subquery doesn't affect the results and
can be optimized out. I presume that is the only time
the optimizer is free to ignore them?
Here is proof from a running instance that SQL above
optimizes into the full scan as claimed:
http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=SH&qid=23
A more interesting example -- or general description of
how/when the phenomemon occurs, would be enlightening.
Cheers,
John Hinsdale
Jonathan Lewis
John,
Your final point is correct - in 10gR2 the optimizer
will attempt to eliminate anything which is redundant
provided it is guaranteeably going to produce the same
result set. Your example is actually a case of predicate
elimination - the execution plan will show no filter predicates,
despite the two that exist in the original text.
I was thinking (only theoretically) of the fact 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 -
the critical test is that it should be a join on a unique key that
is guaranteed not to eliminate data).
DA Morgan
Thanks Jonathan and John.
Jonathan Lewis
> Spendius -
> If you care to email me the full trace 10053 file of the
> misbehaving execution, I'll see if I can determine what
> the problem is.
>
Spendius sent me the trace file.
I believe this is a bug, although Oracle might decide
to call it expected behaviour.
The hint IS being obeyed - you can see all over the
trace file that Oracle is NOT doing calculations
about bitmap indexes and tablescans it does when
the hint is omitted. However, there are THREE
places where Oracle does an INDEX FULL SCAN
calculation when it should be doing a skip scan
calculation. All of them at the point of
Now Joining TF using a nested loop.
Here are the six join orders:
Join order[1]: [TT]#0 [OT]#1 [TF]#2
Join order[2]: [TT]#0 [TF]#2 [OT]#1
Join order[3]: [OT]#1 [TT]#0 [TF]#2
Join order[4]: [OT]#1 [TF]#2 [TT]#0
Join order[5]: [TF]#2 [TT]#0 [OT]#1
Join order[6]: [TF]#2 [OT]#1 [TT]#0
The last two are driven by TF (which uses a skip scan calc)
The first three incorrectly use the full-scan calc.
So what's special about join order 4 ?
The join predicate:
>> 38 WHERE TF.OTHE_ID = TT.OTHE_ID
>> 39 AND OT.DSCR = TT.DSCR)
>> 40* WHERE ID = 5485186
Note line 38 - there is a second predicate into TF,
which becomes an ACCESS predicate into TF
when TT appears before TF in the join order -
which it does in the first three join orders (which do
the wrong thing) but not in the fourth (which does
the right thing)
So: probably a bug relating to the extra access predicate
that appears; but possibly "expected behaviour" from the
Oracle Support point of view.
John K. Hinsdale
> 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
Spendius
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
Jonathan Lewis
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.
Jonathan Lewis
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.
John K. Hinsdale
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