indexing - why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN -

indexing - why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN -

i have read documentation indexes, did examples , have doubts.

i create table , insert random values, (a column has unique values) column not null create index on a, b, c. (b-tree)

select count(*) demo_full_index_scan; =1000 select * demo_full_index_scan; b c d e f ---------- ---------- ---------- ---------- ---------- ---------- 1 7 109 1 1 1 2 12 83 2 2 2 3 21 120 3 3 3 4 13 74 4 4 4 5 2 1 5 5 5 ...

documentation says when query values in index, values gathered index (index fast total scan), here optimizer choosing operation.

explain plan select a,b,c demo_full_index_scan = 1; -------------------------------------------------------------------- | id | operation | name | rows | bytes | cost | -------------------------------------------------------------------- | 0 | select statement | | | | | |* 1 | index range scan | fis_01 | | | | --------------------------------------------------------------------

i have specify hint optimizer take index fast total scan (but dont know why have specify it)

explain plan select /*+ index_ffs(demo_full_index_scan fis_01) */a,b,c demo_full_index_scan = 1; -------------------------------------------------------------------- | id | operation | name | rows | bytes | cost | -------------------------------------------------------------------- | 0 | select statement | | 1 | 11 | 2 | |* 1 | index fast total scan| fis_01 | 1 | 11 | 2 | --------------------------------------------------------------------

by other hand ,this examples shows oracle documentation says. when there value in query not in index, value accessed table access index rowid

explain plan select d demo_full_index_scan = 800; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- | id | operation | name | rows | bytes | co -------------------------------------------------------------------------------- | 0 | select statement | | | | | 1 | table access index rowid| demo_full_index_scan | | | |* 2 | index range scan | fis_01 | | | --------------------------------------------------------------------------------

my question ,in first illustration why oracle take index range scan on fast total index scan.

you're performing index range scan because of clause of sql statement:

select a,b,c demo_full_index_scan = 1;

i'm assuming here don't have unique index on despite uniqueness of column, i.e. table ddl this:

create table demo_full_index_scan ( number , b number , c number , d number ); create index i_demo_full_index_scan on demo_full_index_scan (a, b, c);

as don't have unique index oracle can't know certainty values in unique; however, oracle know first column in index , can find value in range of values available in index.

if clause effort filter based on column c perform index total scan c exists in index, don't need access table, not first column in index:

explain plan select a,b,c demo_full_index_scan c = 1; ------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | ------------------------------------------------------------------------------------------- | 0 | select statement | | 1 | 39 | 1 (0)| 00:00:01 | |* 1 | index total scan | i_demo_full_index_scan | 1 | 39 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------

oracle indexing range database-scan