Re: Why Does Query Require Table Access

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

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


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

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

Original text of this message