18.管理索引(筆記)

18.管理索引

索引是用於加速資料存取的資料庫物件,透過使用索引,可以降低I/O次數,提高資料訪問效能.索引有多種分類方法,按照索引資料的存取方式,可以分為B*-索引,反向索引和點陣圖索引.

按照索引列的個數,可以分為單列索引和複合索引;

按照索引列值的唯一性,可以分為唯一索引和非唯一索引.

1,單列索引和複合索引

單列索引是基於單個列所建立的索引;複合索引是基於兩列或多列所建立的索引.注意,B*-樹索引的個數不能超過32列,點陣圖索引列的個數不能超過30列.可以在同一張表上建立多個索引,但要求列的組合必須不同.

CREATE INDEX emp_idx1 ON emp (ename,job);

2,唯一索引和非唯一索引

唯一索引是索引列值不能重複的索引;非唯一索引是索引列值可以重複的索引.無論是唯一索引還是非唯一索引,索引列都允許NULL,注意,當定義主鍵約束或唯一約束時,oracle會自動在相應的約束上建立唯一索引.

3索引管理的指導方針.

1. 裝載資料後重建索引

2. 索引正確的表和列,建立和規劃索引時,必須選擇合適的表和列.如果選擇的表和列不合適,不僅無法提供查詢速度,反而會極大地降低DML操作的速度.建立索引的指導方針如下:

索引應該建立在WHERE 子句經常引用的表列上.

為了提供多表連線的效能,應該在連線列上建立索引

不用在小表上建立索引

如果經常需要基於某列或某幾列執行排序操作,那麼透過在這些列上建立索引,可以加快資料排序的速度.

3. 限制索引的個數.索引主要用於加速查詢速度,但會降低DML操作的速度.索引越多,DML操作的速度越慢,尤其會極大的影響INSERT操作和DELETE操作的速度.因此,規劃索引時,必須仔細權衡查詢和DML的需求.

4. 刪除不再需要的索引

5. 指定索引塊空間使用引數

基於表建立索引時,oracle會將相應表列資料新增到索引塊.當為索引塊新增資料時,oracle會在索引塊上預留部分空間(按照PCTFREE確定),該預留空間是為將來的INSERT操作準備的,如果將來會在表上執行大量INSERT操作,那麼應該在建立索引時設定較大的PCTFREE.注意,建立索引時不能指定PCTUSED關鍵字.

6. 指定索引所在的表空間

7. 考慮並行建立索引

使用並行建立索引,多個伺服器程式會共同建立索引,這樣可以加快索引的建立速度.但可能會佔用更多的磁碟空間

8. 使用NOLOGGING選項建立索引

當在大表上建立索引時,使用NOLOGGING選項可以最小話重做記錄.使用NOLOGGING選項有以下優點.

節省重做日誌空間

降低索引建立實際

提高索引並行建立的效能.

建立B*-樹索引

B*-樹索引是以B*-樹結構組織並存放索引資料的,它是最常用的索引型別,預設情況下索引資料是以升序方式排列的.如果在WHERE子句中經常要引用某列或某幾列,應該基於這些列建立B*-索引.

建立點陣圖索引

點陣圖索引以位值標識索引行資料,它主要用於在DDS(Decision Support System)系統中執行資料統計,資料彙總等操作.

B*-書索引建立在重複值很少的列上,而點陣圖索引建立在重複值很多,不同值相對固定的列上.在不同值相對固定的列上,使用點陣圖索引可以節省大量磁碟空間,其佔用空間僅僅是在相同列上建立B*-索引空間的1/20~1/10.

建立點陣圖索引

CREATE BITMAP INDEX idx_gender

ON customers(cust_gender);

初始化引數CREATE_BITMAP_AREA_SIZE用於指定建立點陣圖索引時分配的點陣圖區尺寸,預設為8M,改引數值越大,建立點陣圖索引的速度越快.為了加快點陣圖索引建立速度,應將改引數這隻為更大值.該引數是靜態引數,修改後必須重新啟動資料庫才可以生效.

ALTER SYSTEM SET create_bitmap_area_size=8388608 SCOPE=SPFILE;

方向索引是索引列值按照相反順序存放的索引.在順序遞增的列(如ID列)上建立普通B*-樹索引是,如果表的資料量非常大,將導致索引資料分別不均(偏想某個方向),為了避免出現這種情況,應在順序遞增的列上建立反向索引.需要注意,普通B*-索引既適用於範圍查詢,也適用於等值查詢,而反向索引只適用於等值查詢.

CREATE INDEX customers_pk ON customers(cust_id) REVERSE;

建立函式索引

函式索引是基於函式或表示式建立的索引.如果經常在where子句中引用函式或表示式,那麼應該基於函式或表示式監視函式索引,需要注意,建立函式索引要求使用者必須具有QUERY REWRITE系統許可權

CREATE INDEX idx_fname ON customers(lower(cust_first_name));

修改索引

1,修改索引段儲存引數.

建立索引時,oracle會為索引分配相應的索引段,如果索引段儲存引數不合適,可以使用ALTER INDEX命令修改其儲存引數,需要注意,儲存引數INITIAL,MINEXTENTS是不能修改的.而修改其他儲存竄數只對新分配的區起作用.

ALTER INDEX upper_ename STORAGE(NEXT 200K MAXEXTENTS 50);

2,分配和釋放索引空間

ALTER INDEX department_dname ALLOCATE EXTENT(SIZE 1M);

釋放多餘空間

ALTER INDEX department_dname DEALLOCATE UNUSED;

3,重建索引.

ALTER INDEX department_dname REBUILD;

4,聯機重建索引

ALTER INDEX department_dname REBUILD ONLINE;

5合併索引

ALTER INDEX department_dname COALESCE;

刪除索引

DROP INDEX department_dname;

顯示索引資訊

1,顯示錶的所有索引

DBA_INDEXES顯示資料庫的所有索引

ALL_INDEXES顯示資料庫當前使用者可以訪問的所有索引

USER_INDEXES顯示資料庫當前使用者的所有索引

2,顯示索引列

DBA_IND_COLUMNS顯示資料庫所有索引的表的資訊

ALL_IND_COLUMNS

USER_IND_COLUMNS

3,顯示索引段位置及尺寸

DBA_SEGMENTS可以顯示資料庫索引段的資訊

USER_SEGMENTS顯示當前使用者段的資訊.

4,顯示函式索引

DBA_IND_EXPRESSIONS顯示資料庫索引函式索引所對應的函式或表示式

ALL_IND_EXPRESSIONS

USER_IND_EXPRESSIONS