oracle學習篇十二：索引

標籤：

索引：

查詢User_indexes可以擷取有關使用者已建立的索引的詳細資料。
查詢User_ind_partitions可以擷取有關使用者已建立的分區索引的詳細資料。
查詢User_ind_columns可以擷取有關列(使用者的索引是基於這些列建立的)的詳細資料。

索引類型：唯一索引、複合式索引、反向鍵索引、位元影像索引和基於函數的索引。

索引在邏輯上物理上都獨立於關聯表中的資料，在任何時候都可以建立或刪除索引，而不會影響基表或其它索引。

1.建立普通索引的文法：
Create INDEX index_name ON table_name(column_list) [TABLESPACE tablespace_name];

其中：
index_name 指所建立索引的名稱。
table_name 表示為之建立索引的表名。
column_list 是在其上建立索引的列名列表，可以基於多列建立索引。
tablespace_name 為索引指定資料表空間。

樣本12：示範如何在itemfile表的itemcode列上建立索引
create index item_index on itemfile(itemcode);

2.ALTER INDEX語句的REBUILD選項可以用來重建現有的索引。該選項提供的效能要優於使用DROP INDEX和CREATE INDEX語句重新建立索引。

樣本13：重建索引
ALTER INDEX item_index REBUILD;

刪除索引文法：Drop INDEX item_index;

(1)唯一索引：此索引可以確保在定義索引的列中，表的任意兩行的值都不相同。Oracle自動為表的主鍵列建立唯一索引。
可以使用Create UNIQUE INDEX命令明確地建立唯一索引。

樣本14：在itemfile表的itemcode列上建立了一個名為item_index的唯一索引。
Create UNIQUE INDEX item_index ON itemfile(itemcode);

(2)複合式索引：複合式索引是在表中的多個列上建立的索引。複合式索引中列的順序是任意的，不必是表中相鄰的列。
建立複合式索引時，應注意定義中使用的列的順序。通常，最頻繁訪問的列應放置在列表的最前面。

樣本15：在itemfile表上建立了一個名為comp_index的複合式索引，當查詢該表的的WHERE子句同時包含這兩個列或只包含
p_category列時，以下樣本語句建立的索引將用於檢索資料，但如果單獨使用itemrate列，則索引不能用於檢索資料。
Create INDEX comp_index ON itemfile(p_category,itemrate);

(3)反向鍵索引：通常建立在一些值連續增長的列上，例如列中的值是是由序列產生的情況。
樣本16：在itemfile表上建立了一個名為rev_index的反向鍵索引。注意使用REVERSE關鍵字。
Create INDEX rev_index ON itemfile(itemcode) REVERSE;

樣本17：使用關鍵字NOREVERSE可以將反向鍵索引重建為標準索引。
ALTER INDEX rev_index REBUILD NOREVERSE;
注意：不能將標準索引重建為反向鍵索引。

(4)位元影像索引：如果某個列的值重複超過一百次，則可以考慮在該列上建立位元影像索引。
樣本18：itemcode是order_detail表中的低基數列，因為貨物編碼在大多數訂單中都是重複的，因此適合在該列上建立位元影像索引。
Create BITMAP INDEX bit_ind1 ON order_detail(itemcode);
位元影像索引不應當用在頻繁發生的INSERT,UPDATE,DELETE操作的表上。位元影像索引最適合於資料倉儲和決策支援系統。

(5)索引組織表：索引組織表與在一個或多個列上建立索引的普通表相似，但它無需為表和索引維護兩個單獨的儲存空間，
資料庫系統僅維護一個索引，該索引包含相應的已編碼索引值和與其關聯的列值。
樣本19：使用ORGANIZATION INDEX子句來建立索引組織表。
Create table ind_org_tab
(
vencode NUMBER(4) primary key, 注意：primary key是建立索引組織表所必需的。不允許使用分區。
venname VARCHAR2(20)
)
organization index;
索引組織表適合於通過主鍵來訪問資料。

(6)基於函數的索引：如果在WHERE子句的算術運算式或函數中已經包含了某個列，則不會使用該列上的索引。不能在運算式包含任何
彙總函式，LOB列、REF列或包含LOB或REF的物件類型上建立基於函數的索引。

樣本20：venname是vendor_master表的一個列，用於儲存供應商的姓名，假定所有供應商的姓名都以混合大小寫形式儲存
(如：John Smith、Dave Jones、Tony Greig等等)，同時假定我們經常需要根據供應商的姓名來查詢表的資料。由於
姓名是以混合大小寫形式儲存的，因此可能很難給出姓名的正確大小寫形式。
可以建立如下索引：
Create INDEX vn_ind ON vendor_master(UPPER(venname));

樣本21：示範如何使用前面建立的基於函數的索引檢索資料。
select * from vendor_master where UPPER(venname)=‘SMALL‘;

要建立基於函數或運算式的索引，必須具有QUERY REWRITE系統許可權。

(7)索引中的分區：與對錶進行分區類似，Oracle也允許對索引分割區。牽引分區可以儲存在不同的資料表空間中。
局部分區索引：Oracle為表的每個分區建立一個獨立的索引。

樣本22：先建立分區表
Create table order_mast
(
orderno number(4),
venname varchar2(20)
)
partition by range(orderno)
(
partition oe1 values less than(1000),
partition oe2 values less than(2000),
partition oe3 values less than(maxvalue)
);

接著建立局部索引：
create INDEX myind ON order_mast(orderno) LOCAL;

全域分區索引：是指在分區表或非分區表上建立的索引。

樣本23：在上面建立的分區表上建立全域索引
create INDEX glb_ind ON order_mast(orderno) GLOBAL
partition by range(orderno)
(
partition ip1 values less than(1500),
partition ip2 values less than(maxvalue)
);

在有3個分區的表上建立2個分區的索引。注意：不能在散列分區或子分區建立全域索引。

全域非分區索引：全域分區索引是在分區表上建立的全域索引，它類似於非分區表上的索引，索引的結構不會被分割。

oracle學習篇十二：索引