11 Replies Latest reply: Nov 12, 2012 3:52 PM by Dom Brooks RSS

    Index not picked up by optimizer

    amitavachatterjee1975
      Hi,

      In my SQL query I have a where clause which contains reference to 2 table columns for which I have indexes, but no matter how, those indexes are NOT being used by the optimizer as a result I am getting "TABLE ACCESS FULL" for those tables. 2 of the indexes are "non-unique" and I did "...COMPUTE STATISTICS" for the indexes. Needless to say I have used the INDEX hint also. Nothing worked.
      The SQL query is like

      select ...
      from sales, item, salesitem si, salesitemfee sif, salespayment sp, paytype pt, paymethod pm, county, org, org_location org_loc
      where
      SALES.salesid = si.salesid
      AND SALES.salesid = sp.salesid
      AND sp.paytypeid = pt.paytypeid
      AND pt.paymethodid = pm.paymethodid
      AND SALES.locationid = org_loc.locationid
      AND org_loc.orgid = org.orgid
      AND SALES.countyid = county.countyid
      AND si.itemid = item.itemid
      AND sif.salesitemid = si.salesitemid
      and sales.postingDate >= to_date('10-01-2012','mm--dd-yyyy')
      and sales.postingDate < to_date('10-31-2012','mm--dd-yyyy')
      group by si.itemid, si.salesItemid, itemname, countyname, orgname;

      The indexes are on salesitem and salesitemfee which are relevant here.

      I have run out of ides. Please help me.

      Thanks
      Amitava.
        • 1. Re: Index not picked up by optimizer
          sb92075
          amitavachatterjee1975 wrote:
          Hi,

          In my SQL query I have a where clause which contains reference to 2 table columns for which I have indexes, but no matter how, those indexes are NOT being used by the optimizer as a result I am getting "TABLE ACCESS FULL" for those tables. 2 of the indexes are "non-unique" and I did "...COMPUTE STATISTICS" for the indexes. Needless to say I have used the INDEX hint also. Nothing worked.
          The SQL query is like

          select ...
          from sales, item, salesitem si, salesitemfee sif, salespayment sp, paytype pt, paymethod pm, county, org, org_location org_loc
          where
          SALES.salesid = si.salesid
          AND SALES.salesid = sp.salesid
          AND sp.paytypeid = pt.paytypeid
          AND pt.paymethodid = pm.paymethodid
          AND SALES.locationid = org_loc.locationid
          AND org_loc.orgid = org.orgid
          AND SALES.countyid = county.countyid
          AND si.itemid = item.itemid
          AND sif.salesitemid = si.salesitemid
          and sales.postingDate >= to_date('10-01-2012','mm--dd-yyyy')
          and sales.postingDate < to_date('10-31-2012','mm--dd-yyyy')
          group by si.itemid, si.salesItemid, itemname, countyname, orgname;
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ

          The indexes are on salesitem and salesitemfee which are relevant here.
          neither of those names appear in the WHERE clause so why should Oracle use them?


          WHY MY INDEX IS NOT BEING USED
          http://communities.bmc.com/communities/docs/DOC-10031

          http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

          http://www.orafaq.com/tuningguide/not%20using%20index.html
          • 2. Re: Index not picked up by optimizer
            amitavachatterjee1975
            salesitem is si and salesitemfeeis sif. The where clause use these aliases.
            • 3. Re: Index not picked up by optimizer
              sb92075
              amitavachatterjee1975 wrote:
              salesitem is si and salesitemfeeis sif. The where clause use these aliases.
              COLUMNS are indexed; not tables.

              post DDL for all TABLES & INDEXES involved with this SQL
              • 4. Re: Index not picked up by optimizer
                amitavachatterjee1975
                SALESITEMFEE
                ---------------------
                CREATE TABLE "ALINT1"."SALESITEMFEE"
                (     "SALESITEMID" NUMBER(16,0) NOT NULL ENABLE,
                     "FEEID" NUMBER(6,0) NOT NULL ENABLE,
                     "FEETYPEID" NUMBER(2,0) NOT NULL ENABLE,
                     "FUNDID" NUMBER(2,0),
                     "AMOUNT" NUMBER(8,2) DEFAULT 0,
                     "OVERRIDEOPERATORID" NUMBER(8,0),
                     CONSTRAINT "SALESITEMFEE__SALESITEMID_FK" FOREIGN KEY ("SALESITEMID")
                     REFERENCES "ALINT1"."SALESITEM" ("SALESITEMID") ENABLE,
                     CONSTRAINT "SALESITEMFEE__FEEID_FK" FOREIGN KEY ("FEEID")
                     REFERENCES "ALINT1"."FEE" ("FEEID") ENABLE,
                     CONSTRAINT "SALESITEMFEE__FUNDID_FK" FOREIGN KEY ("FUNDID")
                     REFERENCES "ALINT1"."FUND" ("FUNDID") ENABLE,
                     CONSTRAINT "SALESITEMFEE__FEETYPEID_FK" FOREIGN KEY ("FEETYPEID")
                     REFERENCES "ALINT1"."FEETYPE" ("FEETYPEID") ENABLE
                ) SEGMENT CREATION IMMEDIATE
                TABLESPACE "AL_DATA" ;


                CREATE INDEX "ALINT1"."SALESITEMFEE__SALESITEMID_K" ON "ALINT1"."SALESITEMFEE" ("SALESITEMID")
                TABLESPACE "AL_INDEX" ;

                SALESITEM
                ----------------
                CREATE TABLE "ALINT1"."SALESITEM"
                (     "SALESITEMID" NUMBER(16,0),
                     "SALESID" NUMBER(12,0) NOT NULL ENABLE,
                     "ITEMID" NUMBER(6,0) NOT NULL ENABLE,
                     "ITEMAMOUNT" NUMBER(8,2) DEFAULT 0,
                     "ITEMQTY" NUMBER(3,0) DEFAULT 0,
                     "ITEMTOTAL" NUMBER(8,2) DEFAULT 0,
                     "OVERRIDECOMMENT" VARCHAR2(256 BYTE),
                     CONSTRAINT "SALESITEM__SALESITEMID_PK" PRIMARY KEY ("SALESITEMID")
                USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
                STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
                TABLESPACE "ALCIS_INDEX" ENABLE,
                     CONSTRAINT "SALESITEM__SALESID_FK" FOREIGN KEY ("SALESID")
                     REFERENCES "ALINT1"."SALES" ("SALESID") ENABLE,
                     CONSTRAINT "SALESITEM__ITEMID_FK" FOREIGN KEY ("ITEMID")
                     REFERENCES "ALINT1"."ITEM" ("ITEMID") ENABLE
                ) SEGMENT CREATION IMMEDIATE
                TABLESPACE "AL_DATA" ;


                CREATE UNIQUE INDEX "ALINT1"."SALESITEM__SALESITEMID_PK" ON "ALINT1"."SALESITEM" ("SALESITEMID")
                TABLESPACE "AL_INDEX" ;

                CREATE INDEX "ALINT1"."SALESITEM__SALESID_K" ON "ALINT1"."SALESITEM" ("SALESID")
                TABLESPACE "AL_INDEX" ;

                CREATE INDEX "ALINT1"."SALESITEM__ITEMID_K" ON "ALINT1"."SALESITEM" ("ITEMID")
                TABLESPACE "AL_INDEX" ;
                • 5. Re: Index not picked up by optimizer
                  SomeoneElse
                  as a result I am getting "TABLE ACCESS FULL" for those tables.
                  You also need to post the explain plan. We can't see it.
                  • 6. Re: Index not picked up by optimizer
                    amitavachatterjee1975
                    |   0 | SELECT STATEMENT                     |                         |    92 |  9476 |    62  (10)| 00:00:01 |
                    |   1 |  HASH GROUP BY                       |                         |    92 |  9476 |    62  (10)| 00:00:01 |
                    |*  2 |   FILTER                             |                         |       |       |            |          |
                    |*  3 |    HASH JOIN                         |                         |    92 |  9476 |    61   (9)| 00:00:01 |
                    |*  4 |     HASH JOIN                        |                         |    47 |  4324 |    42  (10)| 00:00:01 |
                    |*  5 |      HASH JOIN                       |                         |    47 |  3196 |    39   (8)| 00:00:01 |
                    |   6 |       NESTED LOOPS                   |                         |    38 |  2166 |    21  (15)| 00:00:01 |
                    |*  7 |        HASH JOIN                     |                         |    38 |  2014 |    21  (15)| 00:00:01 |
                    |*  8 |         HASH JOIN                    |                         |    38 |  1406 |    18  (12)| 00:00:01 |
                    |*  9 |          HASH JOIN                   |                         |    38 |  1102 |    16   (7)| 00:00:01 |
                    |  10 |           TABLE ACCESS BY INDEX ROWID| SALES                   |    38 |   684 |    13   (0)| 00:00:01 |
                    |* 11 |            INDEX RANGE SCAN          | SALES__POSTINGDATE_K    |    38 |       |     2   (0)| 00:00:01 |
                    |  12 |           VIEW                       | index$_join$_008        |    68 |   748 |     3  (34)| 00:00:01 |
                    |* 13 |            HASH JOIN                 |                         |       |       |            |          |
                    |  14 |             INDEX FAST FULL SCAN     | COUNTY__COUNTYID_PK     |    68 |   748 |     1   (0)| 00:00:01 |
                    |  15 |             INDEX FAST FULL SCAN     | COUNTY__COUNTYNAME_UK   |    68 |   748 |     1   (0)| 00:00:01 |
                    |  16 |          INDEX FULL SCAN             | ORG_LOCATION__PK        |   233 |  1864 |     1   (0)| 00:00:01 |
                    |  17 |         VIEW                         | index$_join$_009        |   111 |  1776 |     3  (34)| 00:00:01 |
                    |* 18 |          HASH JOIN                   |                         |       |       |            |          |
                    |  19 |           INDEX FAST FULL SCAN       | ORG__ORGID_PK           |   111 |  1776 |     1   (0)| 00:00:01 |
                    |  20 |           INDEX FAST FULL SCAN       | ORG__ORGNAME_UK         |   111 |  1776 |     1   (0)| 00:00:01 |
                    |* 21 |        INDEX UNIQUE SCAN             | SALESPAYMENT__SALESID_K |     1 |     4 |     0   (0)| 00:00:01 |
                    |  22 |       TABLE ACCESS FULL              | SALESITEM               |  5538 | 60918 |    18   (0)| 00:00:01 |
                    |  23 |      VIEW                            | index$_join$_002        |    71 |  1704 |     3  (34)| 00:00:01 |
                    |* 24 |       HASH JOIN                      |                         |       |       |            |          |
                    |  25 |        INDEX FAST FULL SCAN          | ITEM__ITEMID_PK         |    71 |  1704 |     1   (0)| 00:00:01 |
                    |  26 |        INDEX FAST FULL SCAN          | ITEM__ITEMNAME_UK       |    71 |  1704 |     1   (0)| 00:00:01 |
                    |  27 |     TABLE ACCESS FULL                | SALESITEMFEE            | 10696 |   114K|    18   (0)| 00:00:01 |
                    ----------------------------------------------------------------------------------------------------------------
                    Notice TABLE ACCESS FULL for both SALESITEM & SALESITEMFEE tables.

                    Edited by: amitavachatterjee1975 on Nov 12, 2012 1:18 PM
                    • 7. Re: Index not picked up by optimizer
                      SomeoneElse
                      You should edit your post and add
                       tags.  One at the top and one at the bottom.                                                                                                                                                                            
                      • 8. Re: Index not picked up by optimizer
                        amitavachatterjee1975
                        I think I have given enough info to have any suggestions; if you have any please share.
                        • 9. Re: Index not picked up by optimizer
                          SomeoneElse
                          According to the explain plan, the query is taking 1 second. Is that correct? Are the cardinality estimates correct (or at least close)?
                          • 10. Re: Index not picked up by optimizer
                            rp0428
                            >
                            I think I have given enough info to have any suggestions; if you have any please share.
                            >
                            You haven't provided any information that indicates that there is any performance problem or given any reason why you think the index should be used.

                            What makes you think that Oracle has chosen the wrong execution path? There aren't any filter predicates that restrict the rows from those tables and the time is showing only 1 second.

                            There doesn't appear to be any problem so why are you trying to fix it?
                            • 11. Re: Index not picked up by optimizer
                              Dom Brooks
                              I think I have given enough info to have any suggestions; if you have any please share.
                              This is what is normally considered the minimum information for a sql tuning request:
                              HOW TO: Post a SQL statement tuning request - template posting

                              The predicates section of the execution plan is considered mandatory
                              Needless to say I have used the INDEX hint also. Nothing worked.
                              Then you're doing it wrong.

                              Bottom line, you're not selecting much data.
                              Why do you think an index would be cheaper?

                              Execution plans are all about the optimizer picking the cheapest plan based on its estimates.
                              If the estimates are inaccurate, then there's a good chance the plan could be suboptimal.

                              Bottom line, full tables scans and fast full index scans are multiblock operations so can work out cheaper than nested loop lookups of single block range scans.
                              It all depends.