[20220105]建立非唯一主键对性能有影响吗.txt

来源：这里教程网时间：2026-03-03 17:21:37 作者：

[20220105]建立非唯一主键对性能有影响吗.txt --//昨天优化项目，发现一个主键的索引非唯一，估计开始存在主键冲突，索引选择非唯一，解决后没有改正过来。 --//测试看看这样情况逻辑读是否存在变化。 1.环境: SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试 create table deptx as select * from dept; create index pk_deptx on deptx (deptno); SCOTT@book> select uniqueness from user_indexes where index_name = 'PK_DEPTX'; UNIQUENES --------- NONUNIQUE alter table deptx add constraint pk_deptx primary key (deptno) using index pk_deptx; SCOTT@book> @gts deptx Gather Table Statistics for table deptx... PL/SQL procedure successfully completed. --//这样建立非唯一索引作为主键。 3.测试性能: SCOTT@book> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book> select * from deptx where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID cpn6gafrn12h6, child number 0 ------------------------------------- select * from deptx where deptno=10 Plan hash value: 296087899 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 3 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPTX | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 2 | INDEX RANGE SCAN | PK_DEPTX | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 2 | ---------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPTX@SEL$1 2 - SEL$1 / DEPTX@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=10) SCOTT@book> select * from dept where deptno=10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 4xamnunv51w9j, child number 0 ------------------------------------- select * from dept where deptno=10 Plan hash value: 2852011669 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 20 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=10) --//可以看出逻辑读不同，前者是INDEX RANGE SCAN，后者是INDEX UNIQUE SCAN，逻辑读相差1个。