Trying to make some sense out of this observation in Oracle.
SELECT ... FROM MY_TABLE WHERE PART_KEY=x and DT_KEY between 20120101 and 20120731
Uses an index scan on PART_KEY/DT_KEY, even though autotrace shows that the index scan hits more blocks (one at a time, with single-block reads) than a full scan would have hit (in a single multi-block read). Stats are up to date.
The strange part is if I try with a copy of the same table and index WITHOUT partitioning, there seems to be a much higher threshold of selectivity before using the index - the range I gave above did a full scan, and only a very narrow range of values for
DT_KEY between d1 and d2 would use the index. I verified the index would be used in the non-partitioned case as well but Oracle seems more biased towards doing a full scan.
How is Oracle making the decision to use the index instead of a full scan, and what else should I be looking at? Stats are up to date since I collected them right away.
CREATE TABLE MY_TABLE ( PART_KEY NUMBER(10) NOT NULL, DT_KEY NUMBER(8) NOT NULL, ... ) PARTITION BY LIST(PART_KEY) ( PARTITION P1 ... ) AS SELECT .... FROM [source table] CREATE INDEX MY_INDEX ON MY_TABLE(PART_KEY, DT_KEY) LOCAL; EXEC DBMS_STATS.GATHER_TABLE_STATS('[USERNAME]', 'MY_TABLE');