On Nov 13, 2008, at 7:51 AM, Steve McElhinney wrote:
> We have a one-table-query than _insists_ on a full table scan,
> regardless of any indexes, stats, optimizer options, etc. that we try.
>
> This is the query with sample where clause:
>
> select count(xref_reason) as num_links from case_xref
> where court='CREW'
> and court_no='72'
> and yr=2008
> and insol_type='B'
> OR ( xref_court='CREW'
> and xref_court_no='72'
> and xref_yr=2008
> and xref_insol_type='B' )
>
This sort of thing has always been a problem for the optimizer,
in my experience. There are a couple reasons that I can
think of.
First and most importantly, the query plan data structures are not
well suited to the sort of plan you want. Forget about the primary
index for a moment and assume that you have two secondaries.
The plan you need is then something roughly like:
select ... from base where tid in
(select tid from index1 where ...
union select tid from index2 where ....)
but thanks in part to the way unions are structured internally,
it's very difficult (if not impossible) for the optimizer to
propose that plan to itself; the rewrite phase would have to
invent the union subqueries out of thin air. Not that it's
impossible, and it might even be able to do so at times (I
really have no idea), but it wouldn't be simple or general.
With a primary index lookup in the mix it's even harder to
see exactly how one would execute this. I could imagine a
rewrite into some sort of goofy tid full join, or perhaps
a slightly different union form; the latter posing all the
difficulties already mentioned.
The second problem is that the optimizer insists on rewriting
the query into Conjunctive Normal Form, which is a fancy-pants
name for putting everything into the form of AND's of OR's.
In other words, your nice looking query gets rewritten to a horrible
mess that tends to lose the nice key-lookup-ness of your
original query. Then, to produce actual key lookups, it
has to rewrite the CNF back into DNF, OR's of AND's! I've
often thought it should carry both, but whatever. I don't
know if the complex CNF form of your query is a problem
for the optimizer or not, but it might be. It is certainly
going to make it more difficult to recognize that there are
two key-lookup conditions OR'ed together. (See below)
What you can do is try to rewrite the query as a union yourself,
and see if things go any better.
Karl
PS I don't want to even try to write out the CNF for your
query, but the CNF of this simpler one:
select .. from .. where court='CREW' and court_no = 72
OR (xref_court = 'CREW' and xref_court_no=72)
looks like:
.... where (court='CREW' or xref_court='CREW')
and (court='CREW' or xref_court_no = 72)
and (court_no=72 or xref_court='CREW')
and (court_no=72 or xref_court_no=72)
and if you stare at the two for a long time, you'll see
that they are the same thing, but the second certainly
doesn't look like a pair of key lookups!
(I hope I got that right!)
>> Stay informed about: Simple one-table query always scans table - why? 
FAQ
Search
Profile
Private Messages
Log in
