Oracle中主鍵約束跟唯一索引之間的關(guān)聯(lián)關(guān)系

在Oracle中，可以在創(chuàng)建主鍵約束的時(shí)候自動(dòng)創(chuàng)建唯一索引，也可以先創(chuàng)建唯一索引，然后再基于這個(gè)唯一索引來(lái)創(chuàng)建主鍵約束。后一種方式有一個(gè)好處，在 需要對(duì)數(shù)據(jù)量比較大而且讀寫頻繁的OLTP表創(chuàng)建主鍵約束的時(shí)候，可以先ONLINE的創(chuàng)建一個(gè)唯一的索引，然后再創(chuàng)建主鍵約束，這樣可以減少對(duì)表的讀寫 阻塞。但這樣就帶來(lái)一個(gè)問(wèn)題，第一種方式創(chuàng)建的索引在刪除約束的時(shí)候索引會(huì)被自動(dòng)刪除，而第二種方式創(chuàng)建的索引在刪除約束的時(shí)候不會(huì)自動(dòng)刪除，需要?jiǎng)h完約 束刪索引，如果忘記了這個(gè)唯一索引的話，可能會(huì)帶來(lái)跟想象不一樣的結(jié)果。同時(shí)，Oracle針對(duì)這種情況提供了特殊的刪除約束的方法，可以同時(shí)刪除約束和 索引，就是：alter table bear drop constraint pk_bear drop index。在刪除約束的最后加上刪除索引的關(guān)鍵字。
-
 

但寫這一大堆都不是這里要重點(diǎn)描述的內(nèi)容，這里要寫的是怎么知道已經(jīng)創(chuàng)建好的索引到底是第一種方式創(chuàng)建的？還是第二種方式創(chuàng)建的呢？

在Oracle的SYS.IND$視圖中有一個(gè)叫PROPERTY的字段，里面記錄的就是每個(gè)索引對(duì)應(yīng)的屬性，這個(gè)字段的含義是在創(chuàng)建這個(gè)表的SQL中有定義的。在10G的版本中，可以到$Oracle_HOME/rdbms/admin/sql.bsp中查找ind$表的創(chuàng)建腳本；在11G的版本中，可以到相同的位置的dcore.bsp中查找。這些腳本都是創(chuàng)建系統(tǒng)核心表的腳本，很多字段在官方文檔中沒(méi)有注釋的，可以來(lái)這里找找看。在11G中對(duì)PROPERTY字段的注釋如下：

property number not null, /* immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* system managed domain index : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
/* composite domain index : 0x8000 */

這個(gè)是典型的Oracle的表示方法，其中每個(gè)值表示一個(gè)含義，但是多個(gè)值是可以累加起來(lái)表示多個(gè)含義的。比如一個(gè)UNIQUE的REVERSE的索引，對(duì)應(yīng)的值就會(huì)是0X05，PROPERTY中對(duì)應(yīng)的是10進(jìn)制的存放，就應(yīng)該也是5。

可以用下面的SQL來(lái)查詢主鍵跟索引的關(guān)系是第一種還是第二種：
SELECT DECODE(BITAND(PROPERTY, 4096), 4096, 'implicit', 'user-generated') GENERATION,
B.INDEX_NAME
FROM SYS.IND$ A, USER_INDEXES B, USER_OBJECTS C
WHERE B.UNIQUENESS = 'UNIQUE'
AND A.OBJ# = C.OBJECT_ID
AND B.INDEX_NAME = C.OBJECT_NAME
AND B.INDEX_NAME = 'INDEX_NAME';
上面的SQL就是說(shuō)如果PROPERTY為4096，那么對(duì)應(yīng)到16進(jìn)制應(yīng)該是0X1000，也就表示The index was created by a constraint，也就是第一種；其他都是第二種，這種情況下典型的值就是4097，也就是0X1000和0X01值的和。

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]