Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap Index Problem

Re: Bitmap Index Problem

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 17 Sep 2000 10:00:33 +0100
Message-ID: <969181513.22753.0.nnrp-14.9e984b29@news.demon.co.uk>

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>...

>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.
Received on Sun Sep 17 2000 - 04:00:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US