Bitmap Index Problem

I'm having a problem with a bitmap partition index. I have a large table (~500M rows) that is split into ~56 partitions. The partitions are split on a column called snapshot_month which is a number that is equiv of yyyymm.

Anyway, if I run a query like:

select * from table where snapshot_month=200006;

the explain plan states that it is doing a full table scan.

However, if I do a query like:

select * from table where snapshot_month in (200006,200007);

then the explain plan seems to say that it is using the bitmap index.

Why is it not using it on the first query? Since it should provide a direct hit against an index that at the most will return 1/56 of the table, it seems that it should be using it.

This is running 8.0.5 and both the table and index have been analyzed.

Here are the outputs from the 2 explain plans:

STATEMENT_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS
OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_INSTANCE
OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID
POSITION	COST	CARDINALITY	BYTES	OTHER_TAG
PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER
chris	09/13/2000	[NULL]	SELECT STATEMENT	[NULL]	[NULL]


[NULL]	[NULL]	[NULL]	[NULL]	CHOOSE	[NULL]	0	[NULL]	30276

30276	11136693	1559137020	[NULL]	[NULL]	[NULL]	[NULL]

chris	09/13/2000	[NULL]	TABLE ACCESS	FULL	[NULL]
INFORMENT	H_MCDASH_LOAN_LEVEL	1	[NULL]	ANALYZED


[NULL]	1	0	1	30276	11136693	1559137020

[NULL]	NUMBER(52)	NUMBER(52)	1	[NULL]




STATEMENT_ID	TIMESTAMP	REMARKS	OPERATION	OPTIONS
OBJECT_NODE	OBJECT_OWNER	OBJECT_NAME	OBJECT_INSTANCE
OBJECT_TYPE	OPTIMIZER	SEARCH_COLUMNS	ID	PARENT_ID
POSITION	COST	CARDINALITY	BYTES	OTHER_TAG
PARTITION_START	PARTITION_STOP	PARTITION_ID	OTHER

chris1	09/13/2000	[NULL]	SELECT STATEMENT	[NULL]	[NULL]


[NULL]	[NULL]	[NULL]	[NULL]	CHOOSE	[NULL]	0	[NULL]	22769

22769	378622	53007080	[NULL]	[NULL]	[NULL]	[NULL]	[NULL]

chris1	09/13/2000	[NULL]	PARTITION	CONCATENATED	[NULL]


[NULL]	[NULL]	[NULL]	[NULL]	[NULL]	[NULL]	1	0	1

[NULL]	[NULL]	[NULL]	[NULL]	KEY(INLIST)	KEY(INLIST)	1

chris1	09/13/2000	[NULL]	TABLE ACCESS	FULL	[NULL]
INFORMENT	H_MCDASH_LOAN_LEVEL	1	[NULL]	ANALYZED


[NULL]	2	1	1	22769	378622	53007080	[NULL]

KEY(INLIST)	KEY(INLIST)	1	[NULL]

Thanks,

wax_man

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Sep 13 2000 - 12:33:58 CDT