Hi
My oracle version is 11.2.0.2.0.
The below query is not using index though one composite index is present.
explain plan for SELECT code, type FROM MAT WHERE MAT.id = :1 ORDER BY code;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1862655975
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2546 | 33098 | 192 (2)| 00:00:03 |
| 1 | SORT ORDER BY | | 2546 | 33098 | 192 (2)| 00:00:03 |
|* 2 | TABLE ACCESS FULL| MA_TABLE | 2546 | 33098 | 191 (2)| 00:00:03 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------
2 - filter("MAT"."ID"=:1)
select index_name,column_name,COLUMN_POSITION from dba_ind_columns where table_name='MA_TABLE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ --------------- --------------------
IDX_MA_TABLE CODE 1
IDX_MA_TABLE ID 2
I created an index as follows.
create index TEST on MAT("ID","CODE");
Query is using this index but not IDX_MA_TABLE
Why is this query using this index?I tried giving
optimizerskip_scan_enabled=TRUE in init.ora .Still it is not using indexes.Kindly help me why is it so.
Thanks