Re: Why Does Query Require Table Access

<bobf32_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 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.html