[20220105]建立非唯一主鍵對效能有影響嗎.txt

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