Hi, fellows.
I had a weird little case today, which I'll try to describe simply:
Two tables - Tab1 with 4 million rows, Tab2 with 3/4 million rows.
Tab1 has a function based index on an expression: "case dataset when
'DAT' then upperalphanum(eksterntvarenr) end" - upperalphanum is a
function returning uppercase of input stripped of any whitespace and
non-alphanumeric characters. The FBI contains about two hundred
thousand of the 4 million rows of Tab1, for the rest the expression is
NULL.
Query is a simple join between the two tables joining on a two-column key.
There is a predicate on Tab1 on the FBI expression:
"case dataset when 'DAT' then upperalphanum(eksterntvarenr) end
BETWEEN upperalphanum(:bind1) and upperalphanum(:bind2)"
And a filter predicate on two columns of Tab2.
The access I want (and normally get) is index range scan of the FBI
index on Tab1 and nested loop/index access of Tab2.
(The whole purpose of the FBI is to have a small fast index for this
and other similar queries.)
I have three versions of the query for testing:
Q1: Hinted to use FBI index access on Tab1.
Q2: Hinted to use an alternative normal index on Tab1 containing the
columns of the FBI expression, where the expression then will be
evaluated for all rows.
Q3: Unhinted (my normal query.)
Apart from hints, the three queries are identical.
Normally they get plans costed like this:
Q1 hinted to FBI gets total Cost=26276.
Q2 hinted to normal index gets total Cost=40473.
So normal index has a higher cost than FBI.
Q3 unhinted picks the lower cost access plan and uses FBI with total
Cost=26676.
Then I added a check constraint "check( dataset='DAT' )" on Tab2 on
one of the two key columns used for the join.
This changed the access plans for the queries - suddenly appeared
(optimizer generated) a filter predicate dataset='DAT' on Tab1, as the
optimizer know nows via the check constraint on Tab2 and the join
between Tab1 and Tab2, that accessing any Tab1 rows with dataset NOT
equal to 'DAT' would be folly, because they would be certain to be
"thrown away" when we join to Tab1 on dataset column. ("Salted
banana", as NoCoug Journal recently called it ;-)
When that filter predicate was added, my three test queries got new
costs, of course:
Q1 hinted to FBI gets total Cost=24374.
Q2 hinted to normal index gets total Cost=35493.
So even with the new filter predicate reducing estimated cardinality
(and cost) slightly, normal index is still higher cost than FBI.
BUT... Q3 unhinted picks the HIGHER cost access plan and uses normal
index with total Cost=35493 ??
I can understand that my check constraint has a sideeffect of adding a
filter predicate.
I have also tested dropping the constraint again and instead added the
same filter predicate manually to the queries - it gives the same
result (so it is not specifically because there's a check constraint.)
What I canNOT understand is, that with the extra filter predicate in
place, the optimizer picks the HIGHER costed of the two access plans?
So my question really is:
Are there known circumstances where the optimizer does NOT choose the
lowest cost, even though same query with a hint CAN produce a plan
with a lower cost?
Or is this "buggy" behaviour? (My version is 11.2.0.3.0 EE.)
Thanks in advance for any hints I can research ;-)
Regards
Kim Berg Hansen
http://dspsd.blogspot.com
kibeha@xxxxxxxxx <mailto:kibeha@xxxxxxxxx>
@kibeha
