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

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

--------------------------------------------------------------------------------

-

|

--------------------------------------------------------------------------------

-

| 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

--------------------------------------------------------------------------------

-

|

--------------------------------------------------------------------------------

-

| 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

--------------------------------------------------------------------------------

-

|

--------------------------------------------------------------------------------

-

| 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索引的情況下，如果我們的索引高度相同,且正確的應用索引。這樣的情況下我們查詢效能是相同的。

歡迎大家給與糾正錯誤，共同提升！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-734960/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章