Re: Why Does Query Require Table Access
From: <bobf32_at_googlemail.com>
Date: Fri, 30 Jan 2009 05:49:35 -0800 (PST)
Message-ID: <038e7028-61f0-4a3a-a7a2-bc6a11ae15d7_at_x6g2000pre.googlegroups.com>
On 30 Jan, 13:21, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bob..._at_googlemail.com> wrote in message
>
> news:1df5d2a8-4484-4551-a356-49a18ba01d13_at_k36g2000pri.googlegroups.com...
>
>
>
> > > select count (1)
> > > from t
> > > where BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy')
> > > and c1 is not null ;
>
> > 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.
>
> I would be a little more generous and call it an example of
> incompleteness, rather than error.
>
> However, if the table is RANGE partitioned, then the behaviour
> is as expected. I had assumed LIST partitioning because that's
> when the table visit should be redundant.
>
> In your case, you query for:
> BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy')
>
> But with daily partitions, the partition that holds data for 31st Jan
> could hold all possible times within that day, and the optimizer has
> no way of knowing that all the rows also satisfy the test
> business_date = trunc(business_date)
>
> (And adding the constraint doesn't seem to help, even in 11g)
>
> --
> 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 05:49:35 -0800 (PST)
Message-ID: <038e7028-61f0-4a3a-a7a2-bc6a11ae15d7_at_x6g2000pre.googlegroups.com>
On 30 Jan, 13:21, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bob..._at_googlemail.com> wrote in message
>
> news:1df5d2a8-4484-4551-a356-49a18ba01d13_at_k36g2000pri.googlegroups.com...
>
>
>
> > > select count (1)
> > > from t
> > > where BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy')
> > > and c1 is not null ;
>
> > 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.
>
> I would be a little more generous and call it an example of
> incompleteness, rather than error.
>
> However, if the table is RANGE partitioned, then the behaviour
> is as expected. I had assumed LIST partitioning because that's
> when the table visit should be redundant.
>
> In your case, you query for:
> BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy')
>
> But with daily partitions, the partition that holds data for 31st Jan
> could hold all possible times within that day, and the optimizer has
> no way of knowing that all the rows also satisfy the test
> business_date = trunc(business_date)
>
> (And adding the constraint doesn't seem to help, even in 11g)
>
> --
> 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
No it doesn't. But both queries know to only access one partition as partition elimination occurs in both cases. My point is that the first query only does index access to one partition as it knows it can answer the query without hitting the table. The second query should do the same and is functionally equivalent as far as I can tell, but it insists on probing the index (one partition) and then the table access by local index rowid. It is this table access I dispute. Received on Fri Jan 30 2009 - 07:49:35 CST