Re: Why Does Query Require Table Access
Date: Fri, 30 Jan 2009 14:02:21 -0000
Message-ID: <rrGdnZ5h8tPxlR7UnZ2dnUVZ8svinZ2d_at_bt.com>
<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.
-- 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.htmlReceived on Fri Jan 30 2009 - 08:02:21 CST