未加索引的外键(unindexed foreign keys)
来源:互联网 发布:故乡的云 知乎 编辑:程序博客网 时间:2024/06/06 04:01
英文原文和主要观点节选自Thomas Kyte《Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions》一书的第6章locking and latching,本人在开发环境做了验证。
Oracle will place a full table lock on a child table after modification of the parent table in two cases:
• If you update the parent table’s primary key (a very rare occurrence if you follow the rule of relational databases stating that primary keys should be immutable), the child table will be locked in the absence of an index on the foreign key.
• If you delete a parent table row, the entire child table will be locked (in the absence ofan index on the foreign key) as well.
These full table locks are a short-term occurrence in Oracle9i and above, meaning they need to be taken for the duration of the DML operation, not the entire transaction. Even so,they can and do cause large locking
修改外键父表后,oracle在两种情况下会发生子表的全表锁
1) update父表主键(如果遵从关系型数据库主键不变的原则的话会很少发生),如子表的外键未加索引,将被全表锁
2) 如delete父表一行且子表外键未加索引,也会全表锁子表
这些全表锁在oracle 9i及以上版本是短期发生的。意味着子表的全表锁发生于父表的DML操作(前面提到的update和delete)期间而非整个事务。尽管如此,当外键没有索引子表较大时,相应的父表DML操作检查子表的数据一致性耗时也较长,一旦全表锁持续较长时间,对子表做其他DML操作的session等待得也就越多,数据库的并发性和性能会受到影响。
SQL> create table p ( x int primary key );
Table created
创建父表
SQL> create table c ( x references p );
Table created
创建子表
SQL> insert into p values ( 1 );
1 row inserted
SQL> insert into p values ( 2 );
1 row inserted
父表插入数据
SQL> insert into c values ( 2 );
1 row inserted
子表插入数据
这时再开一个窗口,另起session
SQL> delete from p where x = 1;
会发现处于block等待状态,直到前面的窗口做了commit,对父表的删除才能成功执行,这是因为前一个窗口不commit,后面的窗口无法获得子表的全表锁
SQL> create index c_x on c(X);
Index created
SQL> select * from c where x=2 for update;
X
---------------------------------------
2
给子表的外键字段创建索引,再锁住一行
再开新的session
SQL> delete from p where x = 1;
1 row deleted
删除成功,说明在外键有索引的情况下,不再需要子表的全表锁
So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:
• You do not delete from the parent table.
• You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!).
• You do not join from the parent to the child (like DEPT to EMP).
If you satisfy all three conditions, feel free to skip the index—it is not needed. If you meet
any of the preceding conditions, be aware of the consequences. This is the one rare instance when Oracle tends to “overlock” data.
书中描述了哪些情况下无需创建外键的索引,相应的,存在以下需要对外键创建索引的情形:
1) 需对父表delete
2) Update父表主键或者存在唯一性约束的字段(需考虑一些sql自动生成工具)
3) 父子表关联,通过父表查询子表
将测试案例中父表的主键改为唯一性约束字段,测试结果一致。
- 未加索引的外键(unindexed foreign keys)
- Locks and Unindexed Foreign Keys
- 未加索引的外键导致的死锁模拟实验以及问题
- EF中的外键以及关联(Foreign Keys and Relationships (Associations))
- Oracle/PLSQL: Foreign Keys
- Oracle/PLSQL: Foreign Keys
- 查看未创建索引的外键
- 查看外键没有加索引的SQL
- Adding Primary Keys and Foreign Keys to a Table
- Index Monitoring and Foreign Keys – Caution
- mysql 使用外键 foreign key 时的错误解决
- opencv_objdetect231d.lib未加的错误
- 给未加皮肤的EXE文件加皮肤
- ORA-02449: unique/primary keys in table referenced by foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- truncate table 错误 ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- ORA-02266: unique/primary keys in table referenced by enabled foreign keys
- 再一次强调,ORACLE外键必须加索引
- [转]NS2 Data Collections by mitkook
- 程序员必备工具下载
- Java内存管理基础篇- Java内存分配
- Myeclipse 6.0 注册码
- 获得星期几的程序--基姆拉尔森计算公式
- 未加索引的外键(unindexed foreign keys)
- float数据在内存中的存储方法
- DNS污染与DNS劫持
- Java设计模式
- 过滤多个文件中带warn的行,将该行保留到新文件中
- oracle数据类型
- 国外编程网站
- loadView VS viewDidLoad
- oracle 修改用户密码