當(dāng)數(shù)據(jù)庫(kù)創(chuàng)建表時(shí)，每個(gè)表只能有一個(gè)主鍵，但是如果想讓多個(gè)列都成為主鍵時(shí)，就要用到復(fù)合主鍵。

一、主鍵唯一約束

我們知道當(dāng)某列為主鍵時(shí)，Oracle會(huì)自動(dòng)將此列創(chuàng)建唯一約束。也就是說(shuō)不允許有相同的值出現(xiàn)。

如：

CREATE TABLE T

(

ID NUMBER,

NAME VARCHAR2(10),

constraint t_pk primary key (ID)

);

table T 已創(chuàng)建。

INSERT INTO T VALUES(1,'A');

1 行已插入。

insert into T VALUES(1,'B');

SQL 錯(cuò)誤: ORA-00001: 違反唯一約束條件 (TEST.T_PK)

復(fù)合主鍵創(chuàng)建的約束指的是不允許三個(gè)值都重復(fù)的數(shù)據(jù)插入

如：

CREATE TABLE T

(

ID1 NUMBER,

ID2 NUMBER,

ID3 NUMBER,

NAME VARCHAR2(10),

constraint t_pk primary key (ID1,ID2,ID3)

);

table T 已創(chuàng)建。

INSERT INTO T VALUES(1,1,1,'A');

1 行已插入。

INSERT INTO T VALUES(1,1,2,'B');

1 行已插入。

INSERT INTO T VALUES(1,2,1,'A');

1 行已插入。

INSERT INTO T VALUES(1,1,2,'B');

SQL 錯(cuò)誤: ORA-00001: 違反唯一約束條件 (TEST.T_PK)

二、主鍵索引

當(dāng)創(chuàng)建主鍵時(shí)Oracle會(huì)自動(dòng)創(chuàng)建索引。

如：

CREATE TABLE T1

(

ID NUMBER,

NAME VARCHAR2(10),

CONSTRAINT T1_PK PRIMARY KEY (ID)

);

...插入部分?jǐn)?shù)據(jù)...

SELECT * FROM T1 WHERE id = 10;

查看Oracle的解釋計(jì)劃

很明顯Oracle使用了索引來(lái)查詢(xún)。

而當(dāng)執(zhí)行一下查詢(xún)時(shí)由于沒(méi)有索引列，所以使用的是全表掃描查詢(xún)。

SELECT * FROM T1 where name = 'A';

SELECT * FROM T1 ;

當(dāng)創(chuàng)建復(fù)合索引時(shí)包含全部索引列時(shí)Oracle會(huì)以索引方式進(jìn)行查詢(xún)。

SELECT * FROM T WHERE ID1 = 2 AND ID2 = 3 AND ID3 = 1;

當(dāng)條件包含部分索引列時(shí)會(huì)發(fā)生兩種情況。我們重新創(chuàng)建一張表REPOLICYSHARE，向表內(nèi)插入500000行數(shù)據(jù)。

CREATE TABLE REPOLICYSHARE

(

POLICYNO?? VARCHAR2(22),

DANGERNO?? NUMBER(8,0),

REPOLICYNO VARCHAR2(22) NOT NULL,

STARTDATE DATE,

CLASSCODE???? VARCHAR2(4),

RISKCODE????? VARCHAR2(4),

COMCODE?????? VARCHAR2(10) NOT NULL,

REINSMODE???? VARCHAR2(3) NOT NULL,

TREATYNO????? VARCHAR2(10) NOT NULL,

TREATYSECTION VARCHAR2(12),

SHARERATE???? NUMBER(9,6),

CURRENCY????? VARCHAR2(3),

REAMOUNT????? NUMBER(14,2),

REPREMIUM???? NUMBER(14,2),

EXCHRATECNY?? NUMBER(12,8),

ACCPAYDATE DATE NOT NULL,

TREATYFLAG VARCHAR2(1) NOT NULL,

PAYDATE DATE NOT NULL,

STATDATE DATE,

CONSTRAINT PK_MID_R_REPOLICYSHARE PRIMARY KEY (REPOLICYNO,

COMCODE, TREATYNO, ACCPAYDATE, PAYDATE, TREATYFLAG,

CURRENCY)

);

