Re: Why Does Query Require Table Access
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 30 Jan 2009 17:01:54 +0100
Message-ID: <49832466$0$183$e4fe514c_at_news.xs4all.nl>
Jonathan Lewis schreef:
> <bobf32_at_googlemail.com> wrote in message
> news:038e7028-61f0-4a3a-a7a2-bc6a11ae15d7_at_x6g2000pre.googlegroups.com...
>
>
> 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.
>
Date: Fri, 30 Jan 2009 17:01:54 +0100
Message-ID: <49832466$0$183$e4fe514c_at_news.xs4all.nl>
Jonathan Lewis schreef:
> <bobf32_at_googlemail.com> wrote in message
> news:038e7028-61f0-4a3a-a7a2-bc6a11ae15d7_at_x6g2000pre.googlegroups.com...
>
>> 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