Oracle如何管理帶約束的B樹索引

管理帶約束的B樹索引
B樹索引與主鍵和唯一鍵約束是分不開的。這是因為Oracle使用索引來強制執行主鍵和唯一鍵約束。若沒有相關聯的B樹索引，將不能啟用主鍵或唯一約束。

在建立主鍵或唯一鍵約束時，你可以選擇讓Oracle自動建立相應索引。在這種情況下，如果刪除或禁用約束，Oracle也將自動刪除相應的索引。

單獨建立索引和約束也是可以的。當單獨建立索引和約束時，允許刪了或禁用約束，而不會自動刪除相應的索引。如果有大量資料需要處理，你可能希望禁用約束，而不刪除相應索引。

因為在定義外來鍵約束時，Oracle不會自動建立索引，所以必須手動在與外來鍵約束相關的列上建立索引。在大多數情況下，在外來鍵列上建立B樹索引是有益的，因為它有助於避免鎖定問題，並提高透過主鍵和外來鍵列連線父/子表的查詢的效能。

1 在主鍵列上建立B樹索引
主鍵約束保證在一列(或列的組合)中的值可用於唯一標識表內的記錄。每個表只能有一個主鍵約束。主鍵約束不能包含空值。主鍵約束可以被看做是唯一(unique)約束和非空(NOT NULL)約束的結合。為每個表建立主鍵索引有如下幾個很好的理由。
.強制執行了主鍵列在表內必須是唯一的這一業務需求。是的，在某些情況下，可能有一個不需要主鍵的表(比如日誌表)，但在大多數情況下，主鍵對每個表都是必需的。
.主鍵中的許多列，在訪問應用程式的查詢的where子句中被頻繁使用。這些列上的索引將會改善查詢的效能。
.除非已定義父表主鍵或唯一鍵約束，否則Oracle將不允許建立子表的外來鍵約束。因此，如果需要外來鍵約束，就必須使用主鍵或唯一鍵約束。

對任何啟用的主鍵，Oracle都需要一個與之對應的索引。有幾種技術可用來建立主鍵約束及其對應的索引。
.首先建立表。然後在單獨的alter table語句中新增主鍵約束。alter table語句同時建立了主鍵約束和索引。
.在create table 語句中內聯(與列一起)或在單獨的部分中指定主鍵約束。
.首先建立表，然後使用create index語句建立包含主鍵列的索引，最後使用alter table ... add constraint語句新增主鍵約束。

1.1.使用alter table來建立主鍵約束和索引
下面介紹的這種技術是建立主鍵約束和相關聯的索引最可取的方法。這種方法允許對錶的建立與約束和索引的定義分別進行管理。如果你使用的應用程式包含數千個表，約束和索引，那麼將建立表與建立相應的約束和索引分開，可以使用管理和診斷安裝問題變得更容易。這不是死板的規定，相反，它是從診斷問題演變而來的一種偏好。

在這個例子中，表和主鍵約束是分別建立的。首先，建立表時沒有定義任何約束。

SQL> create table cust2
  2  (
  3  cust_id number,
  4  first_name varchar2(200),
  5  last_name varchar2(200)
  6  ) tablespace reporting_data;
Table created.

然後新增主鍵約束

SQL> alter table cust2 add constraint cust2_pk primary key(cust_id) using index tablespace reporting_index;
Table altered.

此程式碼示例使用alter table ... add constraint語句同時建立主鍵約束和唯一索引。約束和索引都被命名為CUSTS_PK。

1.2.使用create table建立主鍵約束和索引
另一種常見的方法是用create table語句建立主鍵約束和索引。可以內聯(和列一起)直接指定一個約束。這種方法的優點是簡單。如果在開發或測試環境中進行試驗，這種方法是快速且有效的。但這種方法也有一個缺點，它不允許在多個列上定義主鍵。例如:

