Re: Why Does Query Require Table Access

From: <bobf32_at_googlemail.com>
Date: Fri, 30 Jan 2009 06:22:45 -0800 (PST)
Message-ID: <c4a96605-dafd-400b-8171-178134ee2d13_at_v39g2000pro.googlegroups.com>



On 30 Jan, 14:02, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <bob..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

The business date is not in the index. I get it now. Thanks very much for your persistence. Received on Fri Jan 30 2009 - 08:22:45 CST

Original text of this message