8

Sorry for the terrible column/table names but since this is for a work project I wanted to insure it was OK to ask. I was just hoping to at least learn why I'm not seeing my functional indices being used so I felt better about adding this indices in a production environment.

The query is using a view I've created that has a number of different columns with a where clause that does the following:

  ....
  AND e.sysid = NVL(wi.ALPHAid, -999)
  AND NVL(wi.ALPHAid,   -999)       <> -999
  AND NVL(wi.BRAVOid,   -999)        = -999
  AND NVL(wi.CHARLIEid, -999)        = -999
  ...

It is my understanding that Oracle cannot use indices if you pass the column through a function and instead you need to create function based indexes. So before I create the indices I get the following cost in my explain plan:

Plan hash value: 1233409744

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                |     1 |   223 |    56   (6)| 00:00:01 |
|   1 |  SORT ORDER BY                   |                                |     1 |   223 |    56   (6)| 00:00:01 |
|   2 |   HASH UNIQUE                    |                                |     1 |   223 |    55   (4)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                                |     1 |   223 |    54   (2)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                                |     1 |   136 |    49   (3)| 00:00:01 |
|   5 |      NESTED LOOPS OUTER          |                                |     1 |   112 |    48   (3)| 00:00:01 |
|*  6 |       HASH JOIN                  |                                |     1 |    87 |    48   (3)| 00:00:01 |
|*  7 |        HASH JOIN                 |                                |  3261 | 97830 |    29   (4)| 00:00:01 |
|   8 |         TABLE ACCESS FULL        | CHARLIE                        |  3261 | 39132 |    15   (0)| 00:00:01 |
|   9 |         TABLE ACCESS FULL        | BRAVO                          |  3261 | 58698 |    13   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS FULL         | ALPHA                          |  3291 |   183K|    19   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID| LOCATION                       |     1 |    25 |     0   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN          | ALPHA_SRVDELLOC_IN1            |     1 |       |     0   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID | DELTA                          |     1 |    24 |     1   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN          | DELTA_PK                       |     1 |       |     0   (0)| 00:00:01 |
|* 15 |     TABLE ACCESS BY INDEX ROWID  | ITEM                           |     1 |    87 |     5   (0)| 00:00:01 |
|* 16 |      INDEX SKIP SCAN             | IDX_ITM                        |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("PR"."SYSID"="E"."BRAVOID" AND "E"."CHARLIEID"="MR"."SYSID")
   7 - access("PR"."SYSID"="MR"."BRAVOID")
  10 - filter("E"."SYSID"<>(-999))
  12 - access("E"."SYSID"="SD"."ALPHAID"(+))
       filter("SD"."ALPHAID"(+)<>(-999))
  14 - access("PR"."DELTAID"="P"."SYSID")
  15 - filter(("WI"."TYPE"='XZ' OR "WI"."TYPE"='Z' OR 
              "WI"."TYPE"='X') AND "WI"."DELINQUENT"='F' AND ("WI"."ACTIVE"='F' OR 
              NVL("WI"."LOCKEDBY",(-999))<>(-999)) AND "WI"."SUSPENDED"='F' AND ("WI"."LOCKEDBY" 
              IS NULL OR "WI"."LOCKEDBY"=12))
  16 - access("WI"."CODE"='MISSING' AND "WI"."TERMINATED"='F')
       filter("WI"."TERMINATED"='F' AND NVL("WI"."ALPHAID",(-999))<>(-999) AND 
              NVL("WI"."BRAVOID",(-999))=(-999) AND NVL("WI"."CHARLIEID",(-999))=(-999) AND 
              "E"."SYSID"=NVL("WI"."ALPHAID",(-999)))

After creating the following indices:

CREATE INDEX "IDX_WFITEM_NVL_BRAVOID" ON ITEM (NVL(BRAVOID, -999));
CREATE INDEX "IDX_WFITEM_NVL_CHARLIEID" ON ITEM (NVL(CHARLIEID, -999));
CREATE INDEX "IDX_WFITEM_NVL_ALPHAID" ON ITEM (NVL(ALPHAID, -999));

I get the following plan:

Plan hash value: 1066773928

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |   232 |    12  (17)| 00:00:01 |
|   1 |  SORT ORDER BY                      |                                |     1 |   232 |    12  (17)| 00:00:01 |
|   2 |   HASH UNIQUE                       |                                |     1 |   232 |    11  (10)| 00:00:01 |
|   3 |    NESTED LOOPS                     |                                |       |       |            |          |
|   4 |     NESTED LOOPS                    |                                |     1 |   232 |    10   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                   |                                |     1 |   208 |     9   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                  |                                |     1 |   190 |     8   (0)| 00:00:01 |
|   7 |        NESTED LOOPS OUTER           |                                |     1 |   178 |     7   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                |                                |     1 |   153 |     7   (0)| 00:00:01 |
|*  9 |          TABLE ACCESS BY INDEX ROWID| ITEM                           |     1 |    96 |     6   (0)| 00:00:01 |
|* 10 |           INDEX SKIP SCAN           | IDX_ITM                        |     1 |       |     5   (0)| 00:00:01 |
|  11 |          TABLE ACCESS BY INDEX ROWID| ALPHA                          |     1 |    57 |     1   (0)| 00:00:01 |
|* 12 |           INDEX UNIQUE SCAN         | ALPHA_PK                       |     1 |       |     0   (0)| 00:00:01 |
|  13 |         TABLE ACCESS BY INDEX ROWID | LOCATION                       |     1 |    25 |     0   (0)| 00:00:01 |
|* 14 |          INDEX RANGE SCAN           | ALPHA_SRVDELLOC_IN1            |     1 |       |     0   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID  | CHARLIE                        |     1 |    12 |     1   (0)| 00:00:01 |
|* 16 |         INDEX UNIQUE SCAN           | CHARLIE_PK                     |     1 |       |     0   (0)| 00:00:01 |
|  17 |       TABLE ACCESS BY INDEX ROWID   | BRAVO                          |     1 |    18 |     1   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN            | BRAVO_PK                       |     1 |       |     0   (0)| 00:00:01 |
|* 19 |      INDEX UNIQUE SCAN              | DELTA_PK                       |     1 |       |     0   (0)| 00:00:01 |
|  20 |     TABLE ACCESS BY INDEX ROWID     | DELTA                          |     1 |    24 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter(("WI"."TYPE"='XZ' OR "WI"."TYPE"='Z' OR 
              "WI"."TYPE"='X') AND "WI"."DELINQUENT"='F' AND ("WI"."ACTIVE"='F' OR 
              NVL("WI"."LOCKEDBY",(-999))<>(-999)) AND "WI"."SUSPENDED"='F' AND ("WI"."LOCKEDBY" IS 
              NULL OR "WI"."LOCKEDBY"=12))
  10 - access("WI"."CODE"='MISSING' AND "WI"."TERMINATED"='F')
       filter("WI"."TERMINATED"='F' AND NVL("BRAVOID",(-999))=(-999) AND 
              NVL("CHARLIEID",(-999))=(-999) AND NVL("ALPHAID",(-999))<>(-999))
  12 - access("E"."SYSID"=NVL("ALPHAID",(-999)))
       filter("E"."SYSID"<>(-999))
  14 - access("E"."SYSID"="SD"."ALPHAID"(+))
       filter("SD"."ALPHAID"(+)<>(-999))
  16 - access("E"."CHARLIEID"="MR"."SYSID")
  18 - access("PR"."SYSID"="MR"."BRAVOID")
       filter("PR"."SYSID"="E"."BRAVOID")
  19 - access("PR"."DELTAID"="P"."SYSID")

As you can see the cost is greatly reduced, but why do I not see the newly created indices?

I expected to see them used in the explain plan, but instead I see it using the appropriate primary key index and the "IDX_ITM" index.

Please let me know if you need more information and I'll see if I can provide it.

Improve this question
3
  • 1
    Do you still get the "new" plan if you drop the indexes? Did you re-calculate any statistics?
    – Mat
    Mar 26, 2014 at 6:18
  • It reverts back to the old plan if I drop them and the new plan shows up if I add them back. It has done this consistently across multiple attempts to figure out what is going on. I've used OEM to gather statistics after dropping the indices and after adding them back. I've also checked the execution plan that OEM shows after running the query and it still shows the reduced cost but no sign of my new indices.
    – Rapida
    Mar 26, 2014 at 13:04
  • Did you find the reason? @Rapida
    – Vimal Bhaskar
    Mar 2, 2020 at 12:49

1 Answer 1

Reset to default
1

From the Notes on Function-based Indexes section of the user documentation for CREATE INDEX:

  • When you subsequently query a table that uses a function-based index, Oracle Database will not use the index unless the query filters out nulls. However, Oracle Database will use a function-based index in a query even if the columns specified in the WHERE clause are in a different order than their order in the column_expression that defined the function-based index.

So you could try adding appropriate NOT NULL conditions to your query.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.