SQL> create table cust3
  2  (
  3  cust_id number primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> insert into cust3 values(1,'jing','yong');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST3');
SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
SYS_C0023486                                                                                                                     INDEX                       1    1048576

在這段程式碼中，Oracle建立了主鍵約束和相應的唯一索引。Oracle自動生成像SYS_C0023486這樣的隨機名稱(約束和索引也被賦予了相同的名稱)。

如果想要明確地對約束和索引提供名稱，就可以執行下面這樣的語句:

SQL> create table cust4
  2  (
  3  cust_id number constraint cust4_pk primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> insert into cust4 values(1,'jing','yong');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST4');
SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
CUST4_PK                                                                                                                         INDEX                       1    1048576

也可以指定放置索引的表空間，如下所示:

SQL> create table cust5
  2  (
  3  cust_id number constraint cust5_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.

還可以在create table語句單獨的部分中定義主鍵約束(不和列在一起)。下面是在單獨的部分定義主鍵約束的例子:

SQL> create table cust6
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  constraint cust6_pk primary key(cust_id) using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;
Table created.

這種技術被稱為外聯(out-of-line)，因為約束的宣告與列定義之間是用逗號隔開的。該做法與內聯的方法相比，具有可以為主鍵指定多列的優勢。

1.3.分別建立B-tree索引和主鍵約束
還可以首先建立索引，然後改變表以應用主鍵約束。為了保持這個例子的完整性，這裡也顯示了create table語句。

SQL> create table cust7
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust7_pk on cust7(cust_id) tablespace reporting_index;
Index created.
SQL> alter table cust7 add constraint cust7_pk primary key(cust_id);
Table altered.

這種方法的優點是，可以獨立於索引刪除或禁用主鍵約束。在大型資料庫環境中，出於資料載入時效能方面的原因，有時可能想要刪除或禁用約束。有時可能需要能刪除約束，但不刪除索引的靈活性。在大型資料庫環境中，重建索引會花費很長的時間並消耗大量的系統資源。

另一種稍微有些牽強的情況是，有可能建立一個與主鍵約束定義的列不同的列的索引。例如:

SQL> create table cust8
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create index cust8_pk on cust8(cust_id,first_name,last_name) tablespace reporting_index;
Index created.
SQL> alter table cust8 add constraint cust8_pk primary key(cust_id);
Table altered.

建議不要建立與約束列不同的列的主鍵索引，但這麼做是可以的。你應該知道有這種情況，避免在診斷問題時感到困惑。

1.4.檢視主鍵約束和索引的詳細資訊
用如下語句可以確認某個索引的詳細資訊:

SQL> select index_name,index_type,uniqueness from user_indexes where table_name='CUST7';
INDEX_NAME                                                                                                                       INDEX_TYPE                  UNIQUENES
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- ---------
CUST7_PK                                                                                                                         NORMAL                      UNIQUE

輸出如上所示，為了驗證約束的資訊，可執行如下查詢:

SQL> select constraint_name,constraint_type from user_constraints where table_name='CUST7';
CONSTRAINT_NAME                                                                                                                  CONSTRAINT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
CUST7_PK                                                                                                                         P

1.5.刪除主鍵約束和索引
建立主鍵約束時自動建立的索引不能直接刪除。在這種情況下，如果像下面這樣直接刪除索引:

SQL> drop index cust4_pk;

就會收到如下錯誤資訊:

drop index cust4_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

要刪除索引，必須首先刪除或禁用主鍵約束。例如，下面的語句將刪除建立約束時自動建立的索引:

SQL> alter table cust4 disable constraint cust4_pk;
Table altered.
SQL> alter table cust4 drop constraint cust4_pk;
Table altered.

或者

SQL> alter table cust4 drop primary key;
Table altered.

在刪除或禁用主鍵約束時，可以選擇不刪除相關索引。可以使用drop/disable constraint子句的keep index子句來保留索引。例如:

SQL> alter table cust4 drop constraint cust4_pk keep index;
Table altered.

此程式碼指示Oracle刪除約束，但保留索引。如果要處理的是很大的表，那麼出於載入或操縱資料時效能方面的原因，可能要禁用或刪除約束，而非索引，因為刪除與一個大表相關聯的索引後，可能需要相當長的時間和大量資源來重新建立它。

要注意的另一個方面是，如果主鍵或唯一鍵被已啟用的外來鍵引用，而試圖刪除父表上的約束，如下所示:

SQL> alter table cust1 drop primary key;

就會收到如下錯誤資訊:

alter table cust1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

在這種情況下，需要先刪除或禁用引用的外來鍵，或使用cascade子句在主鍵約束已經被刪除或禁用時，自動刪除外來鍵約束。例如:

SQL>alter table cust1 drop constraint cust1_pk cascade;
SQL>alter table cust1 disable constraint cust1_pk cascade;
SQL>alter table cust1 drop primary key cascade;

級聯刪除約束只刪除了所有依賴外來鍵的約束，但不從子表中刪除任何資料。

2 在唯一鍵列上建立B樹索引
唯一鍵約束的主要目的是強制地不屬於主鍵一部分的列的唯一性。如果有非主鍵列在一個表中是唯一的這種業務需求，那麼應該使用唯一約束。例如，你可能有定義在客戶表的cust_id列上的主鍵，但可能還需要一個last_name和first_name列的組合上的唯一鍵約束。

唯一鍵與主鍵有兩方面的差別。首先，唯一鍵可以包含NULL值，其次每個表可以定義多個唯一鍵(而每個表只能定義一個主鍵)。

如果需要為某個列建立唯一約束，那麼可以透過下列幾種不同的方式來實現這一需求。
.使用alter table語句來建立一個唯一約束。這將自動建立一個唯一的B樹索引。
.使用create table語句來建立一個唯一約束。這也將自動建立一個唯一的B樹索引。
.分別建立B樹索引和約束。如果想要在禁用或刪除約束時分別管理索引和約束，那麼可以使用這種方法。
.只建立唯一B樹索引，而不費心去建立唯一鍵約束。如果索引中的列不能被子表的外來鍵引用，那麼可以使用這種方法。

2.1.使用alter table來建立唯一約束和索引
這種方法是我們啟用唯一鍵約束並建立相應索引的首選方法。正如在主鍵約束和索引部分中提到的，將建立表的語句與建立約束和索引的語句分離，往往更容易診斷安裝問題。

下面的示例演示如何建立一個表，然後在非主鍵列上新增一個唯一鍵約束。

SQL> create table cust9
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.

接下來，使用alter table語句在cust表的last_name和first_name列的組合上建立一個名為cust9_ux1的唯一約束。

SQL> alter table cust9 add constraint cust9_uk1 unique(last_name,first_name) using index tablespace reporting_index;
Table altered.

此語句建立了該唯一約束。此外，Oracle會自動建立一個具有相同名稱的相關索引。

2.2.使用create table建立唯一約束和索引
使用create table方法的優點在於，它簡單且可以把索引和約束的建立封裝在一個語句中。在用create table語句定義唯一約束時，它可以用內網聯方式，也可以用外聯方式來定義。

第一個例子顯示瞭如何在一個列上內聯地建立唯一鍵約束和索引。由於內聯唯一鍵約束只可以定義在列上，所以我們新增了SSN列，它以內聯內式定義了唯一鍵約束。

SQL> create table cust10
  2  (
  3  cust_id number constraint cust10_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15) constraint cust10_uk1 unique using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;
Table created.

下一個例子使用外聯技術在first_name和last_name列的組合上建立了一個唯一約束:

SQL> create table cust11
  2  (
  3  cust_id number constraint cust11_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15),
  7  constraint cust11_uk1 unique(first_name,last_name) using index tablespace reporting_index
  8  )
  9  tablespace reporting_data;
Table created.

外聯方式的定義具有允許在多列上建立一個唯一鍵約束的優勢。

2.3.分別建立B樹索引和唯一鍵約束
如果需要分別管理索引和約束，那麼可以先建立索引，然後再建立約束。例如:

SQL> create table cust12
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust12_uk1 on cust12(first_name,last_name) tablespace reporting_index;
Index created.
SQL> alter table cust12 add constraint cust12_uk1 unique(first_name,last_name);
Table altered.

分別建立索引和約束的好處是，可以在不刪除底層索引的情況下刪了或禁用約束。在處理大量資料時，可考慮使用這種方法。如果你有任何理由需要禁用約束，然後重新啟用它，就可以這樣做而不刪除索引(因為重新建立大索引可能需要很長一段時間)。

2.4.只建立唯一索引
還可以只建立唯一索引而不新增唯一約束。如果你從來沒有計劃用外來鍵引用一個唯一鍵，那麼只建立一個唯一索引而不定義唯一約束也是可以的。下面是建立一個無關聯約束的唯一索引的例子:

SQL> create table cust13
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust13_uk1 on cust13(first_name,last_name) tablespace reporting_index;
Index created.

在用上述語句明確地建立唯一索引時，Oracle雖然建立了一個唯一索引，但卻沒有在dba/all/user_constraints中為約束新增條目。為什麼這很重要呢?，考慮這種情況:

SQL> insert into cust13 values(1,'JAMES','STARK');
1 row created.
SQL> insert into cust13 values(2,'JAMES','STARK');

下面是被丟擲的相應錯誤訊息提示:

insert into cust13 values(2,'JAMES','STARK')
*
ERROR at line 1:
ORA-00001: unique constraint (JY.CUST13_UK1) violated

如果讓你來診斷這個問題的話，你首先會檢查的地方是dba_constraints，你會按錯誤訊息中顯示的名稱在其中查詢一個約束。然而，沒有查到任何資訊。

SQL> select constraint_name from dba_constraints where constraint_name='CUST13_UK1';
no rows selected

沒有相關記錄可能會使用人困惑:向表中插入資料時，丟擲的錯誤訊息已經表明違反了唯一約束，但在與約束相關的資料字典檢視中卻沒有它的資訊。在這種情況下，必須在dba_indexes中檢視已經建立的唯一索引的詳細資訊。例如:

SQL> select index_name,uniqueness from dba_indexes where index_name='CUST13_UK1';
INDEX_NAME                                         UNIQUENES
-------------------------------------------------- ---------
CUST13_UK1                                         UNIQUE

如果你希望能夠使用與約束相關的資料字典檢視來報告唯一鍵約束，就應該也定義一個約束。

2.5.刪除唯一鍵約束和索引
如果索引是建立唯一鍵約束時自動建立的，那麼不能直接刪除該索引。在這種情況下，必須刪除或禁用唯一鍵約束，而相關的索引會自動被刪除。例如:

SQL> drop index cust11_uk1;
drop index cust11_uk1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>alter table cust11 drop constraint cust11_uk1;

這行語句同時刪除約束和索引。如果想保留索引，那麼可以指定keep index子句。

SQL>alter table cust drop constraint cust11_uk1 keep index;

如果分別建立索引和唯一鍵約束，或者如果沒有唯一鍵約束與唯一索引相關聯，那麼可以直接刪除該索引。

3 索引外來鍵列
外來鍵約束確保插入資料到子表時，相應的父表記錄存在。這是一個保證資料符合父/子業務關係規則的機制。外來鍵也被稱為參照完整性約束。

不同於主鍵和唯一鍵約束，Oracle不會自動建立外來鍵列上的索引。因此，必須在定義為外來鍵約束的列的基礎上手動建立一個外來鍵索引。在大多數情況下，應該在與外來鍵關聯的列上建立索引。這裡有兩個原因。.Oracle經常可以利用外來鍵列上的索引，來改善使用外來鍵列來連線父表和子表的查詢效能。.如果外來鍵列上沒有B樹索引存在，在往子表插入資料或從子表刪除資料時，它會鎖定父表中的所有行。對於頻繁修改父表和子表的應用程式，這將導致鎖定和死鎖問題。

首先討論如何在一個外來鍵列上建立B樹索引，然後再介紹用來檢測未被索引的外來鍵列的一些技巧。

3.1.在外來鍵列上實現索引
假設有這樣的需求:必須為address1表的每條記錄分配cust14表中存在的一個相應cust_id列。為了強制執行這種關係，在address1表上建立如下外來鍵約束:

SQL> create table cust14(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;
Table created.
SQL> create table address1(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;
Table created.
SQL> alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id);
alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id)
                                                                                     *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

注意，外來鍵列必須引用父表中定義了主鍵或唯一鍵約束的列。否則，就會收到錯誤提示資訊"ORA-02270: 此列列表無匹配的唯一鍵或主鍵"。

因為在連線cust14表和address1表時，外來鍵列被廣泛使用，且外來鍵列上的索引會提高效能，所以在這種情況下，必須手動建立一個索引。例如，在address1表的cust_id外來鍵列上建立普通的B樹索引。

SQL>create index addr1_fk1 on address1(cust_id);

索引名不必與外來鍵名稱相同。是否這樣做，只是一種個人喜好。我們覺得約束和相應的索引具有相同的名稱時，維護環境更容易。

建立索引時，如果不指定表空間名稱，Oracle會在使用者的預設表空間放置索引。一般情況下，最好明確指定該表的索引的存放位置。例如:

create index addr1_fk1 on address1(cust_id) tablespace reporting_index;

注意，外來鍵列上的索引，並不一定是B樹型別的。在資料倉儲環境中，經常在星型模式的事實表的外來鍵列上使用點陣圖索引。與B樹索引不同，外來鍵列上的點陣圖索引不能解決父/子表鎖定的問題。使用星型模式的應用程式通常不從事實表刪除或修改子記錄，因此在資料倉儲環境中，在外來鍵列上使用點陣圖索引，鎖定不是什麼問題。

3.2.確定外來鍵列是否已經被索引
如果你從頭開始建立一個應用程式，那麼建立程式程式碼，並確保每一個外來鍵約束都有相應的索引很容易。但是，如果你繼承了一個現成的資料庫，就需要審慎地檢查外來鍵列是否已經被索引。

你可以使用資料字典檢視來驗證，外來鍵約束的所有列上是否有相應的索引。其基本思路是檢查每個外來鍵約束，看它是否有一個相應的索引，這個任務並不像一開始看上去那麼簡單。用下面的查詢作為例子，它可以用來指導你按正確的途徑入手:

SQL> col owner for a30
SQL> col cons_name for a30
SQL> col tab_name for a30
SQL> col cons_column for a30
SQL> col ind_column for a30
SQL> select distinct
  2  a.owner owner,
  3  a.constraint_name cons_name,
  4  a.table_name tab_name,
  5  b.column_name cons_column,
  6  nvl(c.column_name,'***Check index***') ind_column
  7  from dba_constraints a,dba_cons_columns b,dba_ind_columns c
  8  where a.constraint_type='R'
  9  and a.owner=UpPER('&&user_name')
 10  and a.owner=b.owner
 11  and a.constraint_name=b.constraint_name
 12  and b.column_name=c.column_name(+)
 13  and b.table_name=c.table_name(+)
 14  and b.position=c.column_position(+)
 15  order by tab_name,ind_column;
old   9: and a.owner=UpPER('&&user_name')
new   9: and a.owner=UpPER('JY')
OWNER                          CONS_NAME                      TAB_NAME                       CONS_COLUMN                    IND_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
JY                             ADDR_FK1                       ADDRESS                        CUST_ID                        CUST_ID

雖然此查詢簡單並易於理解，但它並不能在所有情況下都正確地報告出未索引的外來鍵。例如，在多列外來鍵的情況下，以不同於索引列的順序定義約束也不要緊，只要索引列在該索引中位於前面即可。換句話說，如果約束被定義為col1和col2，那麼定義一個先是col2,然後是col1的B樹索引也沒關係。

另一方面，使用B樹索引有助於避免鎖定的問題，但點陣圖索引卻做不到這點。在這種情況下，查詢還應該檢查索引型別。

在這些情況下，需要用更復雜的查詢來檢測與外來鍵列相關的索引問題。下面的例子是一個更復雜的查詢，它使用listagg分析函式來比較外來鍵約束列(作為字串返回一行)與相應的索引列:

這個查詢會先提示你輸入一個模式名稱，然後將顯示沒有相應的索引的外來鍵約束。此查詢還檢查了索引型別，點陣圖索引可以在外來鍵列上存在，但它不能防止鎖定問題。

SQL> select
  2   case when ind.index_name is not null then
  3     case when ind.index_type in('BITMAP') then
  4        '** Bitmp idx **'
  5     else
  6        'indexed'
  7     end
  8   else
  9     '** Check idx **'
 10   end checker,
 11   ind.index_type,
 12   cons.owner,cons.table_name,ind.index_name,cons.constraint_name,cons.cols
 13   from( select
 14         c.owner,c.table_name,c.constraint_name,
 15         listagg(cc.column_name,',') within group (order by cc.column_name) cols
 16         from dba_constraints c,dba_cons_columns cc
 17         where c.owner=cc.owner
 18         and c.owner=UPPER('&&schema')
 19         and c.constraint_name=cc.constraint_name
 20         and c.constraint_type='R'
 21         group by c.owner,c.table_name,c.constraint_name) cons
 22  left outer join
 23  (select
 24    table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols
 25    from(select
 26         ic.table_owner,ic.table_name,ic.index_name,ic.column_name,ic.column_position,i.index_type,
 27         connect_by_root(ic.column_name) cbr
 28         from dba_ind_columns ic,dba_indexes i
 29         where ic.table_owner=UPPER('&&schema')
 30         and ic.table_owner=i.table_owner
 31         and ic.table_name=i.table_name
 32         and ic.index_name=i.index_name
 33         connect by prior ic.column_position-1=ic.column_position
 34         and prior ic.index_name=ic.index_name)
 35  group by table_owner,table_name,index_name,index_type,cbr) ind
 36  on cons.cols=ind.cols
 37  and cons.table_name=ind.table_name
 38  and cons.owner=ind.table_owner
 39  order by checker,cons.owner,cons.table_name;
Enter value for schema: JY
old  18:        and c.owner=UPPER('&&schema')
new  18:        and c.owner=UPPER('JY')
old  29:        where ic.table_owner=UPPER('&&schema')
new  29:        where ic.table_owner=UPPER('JY')
CHECKER                        INDEX_TYPE                     OWNER                          TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                COLS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
** Check idx **                                               JY                             ADDRESS                                                       ADDR_FK1                       CUST_ID

表鎖和外來鍵
下面用一個簡單的例子來演示外來鍵列沒有索引時的鎖定問題。

首先建立兩個表(dept和emp)並用一個外來鍵約束把它們關聯起來。

SQL> create table emp(emp_id number primary key,dept_id number);
Table created.
SQL> create table dept(dept_id number primary key);
Table created.
SQL> alter table emp add constraint emp_fk1 foreign key(dept_id) references dept(dept_id);
Table altered.

插入資料

SQL> insert into dept values(10);
1 row created.
SQL> insert into dept values(20);
1 row created.
SQL> insert into dept values(30);
1 row created.
SQL> insert into emp values(1,10);
1 row created.
SQL> insert into emp values(2,20);
1 row created.
SQL> insert into emp values(3,30);
1 row created.
SQL> commit;
Commit complete.

開啟兩個終端會話。在一個會話中，從子表刪除一條記錄，但不提交。

SQL> delete from emp where dept_id=10;
1 row deleted.

現在嘗試(在另一個會話裡)從父表中刪除一些不受子表刪除操作影響的資料。

SQL> delete from dept where dept_id=30;

對父表資料的刪除操作會掛起，直到子表的事務初步提交(或回滾)。如果子表中的外來鍵列上沒有常規的B樹索引，那麼任何時間嘗試往子表插入資料或刪除子表的資料時，它都會在父表上放置一個全表鎖，在子表的事務完成前，該全表鎖會一直阻止刪除或更新父表的資料。

回滾刪除子表資料的操作

SQL> rollback;
Rollback complete.

當回滾刪除子表資料的操作後，刪除父表資料的操作報錯，因為違反完整性約束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

現在額外建立一個子表外來鍵列上的索引，並再次執行前面的操作。

SQL> create index emp_fk1 on emp(dept_id);
Index created.

再次執行刪除操作，刪除子表的資料

SQL> delete from emp where dept_id=10;
1 row deleted.

在另一個會話中刪除父表資料不會掛起會立即報違反完整性約束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

這樣就能夠獨立地執行前面列出的兩個delete語句。當外來鍵列上有B樹索引時，如果從子表中刪除資料，Oracle將不會過分地鎖定父表中的所有行資料。