Re: Why Does Query Require Table Access
Date: Fri, 30 Jan 2009 10:05:43 -0000
Message-ID: <G_OdnYyzgKFrTR_UnZ2dnUVZ8oyWnZ2d_at_bt.com>
<bobf32_at_googlemail.com> wrote in message news:0ffb4e48-0137-411a-83f3-cddf99af64d3_at_u18g2000pro.googlegroups.com...
> Hi,
>
> I have a table t, partitioned by business_date, one partition per day.
> I have a locally partitioned index on this table on column c1. The
> following query performs as expected and accesses only one partition
> of the index, and doesn't touch the table at all (as expected):
>
> select count (1)
> from t partition (p20081031)
> where c1 is not null ;
>
> However the following query produces a plan involving a full index
> scan coupled with a table index rowid.
>
> select count (1)
> from t
> where BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy')
> and c1 is not null ;
>
> I cannot explain the behaviour of the second query. Can anyone help ?
I think it's an example of how Oracle starts with generic code for a task, and then gradually refines it for special cases.
The optimizer doesn't handle the special case of a list partition (I am assuming you have list partitioning here) with one item in the list.
The table access happens in 9i and 10g, but by 11g the table access disappears.
As a side-note - the predicate "c1 is null" becomes redundant after the optimizer has decided that it can use a single column index on that column to satisfy the count. However, the predicate is still tested as Oracle walks the index. Again, you can see an example of how, in principle, there is a special case that could be made a little more efficient.
(The predicate allows the optimizer to make the decision that it is legal to use the index, of course - but once the decision has been made, every entry in the index is guaranteed to satisfy the condition because it's a single column index).
-- 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.htmlReceived on Fri Jan 30 2009 - 04:05:43 CST