1Z0-051-DDL-2簡(jiǎn)單索引的創(chuàng)建和刪除

索引具有兩個(gè)功能：一是強(qiáng)制實(shí)施主鍵約束和唯一約束，二是提高性能

但是會(huì)減低DML操作性的性能。

1.1 索引的必要性

       一、索引是約束機(jī)制的一部分，通過(guò)索引，可以立即（或近似立即）訪問(wèn)鍵值。如果定義主鍵，而主鍵上尚未建立索引，Oracle將自動(dòng)創(chuàng)建一個(gè)索引。唯一約束也需要建立索引，為了提高性能，始終應(yīng)在字表的外鍵上建立索引。索引對(duì)性能而言至關(guān)重要。

       二、可以使用索引的情況是排序。

       三、索引有助于提高性能的第三種情況是聯(lián)接查詢。

1.2 索引的類型

在OLTP系統(tǒng)中，盡量減少索引的數(shù)量，而在OLAP系統(tǒng)中（數(shù)據(jù)倉(cāng)庫(kù)），創(chuàng)建足夠的索引會(huì)起到幫助作用。

（1）B*樹索引

索引樹的葉節(jié)點(diǎn)按順序存儲(chǔ)行鍵，每個(gè)鍵有一個(gè)指針，用來(lái)確定行的物理位置。行的指針是rowid。rowid是Oracle專用的虛擬列，每個(gè)表的每一行都有此虛擬列。每一個(gè)rowid在全局上是唯一的。

Select  count(*) from employees where last_namebetween 'A%' and 'Z%';

注：如果在where 子句中指定的值是null,則會(huì)是一個(gè)例外情況。null不會(huì)參與到B*樹索引中，導(dǎo)致始終掃描全表。

Select *from employees where last_name is null;

使用B*樹的情況如下：

• 列的基數(shù)（不同值得個(gè)數(shù)）很大

• 表的行數(shù)多

• 列用在where 子句或join條件中。

（2）位圖索引

位圖索引使用在數(shù)據(jù)倉(cāng)庫(kù)中，可以從幾個(gè)維度進(jìn)行分析?？梢杂玫轿粓D索引的情況如下：

• 列的基數(shù)（不同值的個(gè)數(shù)）小

• 表中的行數(shù)多。

• 列用于布爾代數(shù)運(yùn)算。

（3）索引類型選項(xiàng)

• 唯一或非唯一  （Unique or non_unique）

• 反向鍵 (Reverse Key)

• 壓縮  (Compressed)

• 復(fù)合 (Composite)

• 基于函數(shù) (Function based)

• 升序或降序 (Ascending or     Desending)

以上6個(gè)選項(xiàng)均可用于B* 樹，只用復(fù)合，基于函數(shù)，升序或降序應(yīng)用于位圖索引。

1.3 創(chuàng)建和使用索引

索引定義在主鍵約束和唯一約束。

語(yǔ)法：

CREATE[UNIQUE | BITMAP] INDEX [schema.]indexname

ON[schema.]tablename (column[,column…]);

注：默認(rèn)創(chuàng)建的索引類型是非唯一，非壓縮，非反向鍵B*樹。

Eg.

SQL>create table dept(deptno number(8),dname varchar2(20));

SQL>createtable emp (empno number(8),ename varchar2(20),deptno number(8));

SQL>createunique index dept_i on dept(deptno);

SQL>createunique index emp_i on emp(empno);

SQL>createbitmap index emp_i2 on emp(deptno);

SQL>altertable dept add constraint dept_pk primary key(deptno);

SQL>altertable emp add constraint emp_pk primary key(empno);

SQL>altertable emp add constraint emp_fk foreign key(deptno) references dept(deptno);

1.4 修改和刪除索引

alter index 命令屬于數(shù)據(jù)庫(kù)管理范疇，在1Z0-055中進(jìn)行解釋。

SQL>drop index emp_i2;

【創(chuàng)建索引案例】

SQL>commwebstore/admin123

SQL>createindex cust_name_i on customers(customer_name,customer_status);

在一些低基數(shù)列上創(chuàng)建位圖索引

SQL>createbitmap index creditrating_i on customers(creditrating);

運(yùn)行下列查詢確定剛剛創(chuàng)建的索引的名稱及其他一些特性。

SQL>selectindex_name,column_name,index_type,uniqueness from user_indexes natural joinuser_ind_columns where table_name = 'CUSTOMERS';