ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle主键与复合主键的性能分析

Oracle主键与复合主键的性能分析

原创 Linux操作系统 作者:lovehewenyu 时间:2012-07-09 12:01:47 0 删除 编辑

Oracle主键与复合主键的性能分析

 

 

总结:

1、主键和复合主键,查询性能相同(索引高度相同,恰当的运用索引)。

2 主键和复合主键,(update,insert)性能不同 (因为复合主键会用更多的块来创建索引,所以update,insert性能低)

 

 

实验思路:

1、  建立实验表,及主键,联合2个主键,联合3个主键

2、  查看索引的结构

3、查看条件相同的,执行计划(来确定主键和复合主键的效率)

 

 

一、              建立实验表;test1为单主键为1columntest2为联合主键2columnstest3为联合主键3columns

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

 

分析:通过执行SQLINDEX 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 手动创建数据库
请登录后发表评论 登录
全部评论
10年老鸟,Oracle,Mysql,EMC 存储,NBU备份

注册时间:2012-02-03

  • 博文量
    268
  • 访问量
    1301451