Re: Why Does Query Require Table Access

From: Shakespeare <>
Date: Fri, 30 Jan 2009 17:01:54 +0100
Message-ID: <49832466$0$183$>

Jonathan Lewis schreef:
> <> wrote in message
>> 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.

> Remind me - is the business_date column in the index ?
> If not, Oracle HAS to visit the table because there may
> be rows in the partition where business_date does not
> match your business_date predicate.
> If you want a predicate SHOULDN'T visit the table, it
> would be something like:
> business_date >= to_date('31-Jan-2008','dd-Mon-yyyy')
> and business_date < to_date('01-Feb-2008','dd-Mon-yyyy')
> Note the necessity of ">=" compared to "<" when dealing
> with range partitions.

Would Oracle be so clever to avoid to read the table when one uses: trunc(business_date) = to_date(....)?

Shakespeare Received on Fri Jan 30 2009 - 10:01:54 CST

Original text of this message