CAN A FUNCTION BASED INDEX GET COVERED ?
The answer is yes. Function based index can be created as a covering index in combination with normal columns and functional columns.
kIsH@xHydra<>CREATE INDEX cov_xd_xc_xs ON xtbl(trunc(xdate),country,salary);
Index created.
kIsH@xHydra<>set autot traceonly explain
kIsH@xHydra<>SELECT name,country FROM xtbl WHERE trunc(xdate) < sysdate and country <> 'NULL' and salary < 10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4249760034
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 455 | 14560 | 1
23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| XTBL | 455 | 14560 | 1
23 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | COV_XD_XC_XS | 82 | |
41 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(TRUNC(INTERNAL_FUNCTION("XDATE"))<SYSDATE@! AND "SALARY"<10000)
filter("SALARY"<10000 AND "COUNTRY"<>'NULL')