NUM_DISTINCT与INDEX SKIP SCAN

一个列的NUM_DISTINCT的个数与执行计划是否走INDEX SKIP SCAN的关系非常密切。
如果一个列的NUM_DISTINCT个数太多，并且建立了以这个列为前缀的组合索引，
查询的时候并没有带这个前缀列，那么ORACLE很可能就不会选择INDEX SKIP SCAN。

 

看如下一个简单的例子：

 

SQL> CREATE TABLE TEST AS SELECT * FROM ALL_OBJECTS;

 

表已创建。

 

SQL> CREATE INDEX IDX_TEST_01 ON TEST(OBJECT_TYPE,OBJECT_NAME);

 

索引已创建。

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',cascade=>TRUE);

 

PL/SQL 过程已成功完成。

 

首先创建了一个表和一个索引，并且收集了统计信息。

SQL> SELECT COLUMN_NAME,NUM_DISTINCT
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME='TEST' AND COLUMN_NAME='OBJECT_TYPE';

 

COLUMN_NAME          NUM_DISTINCT
-------------------- ------------
OBJECT_TYPE                    19

 

SQL> SET AUTOT TRACEONLY EXP
SQL> SET LINESIZE 300
SQL> SELECT * FROM TEST WHERE OBJECT_NAME='T';

 

Execution Plan
----------------------------------------------------------
Plan hash value: 3506850203

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   188 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     2 |   188 |    22   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST_01 |     2 |       |    20   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='T')
       filter("OBJECT_NAME"='T')

 

由于OBJECT_TYPE的NUM_DISTINCT的个数不多，此时ORACLE可以选择INDEX SKIP SCAN。

 

SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS(USER,'TEST','OBJECT_TYPE');

 

PL/SQL 过程已成功完成。

 

SQL> SET AUTOT OFF
SQL> SELECT COLUMN_NAME,NUM_DISTINCT
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME='TEST' AND COLUMN_NAME='OBJECT_TYPE';

 

未选定行

SQL> SET AUTOT TRACEONLY EXP
SQL> SET LINESIZE 300
SQL> SELECT * FROM TEST WHERE OBJECT_NAME='T';

 

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   188 |   148   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     2 |   188 |   148   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='T')
  
由于OBJECT_TYPE的列的统计信息被删掉,ORACLE无法判断OBJECT_TYPE的NUM_DISTINCT的格式，
此时ORACLE不会用到INDEX SKIP SCAN。

 

SQL> EXEC DBMS_STATS.SET_COLUMN_STATS(USER,'TEST','OBJECT_TYPE',DISTCNT=>19);

 

PL/SQL 过程已成功完成。

 

SQL> SELECT COLUMN_NAME,NUM_DISTINCT
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME='TEST' AND COLUMN_NAME='OBJECT_TYPE';

 

COLUMN_NAME          NUM_DISTINCT
-------------------- ------------
OBJECT_TYPE                    19

 

SQL> SET AUTOT TRACEONLY EXP
SQL> SET LINESIZE 300
SQL> SELECT * FROM TEST WHERE OBJECT_NAME='T';

 

Execution Plan
----------------------------------------------------------
Plan hash value: 3506850203

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   188 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     2 |   188 |    22   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST_01 |     2 |       |    20   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"='T')
       filter("OBJECT_NAME"='T')
      
      
通过手工修改列的NUM_DISTINCT的值，使得ORACLE可以再次利用到INDEX SKIP SCAN的执行计划。