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

外键没有索引对性能的影响[@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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/222350/viewspace-1009123/