Hi Experts/Gurus,
I have been reading Oracle docs http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i52044, to know the difference among index range scans, index full scan and index fast full scan. Below is the description for "Fast Full Index Scans"
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
So the document says, for the optimizer to choose index FFS, " at least one column in the index key has the NOT NULL constraint", but what I see from below test case is in complete contrast to what I read.
I created a table with no "NOT NULL" constraints.
SQL> create table r_dummy(a number,b varchar2(10));
Table created.
SQL> insert into r_dummy select level,'hi' from dual connect by level<=20;
20 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('HLODS','R_DUMMY');
PL/SQL procedure successfully completed.
SQL> select * from r_dummy where a <= 10;
A B
---------- ----------
1 hi
2 hi
3 hi
4 hi
5 hi
6 hi
7 hi
8 hi
9 hi
10 hi
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2533004807
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 60 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| R_DUMMY | 10 | 60 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
329 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
As expected its doing "Full Table Scan", now if I'm going to have an index on columns (a,b), If my understanding is correct, as per docs the optimizer shouldn't do a "Index fast full scan" but interestingly it obeys to the index_ffs hint and does a index fast full scan
SQL> create index r_dummy_idx on r_dummy(a,b);
Index created.
SQL> exec dbms_stats.gather_index_stats('HLODS','R_DUMMY_IDX');
PL/SQL procedure successfully completed.
SQL> select /*+index_ffs(r)*/ * from r_dummy r where a <= 10;
A B
---------- ----------
1 hi
2 hi
3 hi
4 hi
5 hi
6 hi
7 hi
8 hi
9 hi
10 hi
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3219236618
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 60 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| R_DUMMY_IDX | 10 | 60 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"<=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
330 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
This clearly shows optimizer chooses "INDEX FAST FULL SCAN" even though neither of the indexed columns have "NOT NULL" constraint.
And there is other statement that speaks about ORDERING results.
+"It cannot be used to eliminate a sort operation, because the data is not ordered by the index key"+
it has eliminated sort operation, I see my results to be in ascending order.
SQL> drop table r_dummy;
Table dropped.
SQL> create table r_Dummy(a number);
Table created.
SQL> insert into r_dummy values(10);
1 row created.
SQL> insert into r_dummy values(3);
1 row created.
SQL> insert into r_dummy values(1);
1 row created.
SQL> insert into r_dummy values(9);
1 row created.
SQL> insert into r_dummy values(5);
1 row created.
SQL> insert into r_dummy values(4);
1 row created.
SQL> insert into r_dummy values(2);
1 row created.
SQL> insert into r_dummy values(6);
1 row created.
SQL> insert into r_dummy values(7);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from r_dummy;
A
----------
10
3
1
9
5
4
2
6
7
9 rows selected.
SQL> create index r_dummy_idx on r_dummy(a);
Index created.
SQL> set autotrace on;
SQL> exec dbms_stats.gather_table_stats('HLODS','R_DUMMY');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_index_stats('HLODS','R_DUMMY_IDX');
PL/SQL procedure successfully completed.
Below you can see the results in ascending order.
SQL> select /*+index_ffs(r)*/ * from r_dummy r where a<=10;
A
----------
1
2
3
4
5
6
7
9
10
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3219236618
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 27 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| R_DUMMY_IDX | 9 | 27 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"<=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
291 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL>
I don't really understand whether docs are wrong or I'm reading them wrong. Please help/correct me if my understanding is poor.