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.
Best Answer
From the Notes on Function-based Indexes section of the user documentation for CREATE INDEX:
So you could try adding appropriate
NOT NULLconditions to your query.