当我们更新父表主键或者删除一个父记录时，都会在子表上加一个表锁，在这条语句完成之前，不允许对子表做任何修改，从而影响并发性。

外键未加索引，可能导致如下问题：
1、删除父表多行时，对于父表的每一行删除，都会把子表全表扫描一次
2、从父表查询子表时，没有索引查询很慢（这就是为什么内嵌表时，建议对隐藏列nested_column_id加索引）

查询操作：

JEL@JEL >create table t1 (id number primary key,name varchar2(20));

Table created.

JEL@JEL >create table t2 (id number,fid number references t1(id),name varchar2(20));

Table created.

JEL@JEL >insert into t1 select object_id,OBJECT_TYPE from all_objects;

9379 rows created.

JEL@JEL >insert into t2 select DATA_OBJECT_ID,object_id,OBJECT_TYPE from all_objects;

9379 rows created.

JEL@JEL >commit;

JEL@JEL >set autotrace on;
JEL@JEL >select f.id,f.name,c.name from t1 f,t2 c where f.id=c.fid and f.id=20;

        ID NAME                 NAME
---------- -------------------- --------------------
        20 TABLE                TABLE


Execution Plan
----------------------------------------------------------
Plan hash value: 3227500623

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

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

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

|   0 | SELECT STATEMENT             |             |     1 |    21 |    19   (0)
| 00:00:01 |

|   1 |  NESTED LOOPS                |             |     1 |    21 |    19   (0)
| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |    10 |     2   (0)
| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | SYS_C002870 |     1 |       |     1   (0)
| 00:00:01 |

|*  4 |   TABLE ACCESS FULL          | T2          |     1 |    11 |    17   (0)
| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F"."ID"=20)
   4 - filter("C"."FID"=20)


Statistics
----------------------------------------------------------
       1317  recursive calls
          0  db block gets
        267  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
          1  rows processed


没有给子表创建索引，所以对t2表进行了全表扫描。

JEL@JEL >create index i_t2 on t2(fid);

Index created.

JEL@JEL >select f.id,f.name,c.name from t1 f,t2 c where f.id=c.fid and f.id=20;

        ID NAME                 NAME
---------- -------------------- --------------------
        20 TABLE                TABLE


Execution Plan
----------------------------------------------------------
Plan hash value: 4198376869

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

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

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

|   0 | SELECT STATEMENT             |             |     1 |    21 |     4   (0)
| 00:00:01 |

|   1 |  NESTED LOOPS                |             |     1 |    21 |     4   (0)
| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |     1 |    10 |     2   (0)
| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | SYS_C002870 |     1 |       |     1   (0)
| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| T2          |     1 |    11 |     2   (0)
| 00:00:01 |

|*  5 |    INDEX RANGE SCAN          | I_T2        |     1 |       |     1   (0)
| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F"."ID"=20)
   5 - access("C"."FID"=20)


Statistics
----------------------------------------------------------
       1362  recursive calls
          0  db block gets
        224  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          1  rows processed

利用了索引进行查找

删除操作：

会话一
JEL@JEL >select OWNER,INDEX_NAME,INDEX_TYPE from all_indexes where index_name='I_T2';

no rows selected

JEL@JEL >delete t2 where fid=20;

1 row deleted.

会话二
JEL@JEL >insert into t1 values (1,'T');

1 row created.

JEL@JEL >delete t1 where id=1;


会话二被锁定，无法操作

会话一执行rollback后，会话二完成删除


JEL@JEL >create index i_t2 on t2(fid);

Index created.

外键创建索引，重复以上操作，顺利完成。

以上可知，不管父表主键有没有变化，都会锁定子表

结论，外键需要加索引