Oracle 基礎--索引

今天，我在完成 資料庫恢復後，溫習了一下索引基礎，感覺真是曾經滄海難為水，單身依舊笑春風。

1: 如果某個約束只作用於單獨的欄位，即可以在欄位級定義約束，也可以在表級定義約束，但如果某個約束作用於多個欄位， 必須在表級定義約束 
2:  在定義約束時可以通過CONSTRAINT關鍵字為約束命名，如果沒有指定，ORACLE將自動為約束建立預設的名稱

定義primary key約束(單個欄位）
create table employees (empno number(5) primary key,...)

指定約束名
create table employees (empno number(5) constraint emp_pk primary key,...)

SQL> edit
Wrote file afiedt.buf

  1  create table employees(
  2  empno number(5),
  3  ename varchar2(15),
  4  phone varchar2(15),
  5  email varchar2(30) unique,
  6  deptno number(3) not null
  7* )
  8  /

Table created.

再次定義同一個欄位not null時：

SQL> alter table employees modify deptno not null;
alter table employees modify deptno not null
                             *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

SQL> edit
Wrote file afiedt.buf

  1* alter table employees add constraint emp_uk unique(ename,phone)
SQL> /

Table altered.

定義了UNIQUE約束的欄位中不能包含重複值，可以為一個或多個欄位定義UNIQUE約束,因此，UNIQUE即可以在欄位級也可以在表級定義， 在UNIQUED約束的欄位上可以包含空值.

foreign key約束

* 定義為FOREIGN KEY約束的欄位中只能包含相應的其它表中的引用碼欄位的值或者NULL值
* 可以為一個或者多個欄位的組合定義FOREIGN KEY約束
* 定義了FOREIGN KEY約束的外部碼欄位和相應的引用碼欄位可以存在於同一個表中，這種情況稱為"自引用"
* 對同一個欄位可以同時定義FOREIGN KEY約束和NOT NULL約束
定義了FOREIGN KEY約束的欄位稱為"外部碼欄位",被FORGIEN KEY約束引用的欄位稱為"引用碼欄位",引用碼必須是主碼或唯一碼,包含外部碼的表稱為子表，
包含引用碼的表稱為父表.

SQL> alter table dept add constraint dpt_pk primary key(deptno);

Table altered.

SQL>  alter table employees add constraint emp_deptno_fk foreign key(deptno) references dept(d
eptno);

Table altered.
SQL> edit
Wrote file afiedt.buf

  1* create table jobs(deptno number(3) not null,jobid number(3) not null, constraint job_pk p
rimary key(jobid,deptno))
SQL> /

SQL> edit
Wrote file afiedt.buf

  1* alter table employees add jobid number(3) not null
SQL> /

SQL> alter table employees add constraint emp_job_fk foreign key(jobid,deptno) references
  2  jobs(jobid,deptno) on delete cascade;

Table altered.

更改foreign key約束定義的引用行為(delete cascade/delete set null/delete no action),預設是delete on action

引用行為(當主表中一條記錄被刪除時，確定如何處理字表中的外部碼欄位）：
delete cascade : 刪除子表中所有的相關記錄
delete set null :　將所有相關記錄的外部碼欄位值設定為NULL
delete no action: 不做任何操作

先刪除原來的約束,再新增約束

SQL> alter table employees drop constraint emp_uk ;

Table altered.

SQL> alter table employees add constraint emp_uk unique(ename,phone);

Table altered.

check約束
* 在CHECK約束的表示式中必須引用到表中的一個或多個欄位，並且表示式的計算結果必須是一個布林值
* 可以在表級或欄位級定義
* 對同一個欄位可以定義多個CHECK約束，同時也可以定義NOT NULL約束
SQL> alter table employees add sal number(7,2) constraint emp_sal_ch1 check(sal>0);

Table altered.
刪除約束時，預設將同時刪除約束所對應的索引，如果要保留索引，用KEEP INDEX關鍵字

SQL>  alter table dept drop primary key  keep index;
 alter table dept drop primary key  keep index
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

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

SQL> alter table dept drop primary key cascade；

Table altered.

刪除約束，除了指定約束名，也可以指定約束的定義內容

SQL> alter table employees drop unique(ename,phone);
Table altered.

禁用/啟用約束(禁用/啟用約束會引起刪除和重建索引的操作)
SQL> edit
Wrote file afiedt.buf

  1* alter table employees disable constraint emp_sal_ch1
SQL> /

Table altered.

SQL> alter table employees modify constraint emp_sal_ch1 enable;

Table altered.

如果有FOREIGN KEY約束正在引用UNIQUE或PRIMARY KEY約束，則無法禁用這些UNIQUE或PRIMARY KEY約束，
這時可以先禁用FOREIGN KEY約束，然後再禁用UNIQUE或PRIMARY KEY約束；或者可以在ALTER TABLE...DISABLE
語句中指定CASCADE關鍵字，這樣將在禁用UNIQUE或PRIMARY KEY約束的同時禁用那些引用它們的FOREIGN KEY約束

SQL> alter table employees disable primary key cascade;

Table altered.

all_constraints/dba_constraints/user_constraints 約束的基本資訊，包括約束的名稱，型別，狀態
(約束型別：C(CHECK約束),P(主碼約束),R(外部碼約束),U(唯一碼約束))

Oracle 的索引
    索引和對應的表應該位於不同的表空間中,oracle能夠並行讀取位於不同硬碟上的資料，可以避免產生I/O衝突
B樹索引：在B樹的葉節點中儲存索引欄位的值與ROWID。
唯一索引和不唯一索引都只是針對B樹索引而言.
Oracle最多允許包含32個欄位的複合索引

索引建立策略
1.匯入資料後再建立索引
2.不需要為很小的表建立索引
3.對於取值範圍很小的欄位（比如性別欄位）應當建立點陣圖索引 .（基數很小的欄位）
4.限制表中的索引的數目
5.為索引設定合適的PCTFREE值
6.儲存索引的表空間最好單獨設定

建立唯一索引,去掉unique 就建立的是不唯一索引。

SQL> alter table employees drop unique(email);

Table altered.

SQL>  create unique index emp_email on employees(email) tablespace rman;

Index created.

建立點陣圖索引

SQL> alter table employees add sex varchar2(2);

Table altered.

SQL> create bitmap index emp_sex on employees(sex) tablespace rman;

Index created.

建立反序索引

SQL> create unique index emp_reinx on employees(empno,jobid) tablespace rman reverse;

Index created.

建立函式索引(函式索引即可以是普通的B樹索引，也可以是點陣圖索引)

SQL> create index emp_substr_empno on employees(substr(empno,1,2)) tablespace rman;

Index created.

由於定義約束時由oracle自動建立的索引通常是不知道名稱的，對這類索引的修改經常是利用alter table ..using index語句進行的,而不是alter index語句,利用employees表中primary key約束對應的索引的PCTFREE引數修改為5
SQL> alter table employees enable primary key using index pctfree 5;

Table altered.
清理索引碎片
1.合併索引(只是簡單的將B樹葉結點中的儲存碎片合併在一起，並不會改變索引的物理組織結構）
SQL> alter index emp_ename coalesce;

Index altered.

刪除索引
SQL> drop index emp_ename;

Index dropped.

如果索引中包含損壞的資料塊，或者包含過多的儲存碎片，需要首先刪除這個索引，然後再重建它.
如果索引是在建立約束時由oracle自動產生的,可以通過禁用約束或刪除約束的方法來刪除對應的索引.
在刪除一個表時,oracle會自動刪除所有與該表相關的索引.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/701141/viewspace-886/，如需轉載，請註明出處，否則將追究法律責任。