Why Does Query Require Table Access

From: <bobf32_at_googlemail.com>
Date: Fri, 30 Jan 2009 00:53:41 -0800 (PST)
Message-ID: <0ffb4e48-0137-411a-83f3-cddf99af64d3_at_u18g2000pro.googlegroups.com>


I have a table t, partitioned by business_date, one partition per day. I have a locally partitioned index on this table on column c1. The following query performs as expected and accesses only one partition of the index, and doesn't touch the table at all (as expected):

select count (1)
from t partition (p20081031)
where c1 is not null ;

However the following query produces a plan involving a full index scan coupled with a table index rowid.

select count (1)
from t
where BUSINESS_DATE = to_date ('31-jan-2008', 'dd-mon-yyyy') and c1 is not null ;

I cannot explain the behaviour of the second query. Can anyone help ? Received on Fri Jan 30 2009 - 02:53:41 CST

Original text of this message