Re: All bitmap indexes not being used in a query where they should be

The statistics are up to date, and the two indexes it is choosing are the most restrictive, but the fact remains that the plan the optimizer is choosing is, in fact, wrong (or more specifically, the cost the optimizer is computing is wrong). This particular table has about 3.3 million rows, and the two indexes in question have about 2000 key entries each, so "in theory", constraining on the two index should divide the table up in to 4 million compartments. However, I sure hope Oracle isn't using this method of naive math in order to choose its plan, since it would be make the assumption that the columns in a fact table are completely independent from one another (a very big assumption, and almost certainly false for nearly all data warehouses).

Somehow the optimizer is thinking that the two indexes adequately "cover" the data I want (even though they only reduce the result set to 16,000 rows), so it views the inclusion of the other indexes as a needless expense. But when it executes the count(*) query, it sees that it can be resolved by never hitting the base table, so it uses the rest of the indexes (the trace file shows the count(*) query is "index only"). But these assumptions by the optimizer are incorrect. I'm wondering if there's some way of getting it to use all the bitmap indexes, whether it thinks its the right idea or not.

Also, I think you may be misinterpreting the "Card" statistic in the query plan. I have always understood it to mean that this is how many times the optimizer anticipates executing that step in the plan. For example, a full scan of a table can still be Card=1, even though it may be returning millions of rows, but a join from say an Invoice table to an LineItem table will often have a Card=N, where N are the number of invoices you expect to match in the query (not the average or total number of LineItems you expect to receive). The Cost statistic reflects how expensive a particular step is (I'm not sure whether the Cost already includes the Card multiplier or not).

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<buo4d6$r5f$1_at_sparta.btinternet.com>...
> Note the line in the execution plan that reads:
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EXPOSUREANALYSIS'
> (Cost=7 Card=1 Bytes=312)
>
> Oracle has calculated that the number of rows
> that will be identified by the combination of just
> these two indexes is 1 - so it would be counter-
> productive to visit any more indexes.
>
> I don't have 8.1.6 around, so I can't tell you
> if this is a bug, or a result of your statistics;
> but check
> user_tab_columns.num_distinct
> for the two used indexes. Oracle should have
> worked out the number of rows that will be
> returned by multiplying the two num_distinct
> together and dividing into the total number of
> rows in the table. (Technically, it should have
> used the density column - but unless you have
> histograms, that will be 1/num_distinct).
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearance2:
> March 2004 Hotsos Symposium - Keynote
> March 2004 Charlotte NC - OUG Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Steve B" <BigBoote66_at_hotmail.com> wrote in message
> news:67bcf80a.0401211327.2fbd9a33_at_posting.google.com...
> > We're running Oracle 8.1.6.1.0. We have the table ExposureAnalysis
> > (see DDL at end of message), with several million rows. We have many
> > queries that go against this table, with a variety of constraints
> > against the Id columns. A typical query looks like this:
> >
> > SELECT /*+ FIRST_ROWS */ fact.exposureanalysisid
> > , fact.ScriptCategoryId
> > , fact.AnalyzeTime
> > , fact.FileSpec
> > , fact.ProcessStatus
> > , fact.ExposureId
> > , fact.MaskId
> > , fact.RecipeId
> > , fact.ExposureToolId
> > , l.LotName
> > , fact.LayerId
> > , l.DeviceId
> > , fact.RouteId
> > , fact.OperationId
> > , l.TechnologyId
> > , fact.Exposetime
> > , 0
> > , 0
> > , ' '
> > , ' '
> > , 0 FROM ExposureAnalysis fact
> > , Lot l
> > WHERE fact.MaskId = 11666
> > AND fact.LotId = l.LotId (+)
> > AND fact.ExposeTime + 1 - 1 >= '2004.01.01 12:34:22'
> > AND fact.LayerId = 64
> > AND fact.ExposeTime + 1 - 1 <= '2004.01.21 12:34:22'
> > AND fact.FileSpec= 'CD Feedback'
> > AND fact.OperationId = 16108
> > AND fact.ProcessStatus = 'D'
> > AND fact.ScriptCategoryId = 106
> > AND fact.ExposureToolId = 183
> > AND fact.ExposeHash >= 1462
> > AND fact.ExposeHash <= 1482;
> >
> >
> > EXPOSUREANALYSISID SCRIPTCATEGORYID ANALYZETIME FILESPEC
> > ------------------ ---------------- -------------------
> > --------------------------------------------
> > 4678177 106 2004.01.21 12:35:01 CD Feedback
> > 4677727 106 2004.01.21 11:52:13 CD Feedback
> > 4677447 106 2004.01.21 11:27:51 CD Feedback
> > 4677717 106 2004.01.21 11:51:52 CD Feedback
> > 4677677 106 2004.01.21 11:47:53 CD Feedback
> > 4677675 106 2004.01.21 11:47:35 CD Feedback
> > 4677445 106 2004.01.21 11:27:32 CD Feedback
> > 4677415 106 2004.01.21 11:23:51 CD Feedback
> > 4677397 106 2004.01.21 11:23:21 CD Feedback
> > 4673476 106 2004.01.21 05:45:31 CD Feedback
> > 4673474 106 2004.01.21 05:45:08 CD Feedback
> > 4673434 106 2004.01.21 05:41:03 CD Feedback
> > 4673419 106 2004.01.21 05:40:31 CD Feedback
> >
> > 13 rows selected.
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=333)
> > 1 0 SORT (ORDER BY) (Cost=10 Card=1 Bytes=333)
> > 2 1 NESTED LOOPS (OUTER) (Cost=8 Card=1 Bytes=333)
> > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EXPOSUREANALYSIS'
> > (Cost=7 Card=1 Bytes=312)
> > 4 3 BITMAP CONVERSION (TO ROWIDS)
> > 5 4 BITMAP AND
> > 6 5 BITMAP INDEX (SINGLE VALUE) OF
> > 'DN19EXPOSUREANALYSIS'
> > 7 5 BITMAP INDEX (SINGLE VALUE) OF
> > 'DN9EXPOSUREANALYSIS'
> > 8 2 TABLE ACCESS (BY INDEX ROWID) OF 'LOT' (Cost=1
> > Card=26618 Bytes=558978)
> > 9 8 INDEX (UNIQUE SCAN) OF 'PKLOT' (UNIQUE)
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 3079 consistent gets
> > 1753 physical reads
> > 60 redo size
> > 1872 bytes sent via SQL*Net to client
> > 316 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 3 sorts (memory)
> > 0 sorts (disk)
> >
> > Thousands of gets and this much physical i/o is very undesirable.
> > Almost all of the columns in the where clause have a bitmap index (the
> > exposetime column is "obfuscated" with a +1-1 in order to prevent the
> > optimizer from using index IE23ExposureAnalysis, which is not
> > desirable for these queries). The query plan above only uses 2 of the
> > 7 possible bitmap indexes in this query, and the using just the two
> > indexes only reduces the set of potential rows down to 16,000 rows,
> > which the server then has to examine one by one to resolve the
> > remaining constraints.
> >
> > This can be illustrated by executing the following count(*) query,
> > which doesn't require the server to access the underlying
> > ExposureAnalysis table and can use only indexes. It contains the same
> > WHERE clause as the query above, but with any reference to columns
> > that don't have bitmap indexes removed:
> >
> > SELECT count(*)
> > FROM ExposureAnalysis fact
> > WHERE fact.MaskId = 11666
> > AND fact.LayerId = 64
> > AND fact.FileSpec= 'CD Feedback'
> > AND fact.OperationId = 16108
> > AND fact.ProcessStatus = 'D'
> > AND fact.ScriptCategoryId = 106
> > AND fact.ExposureToolId = 183
> > AND fact.ExposeHash >= 1462
> > AND fact.ExposeHash <= 1482;
> >
> > COUNT(*)
> > ----------
> > 16
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=1 Bytes=276)
> > 1 0 SORT (AGGREGATE)
> > 2 1 BITMAP CONVERSION (COUNT)
> > 3 2 BITMAP AND
> > 4 3 BITMAP INDEX (SINGLE VALUE) OF
> > 'DN19EXPOSUREANALYSIS'
> > 5 3 BITMAP INDEX (SINGLE VALUE) OF 'DN9EXPOSUREANALYSIS'
> > 6 3 BITMAP INDEX (SINGLE VALUE) OF 'DN7EXPOSUREANALYSIS'
> > 7 3 BITMAP MERGE
> > 8 7 BITMAP INDEX (RANGE SCAN) OF
> > 'DN12EXPOSUREANALYSIS'
> > 9 3 BITMAP INDEX (SINGLE VALUE) OF
> > 'IF364#ANALYSISSCRIPT'
> > 10 3 BITMAP INDEX (SINGLE VALUE) OF
> > 'DN11EXPOSUREANALYSIS'
> > 11 3 BITMAP INDEX (SINGLE VALUE) OF
> > 'IE21EXPOSUREANALYSIS'
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 299 consistent gets
> > 0 physical reads
> > 0 redo size
> > 205 bytes sent via SQL*Net to client
> > 315 bytes received via SQL*Net from client
> > 2 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 1 rows processed
> >
> > You can see that the gets are significantly reduced, and that all the
> > bitmap indexes are participating in the query, to great benefit.
> > However, if we change the above query by adding just a single
> > reference to the underlying table (doing a select of the
> > exposureanalysisid instead of a count(*), the performance immediately
> > degenerates into the case of the larger query above:
> >
> > SELECT exposureanalysisid
> > FROM ExposureAnalysis fact
> > WHERE fact.MaskId = 11666
> > AND fact.LayerId = 64
> > AND fact.FileSpec= 'CD Feedback'
> > AND fact.OperationId = 16108
> > AND fact.ProcessStatus = 'D'
> > AND fact.ScriptCategoryId = 106
> > AND fact.ExposureToolId = 183
> > AND fact.ExposeHash >= 1462
> > AND fact.ExposeHash <= 1482
> >
> >
> > EXPOSUREANALYSISID
> > ------------------
> > 4673419
> > 4673434
> > 4673474
> > 4673476
> > 4677397
> > 4677415
> > 4677445
> > 4677447
> > 4677675
> > 4677677
> > 4677717
> > 4677727
> > 4678177
> > 4678191
> > 4678230
> > 4678245
> >
> > 16 rows selected.
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=281)
> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EXPOSUREANALYSIS'
> > (Cost=7 Card=1 Bytes=281)
> > 2 1 BITMAP CONVERSION (TO ROWIDS)
> > 3 2 BITMAP AND
> > 4 3 BITMAP INDEX (SINGLE VALUE) OF
> > 'DN19EXPOSUREANALYSIS'
> > 5 3 BITMAP INDEX (SINGLE VALUE) OF 'DN9EXPOSUREANALYSIS'
> >
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 3038 consistent gets
> > 0 physical reads
> > 0 redo size
> > 611 bytes sent via SQL*Net to client
> > 384 bytes received via SQL*Net from client
> > 3 SQL*Net roundtrips to/from client
> > 1 sorts (memory)
> > 0 sorts (disk)
> >
> > All attempts to make the optimizer recognize the other bitmap indexes
> > has failed (I have tried using INDEX_COMBINE(ExposureAnalysis
> > DN19EXPOSUREANALYSIS DN9EXPOSUREANALYSIS DN7EXPOSUREANALYSIS
> > DN12EXPOSUREANALYSIS IF364#ANALYSISSCRIPT DN11EXPOSUREANALYSIS
> > IE21EXPOSUREANALYSIS) hint, but this has no effect - in fact, I've
> > never been able to get INDEX or INDEX_COMBINE hints to be recognized
> > by the optimizer).
> >
> > Does anyone have any ideas on how I can kick the first or third query
> > such that it uses indexes in the same manner as the second one?
> >
> > ===============================
> >
> > CREATE TABLE ExposureAnalysis (
> > ExposureAnalysisId NUMBER(22) DEFAULT Null NOT NULL,
> > ScriptCategoryId NUMBER(22) DEFAULT Null NOT NULL,
> > FileSpec CHAR(255) DEFAULT Null NOT NULL,
> > ExposureRawDataId NUMBER(22) DEFAULT Null NOT NULL,
> > AnalyzeTime DATE DEFAULT Null NULL,
> > ProcessStatus CHAR(1) DEFAULT Null NULL,
> > AnalyzeHash NUMBER DEFAULT 0 NULL,
> > SentToServerDate DATE DEFAULT Null NULL,
> > ComputationServerId NUMBER(22) DEFAULT Null NULL,
> > PersistedMessagesId NUMBER(22) DEFAULT Null NULL,
> > AnalysisCategoryId NUMBER(22) DEFAULT Null NULL,
> > ExposureId NUMBER(22) DEFAULT Null NULL,
> > Name CHAR(32) DEFAULT Null NULL,
> > AnalysisEventName CHAR(40) DEFAULT Null NULL,
> > AnalysisClassificationName CHAR(40) DEFAULT Null NULL,
> > AnalysisDataName CHAR(40) DEFAULT Null NULL,
> > Version CHAR(40) DEFAULT Null NULL,
> > LayerId NUMBER(22) DEFAULT Null NULL,
> > LotId NUMBER(22) DEFAULT Null NULL,
> > MaskId NUMBER(22) DEFAULT Null NULL,
> > RecipeId NUMBER(22) DEFAULT Null NULL,
> > ExposureToolId NUMBER(22) DEFAULT Null NULL,
> > RouteId NUMBER(22) DEFAULT Null NULL,
> > OperationId NUMBER(22) DEFAULT Null NULL,
> > TimeType VARCHAR2(20) DEFAULT Null NULL
> > CHECK (TimeType IN ('PreTrackIn',
> > 'TrackIn', 'DuringExposure', 'TrackOut', 'PostTrackOut',
> > 'Synthetic')),
> > ExposeTime DATE DEFAULT Null NULL,
> > ExposeHash NUMBER DEFAULT 0 NULL,
> > FeedbackSource CHAR(1) DEFAULT 'N' NOT NULL
> > CHECK (FeedbackSource IN ('Y',
> > 'N')),
> > ATASource CHAR(1) DEFAULT 'N' NOT NULL
> > CHECK (ATASource IN ('Y', 'N')),
> > FactoryHostSource CHAR(1) DEFAULT 'N' NOT NULL
> > CHECK (FactoryHostSource IN ('Y',
> > 'N')),
> > CreateDate DATE DEFAULT sysdate NULL,
> > ModDate DATE DEFAULT sysdate NULL
> > )
> > TABLESPACE ARGUS
> > STORAGE (
> > INITIAL 500K
> > NEXT 500K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE UNIQUE INDEX PKExposureAnalysis ON ExposureAnalysis
> > (
> > ExposureAnalysisId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE UNIQUE INDEX AKExposureAnalysis ON ExposureAnalysis
> > (
> > ExposureRawDataId ASC,
> > FileSpec ASC,
> > ScriptCategoryId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE INDEX IF1106#ComputationServer ON ExposureAnalysis
> > (
> > ComputationServerId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX IF364#AnalysisScript ON ExposureAnalysis
> > (
> > FileSpec ASC,
> > ScriptCategoryId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE INDEX DN1ExposureAnalysis ON ExposureAnalysis
> > (
> > ExposureId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN10ExposureAnalysis ON ExposureAnalysis
> > (
> > RecipeId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN11ExposureAnalysis ON ExposureAnalysis
> > (
> > ExposureToolId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN12ExposureAnalysis ON ExposureAnalysis
> > (
> > ExposeHash ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN13ExposureAnalysis ON ExposureAnalysis
> > (
> > AnalyzeHash ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN16ExposureAnalysis ON ExposureAnalysis
> > (
> > AnalysisCategoryId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN18ExposureAnalysis ON ExposureAnalysis
> > (
> > RouteId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN19ExposureAnalysis ON ExposureAnalysis
> > (
> > OperationId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN2ExposureAnalysis ON ExposureAnalysis
> > (
> > Name ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX IE21ExposureAnalysis ON ExposureAnalysis
> > (
> > ProcessStatus ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE INDEX IE22ExposureAnalysis ON ExposureAnalysis
> > (
> > PersistedMessagesId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE INDEX IE23ExposureAnalysis ON ExposureAnalysis
> > (
> > ExposeTime ASC,
> > ExposureAnalysisId ASC,
> > ScriptCategoryId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN3ExposureAnalysis ON ExposureAnalysis
> > (
> > AnalysisEventName ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN4ExposureAnalysis ON ExposureAnalysis
> > (
> > AnalysisClassificationName ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN5ExposureAnalysis ON ExposureAnalysis
> > (
> > AnalysisDataName ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN6ExposureAnalysis ON ExposureAnalysis
> > (
> > Version ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN7ExposureAnalysis ON ExposureAnalysis
> > (
> > LayerId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN8ExposureAnalysis ON ExposureAnalysis
> > (
> > LotId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
> >
> > CREATE BITMAP INDEX DN9ExposureAnalysis ON ExposureAnalysis
> > (
> > MaskId ASC
> > )
> > PCTFREE 40
> > TABLESPACE ARGUS_INDEX
> > STORAGE (
> > INITIAL 100K
> > NEXT 100K
> > PCTINCREASE 0
> > )
> > ;
Received on Fri Jan 23 2004 - 08:46:48 CST