最近遇到一个客户发现一个sql语句的执行计划走了index range scan，他期望的结果是Index Unique Scans，因为对应的字段上是有主键的。
经过排查我们发现
INDEX_NAME IX_XXXXXXXXXXXXX
INDEX_TYPE NORMAL
TABLE_OWNER CXXX
TABLE_NAME CXXX_XXXXXXXX
TABLE_TYPE TABLE
UNIQUENESS NONUNIQUE <<<<<<<<<<<<<<<< NONUNIQUE
主键可以使用NONUNIQUE INDEX吗？ NONUNIQUE INDEX的存在是引起执行计划改变的原因吗？
带着疑问我做了下面的测试：
drop table t_table;
–创建表
CREATE TABLE t_table(numcol INT);
–创建非唯一性索引
CREATE INDEX t_table_idx ON t_table(numcol);
–追加主键约束
ALTER TABLE t_table ADD CONSTRAINT t_table_pk PRIMARY KEY(numcol);
SELECT /*+gather_plan_statistics*/ * FROM t_table WHERE numcol = 1;
select sql_id, child_number from v$sql where sql_text like q’!SELECT /*+gather_plan_statistics*/ * FROM t_table WHERE numcol = 1!’ and sql_text not like ‘%v$sql%’;
SQL_ID CHILD_NUMBER
————- ————
698t1z5ruk076 0
–查看执行计划，确实重现了客户的问题
select * from table(dbms_xplan.display_cursor(sql_id=>’698t1z5ruk076′, cursor_child_no=> 0 , format=>’TYPICAL IOSTATS’));
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————————————————————————————————————————————
SQL_ID 698t1z5ruk076, child number 0
————————————-
SELECT /*+gather_plan_statistics*/ * FROM t_table WHERE numcol = 1
Plan hash value: 3519940426
—————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers |
—————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| 0 |00:00:00.01 | 1 |
|* 1 | INDEX RANGE SCAN| T_TABLE_IDX | 1 | 1 | 13 | 0 (0)| 0 |00:00:00.01 | 1 |
—————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(“NUMCOL”=1)
选定了 18 行
–索引是NONUNIQUE INDEX
select OWNER,INDEX_NAME,UNIQUENESS from dba_indexes where table_name=’T_TABLE’;
OWNER INDEX_NAME UNIQUENESS
—————————— —————————— ———-
SKYDL T_TABLE_IDX NONUNIQUE
优化器是如何考虑是否使用Index Unique Scans的呢？ 有两点：
1. 是否一个查询的谓词参照了所有的唯一性索引键值的字段并使用了等值操作符，比如 where emp_id = 100;
2. 索引必须是唯一性的。
同时满足上面的两个条件，优化器才会考虑使用Index Unique Scans。很显然上面的例子不满足第二个条件，因此
优化器忽略了Index Unique Scans。