Re: Why Does Query Require Table Access

From: Jonathan Lewis <>
Date: Fri, 30 Jan 2009 13:21:31 -0000
Message-ID: <>

<> wrote in message
> > 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)


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ 
Received on Fri Jan 30 2009 - 07:21:31 CST

Original text of this message