一个列的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的执行计划。
阅读(2542) | 评论(0) | 转发(0) |
