Re: Why Does Query Require Table Access

From: ddf <oratune_at_msn.com>
Date: Fri, 30 Jan 2009 08:34:15 -0800 (PST)
Message-ID: <57989654-0d6d-41ad-b55d-c7adeddbaf64_at_i24g2000prf.googlegroups.com>

On Jan 30, 10:01 am, Shakespeare <what..._at_xs4all.nl> wrote:
> Jonathan Lewis schreef:
>
>
>
>
>
> > <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.
>
> Would Oracle be so clever to avoid to read the table when one uses:
> trunc(business_date) = to_date(....)?
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

Only if there exists a function-based index on trunc(business_date), I'd expect.

David Fitzjarrell Received on Fri Jan 30 2009 - 10:34:15 CST