[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个。