ITPub博客

首页 > 数据库 > 数据库开发技术 > 外键没有索引对性能的影响(2)

外键没有索引对性能的影响(2)

原创 数据库开发技术 作者:litterbaby 时间:2008-08-18 15:08:42 0 删除 编辑
外键没有索引对性能的影响[@more@]

建立一个索引

create index tmp_ind_1 on tmp_f (num,id);

exec dbms_stats.gather_table_stats('KB','tmp_f');

exec dbms_stats.gather_table_stats('KB','tmp_p');

SQL> delete from tmp_p where pk =100001;

已删除 1 行。

执行计划

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

Plan hash value: 797653082

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | DELETE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | DELETE | TMP_P | | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C0034921 | 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("PK"=100001)

统计信息

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

9 recursive calls

7 db block gets

2929 consistent gets

6 physical reads

764 redo size

393 bytes sent via SQL*Net to client

323 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> select index_name ,leaf_blocks from user_indexes;

INDEX_NAME LEAF_BLOCKS

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

TMP_IND_1 2909

修改索引:

create index tmp_ind_1 on tmp_f (id,object_name);

exec dbms_stats.gather_table_stats('KB','tmp_f');

exec dbms_stats.gather_table_stats('KB','tmp_p');

SQL> delete from tmp_p where pk =100001;

已删除 1 行。

执行计划

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

Plan hash value: 797653082

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | DELETE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | DELETE | TMP_P | | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C0034921 | 1 | 5 | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("PK"=100001)

统计信息

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

1 recursive calls

8 db block gets

2 consistent gets

0 physical reads

0 redo size

394 bytes sent via SQL*Net to client

323 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

SQL> select index_name ,leaf_blocks from user_indexes;

INDEX_NAME LEAF_BLOCKS

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

TMP_IND_1 5557

将上述的索引的次序换换的话会出现什么情况那?

SQL> drop index tmp_ind_1;

索引已删除。

SQL> create index tmp_ind_1 on tmp_f (object_name,id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats('KB','tmp_f');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats('KB','tmp_p');

PL/SQL 过程已成功完成。

SQL>

SQL> delete from tmp_p where pk =100001;

已删除 1 行。

执行计划

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

Plan hash value: 797653082

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time

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

| 0 | DELETE STATEMENT | | 1 | 5 | 1 (0)| 00:00:0

| 1 | DELETE | TMP_P | | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C0034921 | 1 | 5 | 1 (0)| 00:00:0

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

Predicate Information (identified by operation id):

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

2 - access("PK"=100001)

统计信息

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

9 recursive calls

7 db block gets

5654 consistent gets

1242 physical reads

644 redo size

389 bytes sent via SQL*Net to client

324 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1 rows processed

SQL>

SQL> select index_name ,leaf_blocks from user_indexes;

INDEX_NAME LEAF_BLOCKS

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

TMP_IND_1 5582

这是因为在外键上如果没有索引的时候,Oracle需要对数据保证一致性,所以进行全表扫描。

在建立索引的情况下,如果索引上引导列就是所需要的数据时候,就会按照索引的正常范围扫描来实现,但是如果索引的引导列不是所需要的外键列时候,就会对索引进行全索引扫描。这样就会造成非常严重的性能问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/222350/viewspace-1009123/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    131
  • 访问量
    434271