By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table indexTest(
col1 NUMBER,
col2 NUMBER ,
COL3 NUMBER );
create index indexTest_INX on indexTest(col2,col3,0);
INSERT INTO indexTest VALUES(1,2,3)
1 rows affected
--select /*+ gather_plan_statistics */ from dual;
select col2,col3 from indextest;
| COL2 | COL3 |
|---|---|
| 2 | 3 |
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
| PLAN_TABLE_OUTPUT |
|---|
| SQL_ID 9sswavjmgz6gh, child number 0 |
| call dbms_output.enable() |
| NOTE: cannot fetch plan for SQL_ID: 9sswavjmgz6gh, CHILD_NUMBER: 0 |
| Please verify value of SQL_ID and CHILD_NUMBER; |
| It could also be that the plan is no longer in cursor cache (check v$sql_plan) |