I have been asked couple of times by my customers, "Why my index is not being used even though there is an index on a column?"
90% of the time I get this answer - If query is returning more than 15% of rows from table then database choose full table scan vs index scan( index Selectivity). My take on this is: It's one part of the equation and there are other factors that optimizer uses to choose index scan vs table scan. All of the following should be considered carefully and never try these on production before thorough analysis -
- Clustering Factor.
- Selectivity.
- OPTIMIZER_INDEX_COST_ADJ Initialization Parameter.
- Parallel Option.
Clustering Factor - It defines how rows in index block align with the data block.
If Clustering factor=number of blocks in the table then the rows in an index leaf block most likely point to rows in a same data block. In this case, cost of index scan would be much less then cost of table scan that's why optimizer tend to use index scan.
If clustering factor = number of rows in table then rows are randomly assigned in an index leaf block and points to rows in different data block. Optimizer will have to read more blocks and the cost of index scan would be much higher compared to table scan that's why optimizer tend to use full table scan.
You can achieve low clustering factor by re-creating a table with "order by" clause, but this can create high clustering factor on other columns' indexes, extreme precaution should be taken before using this approach( re-creating table with order by ).
Clustering Factor Example:
Example 1 :
We will create two tables with different order by statement.
Table : Test_case1 <--- Order by rownum.
SQL> create table test_case1
2 as
3 select rownum "ROWNUMBER",a.* from dba_objects a where rownum<1001
4 order by rownum;
Table created.
Table : Test_case2 <--- Order by object_name
SQL>
SQL> create table test_case2
2 as
3 select rownum "ROWNUMBER",a.* from dba_objects a where rownum<1001
4 order by object_name;
Table created.
Create index on these two tables and gather statistics.
SQL>
SQL> create index idx_test_case1
2 on test_case1(rownumber);
Index created.
SQL>
SQL> create index idx_test_case2
2 on test_case2(rownumber);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'test_case1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'test_case2');
PL/SQL procedure successfully completed.
SQL> select a.table_name,a.num_rows,a.blocks,b.index_name,b.clustering_factor
2 from user_tables a,user_indexes b
3 where a.table_name=b.table_name
4 and a.table_name like 'TEST_CASE%';
TABLE_NAME NUM_ROWS BLOCKS INDEX_NAME CLUSTERING_FACTOR
---------------------- ------------------ ------------- --------------------------- -------------------------
TEST_CASE1 1000 14 IDX_TEST_CASE1 14
TEST_CASE2 1000 14 IDX_TEST_CASE2 719
In IDX_TEST_CASE1, Clustering factor(14) equal to No of Blocks(14)
In IDX_TEST_CASE2, Clustering Factor(719) approx. equals to No of Rows(1000)
SQL> set autotrace trace explain;
SQL> Select * from test_case1 where rownumber<10;
Execution Plan
----------------------------------------------------------
Plan hash value: 4004684460
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 801 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_CASE1 | 9 | 801 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_CASE1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ROWNUMBER"<10)
The above doing index scan as expected.
SQL> Select * from test_case2 where rownumber<10;
Execution Plan
----------------------------------------------------------
Plan hash value: 1160411121
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 801 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_CASE2 | 9 | 801 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWNUMBER"<10)
Example 2:
Create a similar table with different PCTFREE and results are totally different than what we expected. The idea behind this example is to see what happens if a small table scattered across multiple blocks.
SQL>Create Table test( id number, name varchar(10)) pctfree 99 pctused 1
SQL>Insert into test
Select round(DBMS_RANDOM.VALUE(1,15)), upper(DBMS_RANDOM.STRING('A',10)) NAME from user_objects
SQL>exec dbms_stats.gather_table_stats
(user,'TEST')
SQL>Select a.table_name, B.NUM_ROWS, B.BLOCKS,A.CLUSTERING_FACTOR from user_indexes a, user_tables b where a.table_name=b.table_name and a.table_name = 'TEST' ;
TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
----------------- --------------- ---------- --------------------------
TEST 543 275 526
The number of blocks, 275, are much higher and clustering factor( 526) is near to number of rows(543) in table and optimizer should do full table scan instead of index scan.
SQL> select * from test where id=1;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 504 | 37 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 36 | 504 | 37 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 36 | | 1 (0)|
--------------------------------------------------------------------------------
SQL> select * from test where id=2;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 504 | 37 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 36 | 504 | 37 (0)|
| 2 | INDEX RANGE SCAN | IDX_TEST_ID | 36 | | 1 (0)|
--------------------------------------------------------------------------------
Not good. Optimizer doing index scan instead of full table scan, optimizer thinks the cost of index scan is less than full tables scan and it chooses to do index scan.
Create the same table with low PCTFREE parameter.
SQL>Create table test( id number, name varchar(10)) PCTFREE 10 PCTUSED 90;
SQL> Insert into test
Select round(DBMS_RANDOM.VALUE(1,15)), upper(DBMS_RANDOM.STRING('A',10)) NAME from user_objects;
SQL>Create index idx_test on test(id)
SQL>Exec dbms_stats.gather_table_stats(user,'TEST')
SQL>Select a.table_name, B.NUM_ROWS, B.BLOCKS,A.CLUSTERING_FACTOR from user_indexes a, user_tables b where a.table_name=b.table_name and a.table_name = 'TEST' ;
SQL>Select a.table_name, B.NUM_ROWS, B.BLOCKS,A.CLUSTERING_FACTOR from user_indexes a, user_tables b where a.table_name=b.table_name and a.table_name = 'TEST' ;
TABLE_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
----------------- --------------- ---------- --------------------------
TEST 542 2 30
2 blocks compared to 275, where PCTFREE was 99, clustering factor(30) is near to number of blocks(2) and optimizer should do index scan.
SQL> select * from test where id=1;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 504 | 2 (0)|
| 1 | TABLE ACCESS FULL| TEST | 36 | 504 | 2 (0)|
---------------------------------------------------------------
SQL> select * from test where id=2;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 504 | 2 (0)|
| 1 | TABLE ACCESS FULL| TEST | 36 | 504 | 2 (0)|
---------------------------------------------------------------
Nope. Optimizer thinks cost of doing full table scan is lower than index scan.
The idea behind this example is that a careful consideration should be taken if you think of changing PCTFREE or PCTUSED parameters as it can change your execution plans completely.
Please look into my next blog http://vkoracle.blogspot.com/2012/08/query-not-using-index-2-of-4.html for Selectivity