由建表語(yǔ)句我們能看出此表的所因?yàn)閺?fù)合索引，并且由REPOLICYNO, COMCODE, TREATYNO, ACCPAYDATE, PAYDATE, TREATYFLAG, CURRENCY等列構(gòu)成。

當(dāng)where條件包含REPOLICYNO, COMCODE, TREATYNO列時(shí)。使用的是索引查詢(xún)。

SELECT POLICYNO

FROM REPOLICYSHARE

WHERE REPOLICYNO = 'PO0520062458001329'

AND COMCODE????? = '2458800605'

AND TREATYNO???? = 'OP2006ZL';

當(dāng)where條件包含COMCODE, TREATYNO列時(shí)。使用的是全表掃描查詢(xún)。

SELECT POLICYNO

FROM REPOLICYSHARE

WHERE COMCODE????? = '2458800605'

AND TREATYNO???? = 'OP2006ZL';

為什么同樣是部分列，但查詢(xún)形式卻不一樣呢？

這是因?yàn)閺?fù)合索引實(shí)用的條件為當(dāng)where條件中存在復(fù)合索引的第一列時(shí)，才使用索引。也就是說(shuō)第二個(gè)查詢(xún)語(yǔ)句中沒(méi)有包含REPOLICYNO所以O(shè)racle沒(méi)有使用復(fù)合索引。至于為什么必須有第一列，個(gè)人認(rèn)為由于Oracle默認(rèn)是使用B*樹(shù)索引，而B(niǎo)樹(shù)的復(fù)合索引指向的是第一列的元素索引，如果不包括第一列則無(wú)法找到索引源頭從而無(wú)法使用索引。

如上圖所示，如果不包含第一列則Oracle無(wú)法找到索引的start位置進(jìn)而無(wú)法使用索引。

三、使用什么樣的索引

我們可以從前幾個(gè)查詢(xún)語(yǔ)句看出索引對(duì)于查詢(xún)速度的重要性。

所以要想使

SELECT POLICYNO

FROM REPOLICYSHARE

WHERE COMCODE????? = '2458800605'

AND TREATYNO???? = 'OP2006ZL';

語(yǔ)句查詢(xún)更快，就要建立索引。

但是問(wèn)題也出現(xiàn)了，是創(chuàng)建復(fù)合索引查詢(xún)更快，還是還是單獨(dú)建立索引查詢(xún)更快，還是一樣快呢？

首先我們?yōu)槊總€(gè)列創(chuàng)建索引

CREATE INDEX INX_REPOLICYSHARE_COMCODE ON REPOLICYSHARE(COMCODE);

CREATE INDEX INX_REPOLICYSHARE_TREATYNO ON REPOLICYSHARE(TREATYNO);

然后執(zhí)行SQL語(yǔ)句查看執(zhí)行時(shí)間，并查看Oracle的解釋計(jì)劃。

提取50行的運(yùn)行時(shí)間為：0.452秒

解釋計(jì)劃：

刪除剛剛建立的索引并創(chuàng)建復(fù)合索引。

DROP INDEX INX_REPOLICYSHARE_COMCODE;

DROP INDEX INX_REPOLICYSHARE_TREATYNO;

CREATE INDEX INX_REPOLICYSHARE ON REPOLICYSHARE(COMCODE,TREATYNO);

然后執(zhí)行SQL語(yǔ)句查看執(zhí)行時(shí)間，并查看Oracle的解釋計(jì)劃。

提取50行的運(yùn)行時(shí)間為：0.067秒

解釋計(jì)劃：

結(jié)果很明顯，復(fù)合索引要快于單獨(dú)建立索引。

總結(jié)

以上是生活随笔為你收集整理的oracle联合主键 索引,关于复合主键查询时使用索引研究的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。