Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap Index Problem
It's damn near impossible to read the output
you have sent, but it is actually saying that
in the first case Oracle is doing a full scan of
partition 52, and in the second case it is
concatenating the result of a list of
full table-scans based on a query that offers
a list of partitioning keys. The appearance of
the word KEY(). The bitmap index is NOT being
used in either case.
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk wax_man_at_my-deja.com wrote in message <8podq5$3o7$1_at_nnrp1.deja.com>...Received on Sun Sep 17 2000 - 04:00:33 CDT
>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.