Oracle數(shù)據(jù)庫里的索引是非常重要的，索引的存在簡(jiǎn)化了Oracle數(shù)據(jù)庫的操作過程，下文接下來就為大家詳細(xì)解析Oracle數(shù)據(jù)庫的索引和約束，希望對(duì)大家能夠有所幫助。

Oracle的約束

如果某個(gè)約束只作用于單獨(dú)的字段，即可以在字段級(jí)定義約束，也可以在表級(jí)定義約束，但如果某個(gè)約束作用于多個(gè)字段，

 

必須在表級(jí)定義約束

 

在定義約束時(shí)可以通過CONSTRAINT關(guān)鍵字為約束命名，如果沒有指定，ORACLE將自動(dòng)為約束建立默認(rèn)的名稱

 

定義primary key約束(單個(gè)字段）

 

create table employees (empno number(5) primary key,...)

 

指定約束名

 

create table employees (empno number(5) constraint emp_pk primary key,...)

 

定義primary key約束(多個(gè)字段,在表級(jí)定義約束）

 

create table employees

 

(empno number(5),

 

deptno number(3) not null,

 

constraint emp_pk primary key(empno,deptno)

 

using index tablespace indx

 

storage (initial 64K

 

next 64K

 

)

 

)

 

ORACLE自動(dòng)會(huì)為具有PRIMARY KEY約束的字段(主碼字段)建立一個(gè)唯一索引和一個(gè)NOT NULL約束,定義PRIMARY KEY約束時(shí)可以為它的索引

 

指定存儲(chǔ)位置和存儲(chǔ)參數(shù)

alter table employees add primary key (empno)

 

alter table employees add constraint emp_pk primary key (empno)

 

alter table employees add constraint emp_pk primary key (empno,deptno)

 

not null約束(只能在字段級(jí)定義NOT NULL約束,在同一個(gè)表中可以定義多個(gè)NOT NULL約束)

 

alter table employees modify deptno not null/null

 

unique約束

 

create table employees

 

( empno number(5),

 

ename varchar2(15),

 

phone varchar2(15),

 

email varchar2(30) unique,

 

deptno number(3) not null,

 

constraint emp_ename_phone_uk unique (ename,phone)

 

)

 

alter table employees

 

add constraint emp_uk unique(ename,phone)

 

using index tablespace indx

 

定義了UNIQUE約束的字段中不能包含重復(fù)值，可以為一個(gè)或多個(gè)字段定義UNIQUE約束,因此，UNIQUE即可以在字段級(jí)也可以在表級(jí)定義，

 

在UNIQUED約束的字段上可以包含空值.

 

foreign key約束

 

定義為FOREIGN KEY約束的字段中只能包含相應(yīng)的其它表中的引用碼字段的值或者NULL值

 

可以為一個(gè)或者多個(gè)字段的組合定義FOREIGN KEY約束

 

定義了FOREIGN KEY約束的外部碼字段和相應(yīng)的引用碼字段可以存在于同一個(gè)表中，這種情況稱為"自引用"

 

對(duì)同一個(gè)字段可以同時(shí)定義FOREIGN KEY約束和NOT NULL約束

 

定義了FOREIGN KEY約束的字段稱為"外部碼字段",被FORGIEN KEY約束引用的字段稱為"引用碼字段",引用碼必須是主碼或唯一碼,包含外部碼的表稱為子表，

 

包含引用碼的表稱為父表.

 

A:

 

create table employees

 

(.....,

 

deptno number(3) NOT NULL,

 

constraint emp_deptno_fk foreign key (deptno)

 

references dept (deptno)

 

)

 

如果子表中的外部碼與主表中的引用碼具有相同的名稱，可以寫成:

 

B:

 

create table employees

 

(.....,

 

deptno number(3) NOT NULL

 

constraint emp_deptno_fk references dept

 

)

 

注意：

 

上面的例子(B)中not null后面沒有加逗號(hào),因?yàn)檫@一句的contraint是跟在那一列deptno后面的，屬于列定義，所以都無需指明列。而A例中的是表定義，需要指明那一列，所以要加逗號(hào)，不能在列后面定義，還可以寫成：

 

create table employees

 

(empno char(4),

 

deptno char(2) not null constraint emp_deptno_fk references dept,

 

ename varchar2(10)

 

)

 

表定義contraint的只能寫在最后，再看兩個(gè)例子：

create table employees

 

(empno number(5),

 

ename varchar2(10),

 

deptno char(2) not null constraint emp_deptno_fk references dept,

 

constraint emp_pk primary key(empno,ename)

 

)

 

create table employees

 

( empno number(5),

 

ename varchar2(15),

 

phone varchar2(15),

 

email varchar2(30) unique,

 

deptno number(3) not null,

 

constraint emp_pk primary key(empno,ename),

 

constraint emp_phone_uk unique (phone)

 

)

 

添加foreign key約束(多字段／表級(jí)）

 

alter table employees

 

add constraint emp_jobs_fk foreign key (job,deptno)

 

references jobs (jobid,deptno)

 

on delete cascade

 

更改foreign key約束定義的引用行為(delete cascade/delete set null/delete no action),默認(rèn)是delete on action

 

引用行為(當(dāng)主表中一條記錄被刪除時(shí)，確定如何處理字表中的外部碼字段）：

 

delete cascade : 刪除子表中所有的相關(guān)記錄

 

delete set null :　將所有相關(guān)記錄的外部碼字段值設(shè)置為NULL

 

delete no action: 不做任何操作

 

先刪除原來的外鍵約束,再添加約束

 

ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;

 

ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

 

check約束

 

在CHECK約束的表達(dá)式中必須引用到表中的一個(gè)或多個(gè)字段，并且表達(dá)式的計(jì)算結(jié)果必須是一個(gè)布爾值

 

可以在表級(jí)或字段級(jí)定義

 

對(duì)同一個(gè)字段可以定義多個(gè)CHECK約束，同時(shí)也可以定義NOT NULL約束

 

create table employees

 

(sal number(7,2)

 

constraint emp_sal_ck1 check (sal > 0)

 

)

 

alter table employees

 

add constraint emp_sal_ck2 check (sal < 20000)

 

刪除約束

 

alter table dept drop unique (dname,loc) --指定約束的定義內(nèi)容

 

alter table dept drop constraint dept_dname_loc_uk --指定約束名

 

刪除約束時(shí)，默認(rèn)將同時(shí)刪除約束所對(duì)應(yīng)的索引，如果要保留索引，用KEEP INDEX關(guān)鍵字

 

alter table employees drop primary key keep index

 

如果要?jiǎng)h除的約束正在被其它約束引用，通過ALTER TABLE..DROP語句中指定CASCADE關(guān)鍵字能夠同時(shí)刪除引用它的約束

 

利用下面的語句在刪除DEPT表中的PRIMARY KEY約束時(shí)，同時(shí)將刪除其它表中引用這個(gè)約束的FOREIGN KEY約束:

 

alter table dept drop primary key cascade

 

禁用/激活約束(禁用/激活約束會(huì)引起刪除和重建索引的操作)

 

alter table employees disable/enable unique email

 

alter table employees disable/enable constraint emp_ename_pk

 

alter tabel employees modify constraint emp_pk disable/enable

 

alter tabel employees modify constraint emp_ename_phone_uk disable/enable

 

如果有FOREIGN KEY約束正在引用UNIQUE或PRIMARY KEY約束，則無法禁用這些UNIQUE或PRIMARY KEY約束，

 

這時(shí)可以先禁用FOREIGN KEY約束，然后再禁用UNIQUE或PRIMARY KEY約束；或者可以在ALTER TABLE...DISABLE

 

語句中指定CASCADE關(guān)鍵字，這樣將在禁用UNIQUE或PRIMARY KEY約束的同時(shí)禁用那些引用它們的FOREIGN KEY約束，如：

 

alter table employees disable primary key cascade

 

約束數(shù)據(jù)字典

 

all_constraints/dba_constraints/user_constraints 約束的基本信息，包括約束的名稱，類型，狀態(tài)

 

(約束類型：C(CHECK約束),P(主碼約束),R(外部碼約束),U(唯一碼約束))

 

all_cons_columns/dba/user 約束對(duì)應(yīng)的字段信息

 

Oracle的索引

 

索引和對(duì)應(yīng)的表應(yīng)該位于不同的表空間中,oracle能夠并行讀取位于不同硬盤上的數(shù)據(jù)，可以避免產(chǎn)生I/O沖突

 

B樹索引：在B樹的葉節(jié)點(diǎn)中存儲(chǔ)索引字段的值與ROWID。

 

唯一索引和不唯一索引都只是針對(duì)B樹索引而言.

 

Oracle最多允許包含32個(gè)字段的復(fù)合索引

 

索引創(chuàng)建策略

 

1.導(dǎo)入數(shù)據(jù)后再創(chuàng)建索引

 

2.不需要為很小的表創(chuàng)建索引

 

3.對(duì)于取值范圍很小的字段（比如性別字段）應(yīng)當(dāng)建立位圖索引

 

4.限制表中的索引的數(shù)目

 

5.為索引設(shè)置合適的PCTFREE值

 

6.存儲(chǔ)索引的表空間最好單獨(dú)設(shè)定

 

創(chuàng)建不唯一索引

 

create index emp_ename on employees(ename)

 

tablespace users

 

storage(......)

 

pctfree 0;

 

創(chuàng)建唯一索引

 

create unique index emp_email on employees(email)

 

tablespace users;

 

創(chuàng)建位圖索引

 

create bitmap index emp_sex on employees(sex)

 

tablespace users;

 

創(chuàng)建反序索引

 

create unique index order_reinx on orders(order_num,order_date)

 

tablespace users

 

reverse;

 

創(chuàng)建函數(shù)索引(函數(shù)索引即可以是普通的B樹索引，也可以是位圖索引)

 

create index emp_substr_empno

 

on employees(substr(empno,1,2))

 

tablespace users;

 

修改索引存儲(chǔ)參數(shù)(與表類似，INITIAL和MINEXTENTS參數(shù)在索引建立以后不能再改變)

 

alter index emp_ename storage(pctincrease 50);

 

由于定義約束時(shí)由oracle自動(dòng)建立的索引通常是不知道名稱的，對(duì)這類索引的修改經(jīng)常是利用alter table ..using index語句進(jìn)行的,而不是alter index語句

 

利用下面的語句將employees表中primary key約束對(duì)應(yīng)的索引的PCTFREE參數(shù)修改為5

 

alter table employees enable primary key using index pctfree 5;

 

清理索引碎片

 

1.合并索引(只是簡(jiǎn)單的將B樹葉結(jié)點(diǎn)中的存儲(chǔ)碎片合并在一起，并不會(huì)改變索引的物理組織結(jié)構(gòu)）

 

alter index emp_pk coalesce;

 

2.重建索引(不僅能夠消除存儲(chǔ)碎片,還可以改變索引的全部存儲(chǔ)參數(shù)設(shè)置，并且可以將索引移動(dòng)到其它的表空間中,重建索引

 

實(shí)際上就是再指定的表空間中重新建立一個(gè)新的索引,然后刪除原來的索引)

 

alter index emp_pk rebuild;

 

刪除索引

 

drop index emp_ename;

 

如果索引中包含損壞的數(shù)據(jù)塊，或者包含過多的存儲(chǔ)碎片，需要首先刪除這個(gè)索引，然后再重建它.

 

如果索引是在創(chuàng)建約束時(shí)由oracle自動(dòng)產(chǎn)生的,可以通過禁用約束或刪除約束的方法來刪除對(duì)應(yīng)的索引.

 

在刪除一個(gè)表時(shí),oracle會(huì)自動(dòng)刪除所有與該表相關(guān)的索引.

 

索引數(shù)據(jù)字典

 

all_indexes/dba_indexes/user_indexes 索引的基本信息

 

all_ind_columns/dba_ind_columns/user_ind_columns 索引對(duì)應(yīng)的字段信息

關(guān)于Oracle數(shù)據(jù)庫的索引和約束的知識(shí)就為大家講解這么多，希望大家都能夠從上文中涉及到的內(nèi)容中有所收獲。