Re: Bitmap Index Problem

If you would be interested in an indexing method that would guarantee use of the index, check out OMNIDEX at http://www.disc.com/odxover.html or contact me.

Cheryl Grandy
cgrandy_at_disc.com
303 444-4000
www.disc.com/home

In article <8podq5$3o7$1_at_nnrp1.deja.com>,   wax_man_at_my-deja.com wrote:
> 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]
> [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
> [NULL]
>
> 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.
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever


Sent via Deja.com http://www.deja.com/
Before you buy.