关于复合主键查询时使用索引研究

当数据库创建表时，每个表只能有一个主键，但是如果想让多个列都成为主键时，就要用到复合主键。一、主键唯一约束我们知道当某列为主键时，Oracle会自动将此列创

当数据库创建表时，每个表只能有一个主键，但是如果想让多个列都成为主键时，就要用到复合主键。

一、主键唯一约束

我们知道当某列为主键时，Oracle会自动将此列创建唯一约束。也就是说不允许有相同的值出现。

如：

CREATE TABLE T

(

ID NUMBER,

NAME VARCHAR2(10),

constraint t_pk primary key (ID)

);  

table T 已创建。

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

1 行已插入。

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

SQL 错误: ORA-00001: 违反唯一约束条件 (TEST.T_PK)

 

复合主键创建的约束指的是不允许三个值都重复的数据插入

如：

CREATE TABLE T

(

ID1 NUMBER,

ID2 NUMBER,

ID3 NUMBER,

NAME VARCHAR2(10),

constraint t_pk primary key (ID1,ID2,ID3)

);

table T 已创建。

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 错误: ORA-00001: 违反唯一约束条件 (TEST.T_PK)

 二、主键索引

当创建主键时Oracle会自动创建索引。

如：

CREATE TABLE T1

(

ID NUMBER,

NAME VARCHAR2(10),

CONSTRAINT T1_PK PRIMARY KEY (ID)

);

...插入部分数据...

SELECT * FROM T1 WHERE id = 10;

查看Oracle的解释计划

很明显Oracle使用了索引来查询。

而当执行一下查询时由于没有索引列，所以使用的是全表扫描查询。

SELECT * FROM T1 where name = 'A';

SELECT * FROM T1 ;

当创建复合索引时包含全部索引列时Oracle会以索引方式进行查询。

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

当条件包含部分索引列时会发生两种情况。我们重新创建一张表REPOLICYSHARE，向表内插入500000行数据。

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)

        );

由建表语句我们能看出此表的所因为复合索引，并且由REPOLICYNO, COMCODE, TREATYNO, ACCPAYDATE, PAYDATE, TREATYFLAG, CURRENCY等列构成。

当where条件包含REPOLICYNO, COMCODE, TREATYNO列时。使用的是索引查询。

SELECT POLICYNO

FROM REPOLICYSHARE

WHERE REPOLICYNO = 'PO0520062458001329'

AND COMCODE      = '2458800605'

AND TREATYNO     = 'OP2006ZL';

当where条件包含COMCODE, TREATYNO列时。使用的是全表扫描查询。

SELECT POLICYNO

FROM REPOLICYSHARE

WHERE COMCODE      = '2458800605'

AND TREATYNO     = 'OP2006ZL';

为什么同样是部分列，但查询形式却不一样呢？