Oracle主鍵與複合主鍵的效能分析

總結：
1、主鍵和複合主鍵，查詢效能相同（索引高度相同，恰當的運用索引）。
2、主鍵和複合主鍵，(update,insert)效能不同(因為複合主鍵會用更多的塊來建立索引，所以update,insert效能低)
 
實驗思路：
1、 建立實驗表，及主鍵，聯合2個主鍵，聯合3個主鍵
2、 查看索引的結構
3、查看條件相同的，執行計畫（來確定主鍵和複合主鍵的效率）
 
 
一、             建立實驗表；test1為單主鍵為1個column，test2為聯合主鍵2個columns，test3為聯合主鍵3個columns
SQL> create table test1(a number,b number,c number,primary key(a));
 
Table created.
 
SQL> create table test2(a number,b number,c number,primary key(a,b));
 
Table created.
 
SQL> create table test3(a number,b number,c number,primary key(a,b,c));
 
Table created.
 
二、             查看索引的結構
1、先查看一下建立的表對應的索引
SQL> select index_name,table_name from user_indexes;
 
INDEX_NAME                    TABLE_NAME
------------------------------ ------------------------------
SYS_C005198                   TEST1
SYS_C005199                   TEST2
SYS_C005200                   TEST3
 
2、寫個儲存過程來給實驗表插入資料
begin
for i in 1..10000 loop
insert into test1 values(i,i+1,i+2);
commit;
end loop;
end;
 
 
Test1
SQL>analyze index SYS_C005198 validate structure;
 
Index analyzed.
 
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;
 
   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
        2        24         1        18     10000          0
Test2
SQL> analyze index SYS_C005199 validate structure;    
 
Index analyzed.
 
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;
 
 
   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
        2        32         1        23     10000          0
Test3
SQL>analyze index SYS_C005200 validate structure;
 
Index analyzed.
 
SQL> select HEIGHT,BLOCKS,BR_BLKS,LF_BLKS,LF_ROWS,DEL_LF_ROWS from index_stats ;
 
   HEIGHT    BLOCKS   BR_BLKS   LF_BLKS   LF_ROWS DEL_LF_ROWS
---------- ---------- ---------- ---------- ---------- -----------
        2        40         1        28     10000          0
 
總結：根據B-TREE索引的結構特點。說明主鍵和聯合主鍵，同樣的資料聯合主鍵需要更多的資源來維護索引。（聯合主鍵索引因為用了更多的塊，所以update,insert會比主鍵索引慢一些。至於查詢下面研究）
 
三、             查看相同情況下，主鍵的效率。
 
1、 語句都讓其走INDEX UNIQUE SCAN，看看效率：
 
 
Test1
SQL> select a from test1 where a=5555;
 
        A
----------
     5555
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2716871853
 
--------------------------------------------------------------------------------
-
 
| Id | Operation        | Name       | Rows | Bytes | Cost (%CPU)| Time
|
 
--------------------------------------------------------------------------------
-
 
|  0 | SELECT STATEMENT |            |    1 |   13 |    1  (0)| 00:00:01
|
 
|* 1 | INDEX UNIQUE SCAN| SYS_C005198 |    1 |   13 |    1  (0)| 00:00:01
|
 
--------------------------------------------------------------------------------
-
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 - access("A"=5555)
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         2 consistent gets
         0 physical reads
         0 redo size
       405 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
1        rows processed
Test2
SQL> select a,b from test2 where a=5555 and b=5556;
 
        A         B
---------- ----------
     5555      5556
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3210951477
 
--------------------------------------------------------------------------------
-
 
| Id | Operation        | Name       | Rows | Bytes | Cost (%CPU)| Time
|
 
--------------------------------------------------------------------------------
-
 
|  0 | SELECT STATEMENT |            |    1 |   26 |    1  (0)| 00:00:01
|
 
|* 1 | INDEX UNIQUE SCAN| SYS_C005199 |    1 |   26 |    1  (0)| 00:00:01
|
 
--------------------------------------------------------------------------------
-
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 - access("A"=5555 AND "B"=5556)
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         2 consistent gets
         0 physical reads
         0 redo size
       460 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
1        rows processed
Test3
SQL> select a,b,c from test3 where a=5555 and b=5556 and c=5557;
 
        A         B         C
---------- ---------- ----------
     5555      5556      5557
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1852305570
 
--------------------------------------------------------------------------------
-
 
| Id | Operation        | Name       | Rows | Bytes | Cost (%CPU)| Time
|
 
--------------------------------------------------------------------------------
-
 
|  0 | SELECT STATEMENT |            |    1 |   39 |    1  (0)| 00:00:01
|
 
|* 1 | INDEX UNIQUE SCAN| SYS_C005200 |    1 |   39 |    1  (0)| 00:00:01
|
 
--------------------------------------------------------------------------------
-
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  1 - access("A"=5555 AND "B"=5556 AND "C"=5557)
 
 
Statistics
----------------------------------------------------------
         1 recursive calls
         0 db block gets
         2 consistent gets
         0 physical reads
         0 redo size
       515 bytes sent via SQL*Net to client
       385 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed
 
分析：通過執行SQL走INDEX UNIQUE SCAN索引的情況，分析執行計畫得到的結果是主鍵和聯合主鍵效能相同：
 
（我們關注的：
          1 recursive calls
         0 db block gets
         2 consistent gets
         0 physical reads
         0 redo size
消耗一致和COST消耗一致。）
 
總結：主鍵和聯合主鍵，應用B-tree索引的情況下，如果我們的索引高度相同,且正確的應用索引。這樣的情況下我們查詢效能是相同的。
 
 
歡迎大家給與糾正錯誤，共同提升！