| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: All bitmap indexes not being used in a query where they should be
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 counterproductive 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...Received on Thu Jan 22 2004 - 03:16:22 CST
> 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
> )
> ;
 |
 |