外键列上缺少索引会带来两个问题,限制并发性、影响性能。而这两个问题中的任意一个都可能会造成严重性能问题。
无论是Oracle的官方文档,还是在Tom的书中都说明了两种情况下可以忽略外键上的索引。其实我认为不需要那么麻烦,与增加一个索引所带来的性能开销和磁盘空间开销相比,确实索引可能引发的问题要严重得多。因此,我会选择在所有的外键列上添加索引,虽然可能导致创建了部分多余的索引,但是这样相除了外键约束由于确实索引所带来的性能问题和并发性问题。
如果外键列上缺少索引,从主表关联子表的查询就只能对子表选择全表扫描的查询,这是显而易见的问题:
SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> ALTER TABLE T_P ADD PRIMARY KEY (ID);
表已更改。
SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C 2 FOREIGN KEY (FID) 3 REFERENCES T_P (ID);
表已更改。
SQL> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
已创建884行。
SQL> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 884) + 1, OBJECT_NAME 2 FROM ALL_OBJECTS;
已创建30339行。
SQL> COMMIT;
提交完成。
SQL> SELECT A.ID, A.NAME, B.NAME 2 FROM T_P A, T_C B 3 WHERE A.ID = B.FID 4 AND A.ID = 880;
ID NAME NAME ---------- ------------------------------ ------------------------------ 880 T_COMPRESS /eb2b6b5_Options1 880 T_COMPRESS DATE 880 T_COMPRESS DEF$_SCHEDULE 880 T_COMPRESS GV_$SESSION_EVENT . . . 880 T_COMPRESS sun/io/ByteToCharCp1251 880 T_COMPRESS /5ba3839f_DirStateFactoryResul 880 T_COMPRESS USER_INDEXTYPES
已选择34行。
执行计划 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P' 3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE) 4 1 FILTER 5 4 TABLE ACCESS (FULL) OF 'T_C'
统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 190 consistent gets 0 physical reads 0 redo size 1829 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 34 rows processed
|
由于缺少索引,上面的这个关联查询只能采用MERGE JOIN,而如果联立了外键列上的索引:
SQL> CREATE INDEX IND_T_C_FID ON T_C (FID);
索引已创建。
SQL> SELECT A.ID, A.NAME, B.NAME 2 FROM T_P A, T_C B 3 WHERE A.ID = B.FID 4 AND A.ID = 880;
ID NAME NAME ---------- ------------------------------ ------------------------------ 880 T_COMPRESS /e1538703_EntryInfoImpl 880 T_COMPRESS /7b832daf_ObjectStreamClassCom 880 T_COMPRESS java/awt/peer/ScrollbarPeer 880 T_COMPRESS /1982bd95_PermissionsEnumerato . . . 880 T_COMPRESS /9ebda46b_GetInterface 880 T_COMPRESS /c71f85e7_DefaultPopupFactory 880 T_COMPRESS /7b549d81_DataFormatException
已选择34行。
执行计划 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_P' 3 2 INDEX (UNIQUE SCAN) OF 'SYS_C002964' (UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_C' 5 4 INDEX (RANGE SCAN) OF 'IND_T_C_FID' (NON-UNIQUE)
统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 42 consistent gets 1 physical reads 0 redo size 1829 bytes sent via SQL*Net to client 394 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 34 rows processed
|
SQL> SET AUTOT OFF SQL> SELECT * FROM T_P WHERE ID < 5;
ID NAME ---------- ------------------------------ 1 SEG$ 2 CLU$ 3 OBJ$ 4 FILE$
SQL> SELECT * FROM T_C WHERE ID < 5;
ID FID NAME ---------- ---------- ------------------------------ 1 2 /1005bd30_LnkdConstant 2 3 /10076b23_OraCustomDatumClosur 3 4 /10297c91_SAXAttrList 4 5 /103a2e73_DefaultEditorKitEndP
|
下面在另一个会话中删除子表的一条记录:
SQL> SET SQLP 'SQL2> ' SQL2> DELETE T_C WHERE ID = 2;
已删除 1 行。
删除了一条为2的子表级联,其对应的主表记录ID为3,下面尝试在第一个会话新增一条ID为1000的记录,然后删除这条记录:
SQL> INSERT INTO T_P VALUES (1000, 'A');
已创建 1 行。
SQL> DELETE T_P WHERE ID = 1000;
已删除 1 行。
SQL> ROLLBACK;
回退已完成。
可以看到,并没有发生锁表的情况,这是因为子表外键列上有索引,删除主表的记录时,只会锁定子表参考主表的对应记录。
会话二回滚:
SQL2> ROLLBACK;
回退已完成。
下面删除外键索引:
SQL> DROP INDEX IND_T_C_FID;
索引已删除。
重复刚才的操作,在另一个会话执行删除操作:
SQL2> DELETE T_C WHERE ID = 2;
已删除 1 行。
在会话一重复插入和删除操作:
SQL> INSERT INTO T_P VALUES (1000, 'A');
已创建 1 行。
SQL> DELETE T_P WHERE ID = 1000;
这时会话被锁住,因为缺少了外键索引后,主表删除或更新记录会导致子表整个表被锁,而这会导致严重的系统并发问题。
SQL2> ROLLBACK;
回退已完成。
会话2回滚后,会话1的删除操作才可以继续执行:
已删除 1 行。
SQL>
|
可能有些人会认为,系统中不存在删除而不会导致这个问题,其实不仅是删除,主键列的更新同样可以导致这个问题。
而且这种更新可能是工具帮你自动完成的,因为很多工具会自动生成SQL语句,而在这种生成的SQL语句中,UPDATE的列是表中的所有列,所以即使主键的值没有发生变化,但是仍然是被更新了:
SQL2> DELETE T_C WHERE ID = 2; |
已删除 1 行。
还是删除这条ID为2的子表记录,下面在主表执行一个更新操作:
SQL> UPDATE T_P SET ID = 500 WHERE ID = 500; |
可以看到,不管值是否发生了变化,只要主键列被更新,就会导致操作被锁定。
显而易见,不加索引的外键列会造成严重的性能问题,所以除非你有十分的把握,否则还是在外键列上添加索引吧。
分享到:
相关推荐
另附以下功能 * 生成目录树 * 生成迁移脚本 * 查找所有System.out,.err * 批量替换多关键字 详情见 http://blog.csdn.net/amosryan/article/details/6684465
主要介绍了Oracle外键不加索引引起死锁的情况及解决,需要的朋友可以参考下
如何在oracle中查询所有用户表的表名、主键名称、索引、外键等
主要介绍了Oracle中检查外键是否有索引的SQL脚本分享,本文给出了两个版本的脚本源码,一个查询所有用户,一个查询单用户,需要的朋友可以参考下
应该建索引列的特点: ...5)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 6)在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
外键指定一个列(或一组列)的值必须符合另一个表的一些行的值。我们说这是维持关联表的参照完整性。 在图形化界面中,在 外键 选项卡,只需简单地点击外键栏位来编辑。使用外键的工具栏,能让你创建新的、编辑或...
Oracle 透明数据加密 (TDE) 能够加密存储在表和表空间中的敏感数据,例如手机号码,身份证号等,对于有权访问数据的数据库用户或应用程序,加密数据将被透明地解密。...ORA -28335:无法加加密引用或引用的外键约束列
主键是定义一个表中起主要作用的数据项,这些数据项的数据在表中是唯一的,同时系统按主键为表建立索引。 外键是定义一个表中的某数据项的数据,要参照另一个表的主键数据。既没有在另一个表的主键数据中出现的数据...
11.6.3 外键是否应该加索引? 475 11.6.4 为什么没有使用我的索引? 476 11.6.5 神话:索引中从不重用空间 483 11.6.6 神话:最有差别的元素应该在最前面 486 11.7 小结 490 第12章 数据类型 491 12.1 Oracle...
” 或 Ctrl+鼠标点击字串“[user.]objectName[@dbLink]”,如果是一表名,则能清楚地显示表的列信息(包括列名称、数据类型及长度、默认值、非空)、索引、约束条件(主键、唯一键、检查键、子表、触发器、外键、...
索引,包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在案例精讲中,对表压缩、约束的使能与失能、表的层次结构查询、防止删除表及对象、提取创建外键约束的...
CLOB, LONG) <2> 运行命令 "desc tablename" 能清楚地显示表的所有列(包括名称、数据类型及长度、默认值、非空)、所有的索引、所有的约束条件(主键、唯一键、检查键、子表、外键、触发器、甚至外键的外键) ...
提供了一个Oracle数据库建表语句的完整示例代码,用于演示如何在Oracle环境中创建一个结构化的数据表。 资源优点: 完整性展示:DEMO提供了从基础建表到复杂约束(如外键、自增主键)等实际生产场景所需的完整SQL...
说明:Oracle中需要创建用户一定是要具有dba(数据库管理员)权限的用户才能创建,而且创建的新用户不具备任何权限,连登录都不可以。 用法:create user 新用户名 identified by 密码 例子: 2. 修改密码 说明:...
索引,包括B树索引、基于函数的索引、位图索引、反向索引、降序索引、压缩索引等的使用方法及其适用情形等。在案例精讲中,对表压缩、约束的使能与失能、表的层次结构查询、防止删除表及对象、提取创建外键约束的...
Michael McLaughlin,OracleACE,是美国爱达荷州杨百翰大学计算机信息技术系教授,Michael参与Oracle公司系列产品的研发已经有20年了,担任过开发者、DBA,以及电子商务套件应用程序DBA。 他在咨询、支持和开发方面...
判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上,就必须熟悉数据库应用程序中的所有SQL语句,从中统计出常用的可能对性能有影响的部分SQL,分析、归纳出作为Where条件子句的字段及其组合...