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 -> All bitmap indexes not being used in a query where they should be

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

From: Steve B <BigBoote66_at_hotmail.com>
Date: 21 Jan 2004 13:27:13 -0800
Message-ID: <67bcf80a.0401211327.2fbd9a33@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 Wed Jan 21 2004 - 15:27:13 CST












Original text of this message













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