Re: Why Does Query Require Table Access
From: <bobf32_at_googlemail.com>
Date: Fri, 30 Jan 2009 04:33:01 -0800 (PST)
Message-ID: <1df5d2a8-4484-4551-a356-49a18ba01d13_at_k36g2000pri.googlegroups.com>
On 30 Jan, 10:05, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bob..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Date: Fri, 30 Jan 2009 04:33:01 -0800 (PST)
Message-ID: <1df5d2a8-4484-4551-a356-49a18ba01d13_at_k36g2000pri.googlegroups.com>
On 30 Jan, 10:05, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bob..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Thanks for the reply Jonathan. If I understand correctly it is essentially a bug in releases < 11g. I would say though that the table is range, rather than list partitioned. Should have made that clear. Received on Fri Jan 30 2009 - 06:33:01 CST