Index is not getting used

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8 and see how the optimizer executes.

We see this in the below example.
create table test (x number, y number);
insert into test values ( 4,5);
insert into test values (1,3);
insert into test values (7,5);
insert into test values (3,6);

SQL> select * from test;

X Y
----- ----
4 5 1 3
7 5
3 6

SQL> create index test_idx on test(x,y);

Index created.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','TEST');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from test where y=5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1 Bytes=6)
The above query is going for the FULL table access since we have an index but the Y is not a leading edge of the index.


SQL> select * from test where x=3;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1 Bytes=6)

Now we try with both the conditions. The below example is having both X and Y in the where clause.

SQL> select * from test where x=3 and y=5;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1 Bytes=6)

Still it is not going for the index scan. The reason is that, the table is having the small number of records (4 records). The optimizer checks the best plan and goes for the FULL table access. This is proving us the FULL table access is not always bad.

“FULL table access is not always bad and indexes are not always good.”

We increase the records in the table.
SQL> set autotrace off;
SQL> insert into test select * from test;
8 rows created.

SQL> /
16 rows created.
....
...
..
.
SQL> /
16384 rows created.

SQL> commit;
Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','TEST');
3end;
/
PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from test where x=3;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=8192 Bytes=49152)

1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4 Card=8192 Bytes=49152)
The optimizer will allow to use the index since our predicate did not involve the column X. the optimizer would notice that it did not have to go to the table to get either X or Y. they are in the index. So it may very well option for the fast full index scan. The index is smaller typically smaller than the underlying table. (This access path is always available with CBO only).
SQL> select * from test where y=6;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=10923 Bytes=65538)

1 0 INDEX (FAST FULL SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=4 Card=10923 Bytes=65538)

Now we try with both the conditions. The below example is having both X and Y in the where clause.
SQL> select * from test where x=3 and y=6;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2731 Bytes=16386)
1 0 INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=3 Card=2731 Bytes=16386)
The explain plans shows that, it is going for the index scan.