Hi
there.
I have a non-unique
index on a table, and I'm trying to force Oracle to use the index - but it
always does a FTS. Why? (I've tried it with and without the
alias)
SQL> set
autotrace traceonly
SQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year
2 FROM TIREADVISOR.vehicle A
3 ORDER BY veh_year DESC;
SQL> SELECT /*+ INDEX(A,vehicle_veh_year_indx) */ DISTINCT veh_year
2 FROM TIREADVISOR.vehicle A
3 ORDER BY veh_year DESC;
20 rows
selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=118 Card=20 Bytes=80)
1
0 SORT (ORDER BY) (Cost=118 Card=20 Bytes=80)
2 1 SORT (UNIQUE) (Cost=67 Card=20 Bytes=80)
3 2 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 Card=19607 Bytes=78428)
2 1 SORT (UNIQUE) (Cost=67 Card=20 Bytes=80)
3 2 TABLE ACCESS (FULL) OF 'VEHICLE' (Cost=16 Card=19607 Bytes=78428)
===
select TABLE_NAME, INDEX_NAME, COLUMN_NAME,
COLUMN_POSITION
from dba_ind_columns
where INDEX_OWNER = 'TIREADVISOR'
and TABLE_NAME = ('VEHICLE') order by 1,2,4,3
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COLUMN_NAME COLUMN_POSITION
-------------------- ---------------
VEHICLE VEHICLE_PK
VEH_ID 1
from dba_ind_columns
where INDEX_OWNER = 'TIREADVISOR'
and TABLE_NAME = ('VEHICLE') order by 1,2,4,3
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COLUMN_NAME COLUMN_POSITION
-------------------- ---------------
VEHICLE VEHICLE_PK
VEH_ID 1
VEHICLE
VEHICLE_VEH_YEAR_INDX
VEH_YEAR 1
VEH_YEAR 1
Thanks for any
help!
-
Jerry