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

Oracle的約束

如果某個約束只作用于單獨的字段，即可以在字段級定義約束，也可以在表級定義約束，但如果某個約束作用于多個字段，

 

必須在表級定義約束

 

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

 

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

 

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

 

指定約束名

 

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

 

定義primary key約束(多個字段,在表級定義約束）

 

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自動會為具有PRIMARY KEY約束的字段(主碼字段)建立一個唯一索引和一個NOT NULL約束,定義PRIMARY KEY約束時可以為它的索引

 

指定存儲位置和存儲參數(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約束(只能在字段級定義NOT NULL約束,在同一個表中可以定義多個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約束的字段中不能包含重復值，可以為一個或多個字段定義UNIQUE約束,因此，UNIQUE即可以在字段級也可以在表級定義，

 

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

 

foreign key約束

 

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

 

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

 

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

 

對同一個字段可以同時定義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后面沒有加逗號,因為這一句的contraint是跟在那一列deptno后面的，屬于列定義，所以都無需指明列。而A例中的是表定義，需要指明那一列，所以要加逗號，不能在列后面定義，還可以寫成：

 

create table employees

 

(empno char(4),

 

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

 

ename varchar2(10)

 

)

 

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

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約束(多字段／表級）

 

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),默認是delete on action

 

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

 

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約束的表達式中必須引用到表中的一個或多個字段，并且表達式的計算結(jié)果必須是一個布爾值

 

可以在表級或字段級定義

 

對同一個字段可以定義多個CHECK約束，同時也可以定義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 --指定約束名

 

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

 

alter table employees drop primary key keep index

 

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

 

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

 

alter table dept drop primary key cascade

 

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

 

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約束，

 

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

 

語句中指定CASCADE關(guān)鍵字，這樣將在禁用UNIQUE或PRIMARY KEY約束的同時禁用那些引用它們的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 約束對應(yīng)的字段信息

 

Oracle的索引

 

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

 

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

 

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

 

Oracle最多允許包含32個字段的復合索引

 

索引創(chuàng)建策略

 

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

 

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

 

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

 

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

 

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

 

6.存儲索引的表空間最好單獨設(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;

 

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

 

alter index emp_ename storage(pctincrease 50);

 

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

 

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

 

alter table employees enable primary key using index pctfree 5;

 

清理索引碎片

 

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

 

alter index emp_pk coalesce;

 

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

 

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

 

alter index emp_pk rebuild;

 

刪除索引

 

drop index emp_ename;

 

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

 

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

 

在刪除一個表時,oracle會自動刪除所有與該表相關(guān)的索引.

 

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

 

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

 

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

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