Many of us believe that optimizer will prefer FTS to index access on a table whose size is smaller than DB_FILE_MULIBLOCK_READ_COUNT as whole table can be accessed with just a single I/O as compared to at least two I/O’s ( 1 index block and 1 data block) in case of index access.
Well, that is not always true!
Let’s demonstrate …
SQL> sho parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> sho parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8
SQL> sho parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
- Create a table whose size = db_file_multiblock_read_count
i.e. = 8 blocks and has a unique index on id column
SQL> drop table mbrc_7b purge; create table mbrc_7b (id number primary key, text char(700)); begin for i in 1..60 loop insert into mbrc_7b values (i, 'txt'||i); commit; end loop; end; / exec dbms_stats.gather_table_stats(USER, 'MBRC_7B', cascade=> true);
– Check that table’s HWM is set to 7 blocks
select table_name, blocks from user_tables where table_name= 'MBRC_7B';
TABLE_NAME BLOCKS
—————————— ———-
MBRC_60K 7
– check that table has been assigned 8 blocks
SQL> select segment_name, min_extents, blocks, extents from dba_segments where segment_name like '%MBRC%';
SEGMENT_NA MIN_EXTENTS BLOCKS EXTENTS
———- ———– ———- ———-
MBRC_7B 1 8 1
- Note that the following query uses index access although table size =db_file_multiblock_read_count and can be read in one multi block I/O
SQL> set autotrace traceonly
select * from mbrc_7b where id =1;
set autotrace off
Execution Plan
———————————————————-
Plan hash value: 4289258908
Execution Plan
———————————————————-
Plan hash value: 4289258908
——————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 704 | 1 (0)| 00:00:09 |
| 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 1 | 704 | 1 (0)| 00:00:09 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0014075 | 1 | | 0 (0)| 00:00:01 |
Predicate Information (identified by operation id):
—————————————————
2 – access(“ID”=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
1089 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- Let’s verify that the if FTS is used, cost of the query is more (4) as compared to index access (1).
SQL>set autotrace traceonly
select /*+ full (s) */ * from mbrc_7b s where id =1;
set autotrace traceonly
Execution Plan
———————————————————-
Plan hash value: 1640139338
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 704 | 4 (0)| 00:00:35 |
|* 1 | TABLE ACCESS FULL| MBRC_7B | 1 | 704 | 4 (0)| 00:00:35 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(“ID”=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1179 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2
– we can see that cost of FTS is more than index access
The reason as explained by Oracle Guru Richard Foote is :
Oracle needs to read the table segment header in order to determine what blocks need to be accessed to begin the FTS operation.
Oracle begins a FTS function call by accessing the segment header of the segment as it contains vital info required for the FTS operation.
However an index scan (but not a Fast Full Index Scan) begins by directly accessing the index root block (or single leaf block if it’s a level 0 index). there is nothing within the index segment header that’s required for an index scan, so the segment header is an “overhead” we can save and potentially taken advantage of.
Oracle must visit the table segment header during a FTS because it contains vital information necessary to perform the FTS, namely the extent map and the High Water Mark (HWM) associated with the table.
During an index scan operation, there’s nothing of interest within the index segment header. The critical index block, the index block by which all index scans must start is the root block of the index (except Fast Full Index Scans which are basically the FTS equivalent for indexes). There’s no need to access the index segment header because it’s the root block that actually contains all the necessary information by which to start the index scan operation. The root blocks contains the pointers to subsequent index blocks (be it a branch or leaf blocks) that Oracle needs to follow in order to find the index entry of interest.
with a Unique index, there can only be a maximum of 1 row returned. Therefore, when selecting this one row, Oracle doesn’t have to perform the second fetch operation to confirm there are indeed no more rows to return
– Let’s see what happens if we access all the rows with a predicate id between <min_val> and <max_val>
— Note that
- Optimizer chooses FTS !!!
- Cost of the query is 4 units i.e same as earlier with predicate where id=1
- Consistent gets = 13
SQL> set autotrace traceonly select * from mbrc_7b where id between 1 and 60; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1640139338 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 60 | 42240 | 4 (0)| 00:00:35 | |* 1 | TABLE ACCESS FULL| MBRC_7B | 60 | 42240 | 4 (0)| 00:00:35 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID">=1 AND "ID"<=60) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 43490 bytes sent via SQL*Net to client 449 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 60 rows processed
- Let’s verify that the cost of the query is more if index is used
– Note that index range scan has CPU cost of 1 as compared of 0 when index unique scan was used
– Cost of the query is 7 units as compared to 4 for FTS
– No. of consistent gets are 15 as compared to 13 in FTS
SQL>set autotrace traceonly select /*+ index (s) */ * from mbrc_7b s where id between 1 and 60; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1386006575 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 60 | 42240 | 7 (0)| 00:01:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 60 | 42240 | 7 (0)| 00:01:01 | |* 2 | INDEX RANGE SCAN | SYS_C0014075 | 60 | | 1 (0)| 00:00:09 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=60) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 43490 bytes sent via SQL*Net to client 449 bytes received via SQL*Net from client 5 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 60 rows processed
*****************************************
Hence, it can be seen that whereas cost of FTS remains at 4 irrespective of the predicate, cost of index access increases from 1 to 7 as no. of data blocks accessed increase.
Logically speaking, there should be a threshold no. of data blocks accessed above which the execution plan changes from Index access to FTS. After trying various ranges, I found out that plan switches from index access to FTS as id range changes from 1 – 29 to 1 – 30.
(Please note that above values might vary from system to system due to the difference in system statistics. )
Let’s verify…
— Note that index access is used for id range 1 – 29
SQL> set autotrace traceonly select * from mbrc_7b where id between 1 and 29; set autotrace off 29 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1386006575 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 29 | 20416 | 4 (0)| 00:00:3 | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 29 | 20416 | 4 (0)| 00:00:3 |* 2 | INDEX RANGE SCAN | SYS_C0014075 | 29 | | 1 (0)| 00:00:0 ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=29) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 21251 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 29 rows processed
– Note that plan changes to FTS as upper limit of the range changes to 30
SQL> set autotrace traceonly select * from mbrc_7b where id between 1 and 30; set autotrace off 30 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1640139338 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 21120 | 4 (0)| 00:00:35 | |* 1 | TABLE ACCESS FULL| MBRC_7B | 30 | 21120 | 4 (0)| 00:00:35 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<=30 AND "ID">=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 21960 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30 rows processed
– Note that for id between 1 and 30 indexed access costs 5 units whereas FTS cost is 4.
–
SQL> set autotrace traceonly select /*+ index(s) */ * from mbrc_7b s where id between 1 and 30; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1386006575 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 21120 | 5 (0)| 00:00:44 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_7B | 30 | 21120 | 5 (0)| 00:00:44 | |* 2 | INDEX RANGE SCAN | SYS_C0014075 | 30 | | 1 (0)| 00:00:09 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=30) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 21960 bytes sent via SQL*Net to client 427 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30 rows processed
— Let’s have a table with non unique index –
SQL> drop table mbrc_nu purge; create table mbrc_nu (id number , text char(700)); begin for i in 1..60 loop for j in 1..10 loop insert into mbrc_nu values (i, 'txt'||j); commit; end loop; commit; end loop; end; /
– Check that the table has 10 records for each value of id
select id, count(*) from mbrc_nu group by id order by id; ID COUNT(*) --------- ---------- 1 10 2 10 3 10 4 10 ... 59 10 60 10
– create non unique index on the table and gather statistics
create index mbrc_nu_idx on mbrc_nu(id); exec dbms_stats.gather_table_stats(USER, 'MBRC_NU', cascade=> true); select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_NU'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- MBRC_NU 600 60 SQL> select segment_name, min_extents, blocks, extents from dba_segments where segment_name like '%MBRC_NU%'; SEGMENT_NAME MIN_EXTENTS BLOCKS EXTENTS --------------- ----------- ---------- ---------- MBRC_NU 1 64 8 MBRC_NU_IDX 1 8 1
– Note that the index has a low clustering factor of 60 close to no. of blocks (60) ) i.e rows for a key value are placed in the same block
SQL>col index_name for a15
Col table_name for a15
select index_name, table_name,blevel, leaf_blocks, clustering_factor
from user_indexes
where index_name = 'MBRC_NU_IDX';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
--------------- --------------- ---------- ----------- -----------------
MBRC_NU_IDX MBRC_NU 1 2 60
– check that all 10 rows with id = 1 are in the same block
sql>select count(*) , id, dbms_rowid.rowid_block_number(rowid) from mbrc_nu where id = 1 group by id, dbms_rowid.rowid_block_number(rowid); COUNT(*) ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) --------- ---------- ------------------------------------ 10 1 100025
– Note that for predicate id = 1, index is used as only one table block needs to be accessed
SQL> set autotrace traceonly select * from mbrc_nu where id =1; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1257710425 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 7040 | 3 (0)| 00:00:26 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 10 | 7040 | 3 (0)| 00:00:26 | |* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 10 | | 1 (0)| 00:00:09 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 7654 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
* Let’s check cost of plan if FTS is used
* Note that cost of the plan is much higher (24) as compared to index scan(3)
SQL>set autotrace traceonly select /*+ full(s) */ * from mbrc_nu where id =1; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 112258944 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 7040 | 24 (0)| 00:03:27 | |* 1 | TABLE ACCESS FULL| MBRC_NU | 10 | 7040 | 24 (0)| 00:03:27 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 64 consistent gets 0 physical reads 0 redo size 7602 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
Just like in case of unique indexed table, here also execution plan will change from Index access to FTS when more than a threshold range of id’s are queried
Here the threshold is id = 1 to 21 i.e. whenever more than 21 table blocks need to be queried, FTS will be used.
Let’s verify
* Note that Index access is used for range 1 – 21
* Note that cost of index access is 24 which is same as FTS as seen earlier
SQL> set autotrace traceonly select * from mbrc_nu where id between 1 and 21; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1257710425 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 213 | 146K| 24 (0)| 00:03:27 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 213 | 146K| 24 (0)| 00:03:27 | |* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 213 | | 2 (0)| 00:00:18 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=21) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 77 consistent gets 0 physical reads 0 redo size 151313 bytes sent via SQL*Net to client 558 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 210 rows processed
* Let’s check for range 1 – 22
* Note that plan switches to FTS as from this range onwards
Cost of index access is more than FTS
SQL> set autotrace traceonly select * from mbrc_nu where id between 1 and 22; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 112258944 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 224 | 154K| 24 (0)| 00:03:27 | |* 1 | TABLE ACCESS FULL| MBRC_NU | 224 | 154K| 24 (0)| 00:03:27 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<=22 AND "ID">=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 78 consistent gets 0 physical reads 0 redo size 158543 bytes sent via SQL*Net to client 569 bytes received via SQL*Net from client 16 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 220 rows processed
* Let’s verify that from this range onwards Cost of index access is more than FTS
* Note than cost of index access for id = 1 – 22 is 25 which is more than that of FTS (24)
SQL> set autotrace traceonly select /*+ index (s) */ * from mbrc_nu s where id between 1 and 22; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1257710425 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 224 | 154K| 25 (0)| 00:03:36 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_NU | 224 | 154K| 25 (0)| 00:03:36 | |* 2 | INDEX RANGE SCAN | MBRC_NU_IDX | 224 | | 2 (0)| 00:00:18 | ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=22) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 54 consistent gets 0 physical reads 0 redo size 158543 bytes sent via SQL*Net to client 569 bytes received via SQL*Net from client 16 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 220 rows processed
Hence, it can be concluded that as no. of blocks accessed increase, cost of FTS remains same but cost of index access increases. The switching of plan from Index access to FTS takes place after a thershold cost of index access becomes more than FTS.
In case of non unique index, we need to consider another factor i.e clustering factor of index. In the scenario above, clustering factor of the index was low (approached no. of blocks) and all the rows belonging to same id were placed in the same block and hence only one table block needed to be accessed for each value of id. But if clustering factor of the index is high (approaches no. of rows), rows of an id are scattered across a large no. of blocks and cost of index access increases. In that case switching from index access to FTS might occur at a smaller threshold.
Let’s verify
- create another table mbrc_hi_cf from mbrc_nu with rows ordered randomly
sql>drop table mbrc_hi_cf purge; create table mbrc_hi_cf as select * from (select * from mbrc_nu order by dbms_random.random) order by dbms_random.random; create index mbrc_hi_cf_idx on mbrc_hi_cf(id); exec dbms_stats.gather_table_stats(USER, 'MBRC_HI_CF', cascade=> true); select table_name, num_rows, blocks from user_tables where table_name= 'MBRC_HI_CF'; TABLE_NAME NUM_ROWS BLOCKS ------------------------------ ---------- ---------- MBRC_NU 600 60 SQL> SQL> col segment_name for a20 select segment_name, min_extents, blocks, extents from dba_segments where segment_name like '%MBRC_HI_CF%'; SEGMENT_NAME MIN_EXTENTS BLOCKS EXTENTS -------------------- ----------- ---------- ---------- MBRC_HI_CF 1 64 8 MBRC_HI_CF_IDX 1 8 1
– Note that the index has a high clustering factor of 565 close to rows (600) )i.e rows for a key value are scattered acorss different blocks
SQL>col index_name for a15
Col table_name for a15
select index_name, table_name,blevel, leaf_blocks, clustering_factor
from user_indexes
where index_name = 'MBRC_HI_CF_IDX';
INDEX_NAME TABLE_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
--------------- --------------- ---------- ----------- -----------------
MBRC_HI_CF_IDX MBRC_HI_CF 1 2 565
— check that 10 rows with id = 1 are scattered across 10 blocks
sql>select count(*) , id, dbms_rowid.rowid_block_number(rowid) from mbrc_hi_cf where id = 1 group by id, dbms_rowid.rowid_block_number(rowid); COUNT(*) ID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ---------- ---------- ------------------------------------ 1 1 109200 1 1 109227 1 1 109231 1 1 109186 1 1 109203 1 1 109210 1 1 109188 1 1 100092 1 1 109196 1 1 109234 10 rows selected.
– Let’s check the plan used by optimizer for id = 1
— Note that it still uses index access but cost has increased from 3 (in mbrc_nu) to 11 as 10 data blocks are accessed to get all the rows
SQL> set autotrace traceonly select * from mbrc_hi_cf where id =1; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 3955578425 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 7040 | 11 (0)| 00:01:35 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 10 | 7040 | 11 (0)| 00:01:35 | |* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 10 | | 1 (0)| 00:00:09 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=1) Statistics ---------------------------------------------------------- 24 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 7654 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
* Let’s check cost of plan if FTS is used
* Note that cost of the plan is same (24) as earlier (mbrc_nu) and is more than
that of index access (11)
SQL>set autotrace traceonly select /*+ full(s) */ * from mbrc_hi_cf s where id =1; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1581728470 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 7040 | 24 (0)| 00:03:27 | |* 1 | TABLE ACCESS FULL| MBRC_HI_CF | 10 | 7040 | 24 (0)| 00:03:27 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=1) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 64 consistent gets 0 physical reads 0 redo size 7602 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
Just like in case of unique indexed table, here also execution plan will change from Index access to FTS when more than a threshold range of id’s are queried.
Here the threshold is reached for a much smaller range of id’s i.e for id = 1 to 2 , index access is used but plan switches to FTS for id = 1 to 3 and above as rows for same id are scattered across multiple blocks.
– Let’s verify..
* Note that Index access is used for range 1 – 2
* Note that cost of index access is 21 which is less than FTS (24) as seen earlier
SQL> set autotrace traceonly select * from mbrc_hi_cf where id between 1 and 2; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 3955578425 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 20 | 14080 | 21 (0)| 00:03:01 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 20 | 14080 | 21 (0)| 00:03:01 | |* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 20 | | 2 (0)| 00:00:18 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 65 consistent gets 0 physical reads 0 redo size 14142 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed
–
— Let’s verify that plan switches from index access to FTS for the range id = 1 – 3
SQL> set autotrace traceonly select * from mbrc_hi_cf where id between 1 and 3; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 1581728470 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 21120 | 24 (0)| 00:03:27 | |* 1 | TABLE ACCESS FULL| MBRC_HI_CF | 30 | 21120 | 24 (0)| 00:03:27 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"<=3 AND "ID">=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 65 consistent gets 0 physical reads 0 redo size 19859 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30 rows processed SQL> set autotrace off
– Let’s verify that from this range onwards Cost of index access is more than FTS as records of each id are scattered across a large no.. of blocks
— Note than cost of index access is 31 which is more than that of FTS (24)
SQL> set autotrace traceonly select /*+ index (s) */ * from mbrc_hi_cf s where id between 1 and 3; set autotrace off Execution Plan ---------------------------------------------------------- Plan hash value: 3955578425 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 21120 | 31 (0)| 00:04:27 | | 1 | TABLE ACCESS BY INDEX ROWID| MBRC_HI_CF | 30 | 21120 | 31 (0)| 00:04:27 | |* 2 | INDEX RANGE SCAN | MBRC_HI_CF_IDX | 30 | | 2 (0)| 00:00:18 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID">=1 AND "ID"<=3) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 32 consistent gets 0 physical reads 0 redo size 21230 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30 rows processed
Hence it can be concluded that in case we have multiple rows with same id’s , FTS may be used for a smaller range of id’s if clustering factor is high.
Summary:
– For tables sized smaller than db_file-multiblock_read_count, index access may be preferred to FTS as no. of blocks accessed in FTS are more than those accessed in index access.
– As no. of table blocks accessed increase, cost of FTS remains same but cost of index access increases. The execution plan changes from index access to FTS after a threshold no. of table blocks accessed.
– For tables with non unique index, the clustering factor also plays a role in determining the threshold id range for plan switching. The switching takes place at a smaller range if clustering factor is high and vice versa.
I hope this post was useful.
Thanks for your time!
References:
https://forums.oracle.com/forums/thread.jspa?messageID=11051591#11051591
http://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/
——————————————————————————————–
Related links:
Home
Tuning Index
Clustering Factor Demystified : Part-I
Clustering Factor Demystified : Part-II
Clustering Factor Demystified : Part-III
DB_FILE_MULTIBLOCK_READ_COUNT And Extent Size
Multiblock Reads and Cached Blocks