我的Oracle 9i學習日志（16）-- 索引的管理

索引分類：

• 邏輯

– 單列或串接：在單列或多列上建立索引，最多包含32列。

– 唯一或非唯一：保證在表中沒有兩行或以上的鍵列沒有重複值或不限制。

– 基于函數：在建立索引的一列或多列上使用函數或表達式，索引預先計算函數或表達式的值，并将結果存儲在索引中。可以建立為B樹或位圖索引。

– 域：對于特定應用程式的索引。僅支援單列域索引。可以在具有标量、對象或LOB 資料類型的列上建立單列域索引。

• 實體

– 分區或非分區

– B 樹

正常或反向鍵

– 位圖

B樹索引：

圖1

B 樹索引的結構：

索引的頂部為根，其中包含指向索引中下一級的項。下一級為分支塊，分支塊又指向索引中下一級的塊。最低一級為葉節點，其中包含指向表行的索引項。葉塊為雙重連結，有助于按鍵值的升序和降序掃描索引。如下圖：

圖2

索引葉項的格式：

索引項由以下部分組成：

• 項标題，存儲列數和鎖定資訊

• 鍵列的“長度- 值” 對，用于定義鍵列的大小及該列的值（該值對的數目即索引中的最大列數。）

• 行的行辨別，包含鍵值

索引葉項的特征：

在非分區表上的B 樹索引中：

• 如果多行具有相同的鍵值，除非對索引進行了壓縮，否則鍵值重複。

• 對于所有鍵列都為NULL 的行，沒有對應的索引項。是以，指定NULL 的WHERE 子句始終進行全表掃描。

• 因為所有行都屬于同一段，是以使用受限行辨別指向表中的行。

DML 操作對索引的影響：

在表上執行DML 操作時，Oracle 伺服器将維護所有的索引。下面解釋DML 指令對索引的影響：

• 插入操作導緻在适當的塊中插入索引項。

• 删除行隻導緻邏輯删除索引項。删除的行所用的空間仍不能用于新項，直到删除塊中的所有項。

• 更新鍵列将導緻邏輯删除和向索引插入項。除了建立時以外，PCTFREE 設定在其它任何時候都對索引沒有影響。即使索引塊空間少于PCTFREE 指定的空間，仍可以向索引塊添加新項。

位圖索引

圖3

在下列情況中，位圖索引比B 樹索引更有利：

• 當表包含數百萬行且鍵列的基數很低（即，該列中重複的值很多）時。例如，對于包含護照記錄的表的性别列和婚姻狀況列而言，位圖索引比B 樹索引更适合

• 當查詢經常使用涉及OR 運算符的多個WHERE 條件組合時

• 當鍵列上存在隻讀或很少的更新操作時

位圖索引的結構：

将位圖索引也組織為B 樹，但葉節點存儲每個鍵值的位圖而非行辨別清單。位圖中的每一位對應一個可能的行辨別，如果設定了位，則意味着具有相應行辨別的行包含鍵值。

如圖3所示，位圖索引的葉節點包含下列幾項：

• 項标題，包含列數和鎖定資訊

• 鍵值由每個鍵列的“長度- 值” 雙值組成（本例中，關鍵字僅包含一列，第一項的鍵值為Blue）。

• 開始行辨別，本例中的開始行辨別包含檔案号3、塊号10 和行号0

• 結束行辨別，本例中的結束行辨別包含塊号12 和行号8

• 位圖段，由位串組成（對應的行包含鍵值時設定位；不包含鍵值時不設定位。Oracle

伺服器使用專利壓縮技術存儲位圖段。）開始行辨別是位圖的位圖段指向的第一行的行辨別，即，位圖的第一位對應此行辨別，位圖的第二位對應塊中的下一行，而結束行辨別是位圖段所包含的表中最後一行的指針。位圖索引使用受限行辨別。

使用位圖索引：

B 樹用于定位包含給定鍵值的位圖段的葉節點。開始行辨別和位圖段用于定位包含鍵值的行。

更改表中的鍵列時，必須修改位圖。這将導緻鎖定相關的位圖段。由于鎖是在整個位圖段上擷取的，是以直到第一個事務處理結束後，才能由其它事務處理更新位圖包含的行。

圖4

建立索引

CREATE INDEX hr.employees_last_name_idx ON hr.employees(last_name)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx;

圖5

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

ON { cluster_index_clause

| table_index_clause

| bitmap_join_index_clause

}

[ UNUSABLE ] ;

索引可在表所有者的帳戶下或其它帳戶下建立，但索引通常在表所在的同一帳戶下建立。

上面的語句在EMPLOYEES 表上建立一個索引（使用LAST_NAME 列）。

文法選項

UNIQUE：用于指定唯一的索引（預設為Nonunique。）

Schema：索引/表的所有者

Index：索引名

Table：表名

Column：列名

ASC/DESC：訓示是按升序還是按降序建立索引

TABLESPACE：指定要在其中建立索引的表空間

PCTFREE：建立索引時為容納新的索引項而在每塊中保留的空間大小（以總空間量減去塊

頭後的百分比表示）

INITRANS：指定每塊中預先配置設定的事務處理項的數目（預設值和最小值為2。）

MAXTRANS：限制可以為每個塊配置設定的事務處理項數（預設值為255。）

STORAGE 子句：辨別确定如何為索引配置設定區的存儲子句

LOGGING：指定在重做日志檔案中記錄索引建立操作和在索引上執行的後續操作（這是預設值。）

NOLOGGING：指定在重做日志檔案中不記錄建立操作和某些類型的資料加載操作

NOSORT：指定将行按升序存儲在資料庫中，這樣，Oracle 伺服器在建立索引時不必對行進行排序

注：

• 如果已為表空間定義了MINIMUM EXTENT，則索引的區大小将向上舍入為下一個更高的MINIMUM EXTENT 值的倍數。

• 如果省略了[NO]LOGGING 子句，索引的事件記錄屬性将預設為表所駐留的表空間的事件記錄屬性。

• 不能為索引指定PCTUSED。由于索引項必須按正确的順序存儲，是以使用者無法控制何時在某一索引塊中插入。

• 如果在資料未按關鍵字排序的情況下使用NOSORT 關鍵字，語句将終止并顯示錯誤。如果表上已經有多個DML 操作，則該選項很可能無效。

• 如果可能，Oracle 伺服器使用現有索引建立新的索引。當新索引的關鍵字與現有索引鍵的主要部分對應時，就會發生這種情況。

建立索引原則

建立索引時應考慮：

• 索引能夠提高查詢性能并降低DML 操作速度。始終使易失表所需的索引數保持最少。

• 将索引放在一個單獨的表空間中，不要放在有還原段、臨時段和表的表空間中。

• 使用統一的區大小：塊數是5 的倍數或對表空間使用MINIMUM EXTENT 大小

• 對大型索引而言，避免生成重做日志可顯著提高性能。請考慮使用NOLOGGING 子句

建立大型索引。

• 由于索引項比索引行小，是以索引塊趨向于在每塊中包含更多的項。是以，

INITRANS 在索引中通常比在對應的表中高。

索引和PCTFREE：

索引的PCTFREE 參數與表的PCTFREE 參數工作方式不同。前者僅在建立索引時用來為需要插入到同一索引塊的索引項保留白間。而不更新索引項。更新鍵列時，這将涉及邏輯删除索引項和插入。

在單調遞增（如系統生成的發票号）列的索引上使用較低的PCTFREE 值。在這些情況下，新的索引項總是追加到現有項上，沒有必要在兩個現有索引項間插入一個新項。

如果插入行的索引列值可采用任何值（即新值在目前的值範圍内），則應該提供較高的PCTFREE。發票表的客戶代碼列上的索引就是一個要求高PCTFREE 值的索引。在這種情況下，将PCTFREE 值指定為由下列等式所表示的值是非常有用的：

最大行數– 初始行數x 100

最大行數

最大值可用于特定的時間周期，如一年。

建立位圖索引

CREATE BITMAP INDEX orders_region_id_idx ON orders(region_id) PCTFREE 30 STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;

文法：

使用下列指令建立位圖索引：

CREATE BITMAP INDEX [schema.] index

ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ NOSORT ]

注意，位圖索引不能是唯一的（unique）。

初始化參數CREATE_BITMAP_AREA_SIZE 決定了記憶體中用于存儲位圖段的空間量。預設值為8 MB。使用較大的值，可提高索引建立的速度。如果基數很小，可将該值設定為一個較小值。例如，如果基數僅為2，則該值可以為千位元組數量級而非兆位元組數量級。一般來講，基數越大，則擷取最佳性能所需的記憶體越多。

更改索引的存儲參數

某些存儲參數和塊使用參數可通過ALTER INDEX 指令進行修改。

ALTER INDEX [schema.]index

更改索引存儲參數與更改表存儲參數的含義一樣。通常，進行此類更改是為了增加索引的MAXEXTENTS。可更改塊使用參數以保證在索引塊上實作更進階别的并發性。

配置設定和回收索引空間

手動配置設定索引空間：

在表上進行頻繁的插入操作前，可能需要向索引添加區。添加區可防止索引動态擴充并導緻性能降低。

從索引中手動回收空間：

使用ALTER INDEX 指令的DEALLOCATE 子句釋放索引中超過高水位标記的未用空間。

使用下列指令配置設定或回收索引空間：

{ALLOCATE EXTENT ([SIZE integer [K|M]]

[ DATAFILE ‘filename’ ])

| DEALLOCATE UNUSED [KEEP integer [ K|M ] ] }

手動配置設定和手動回收索引空間所遵循的規則與在表上使用這些指令時相同。

注：當建立索引的表被截斷時，回收索引空間。截斷表将導緻截斷關聯的索引。

離線重建索引

ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;

Oracle操作步驟：

1、鎖定表。

2、建立一個新的，臨時的索引，從原索引讀取資訊。

3、删除原索引。

4、重命名臨時索引為原索引。

5、解除表鎖定。

索引重建具有以下特點：

• 将現有索引作為資料源建立新索引。

• 使用現有索引建立索引時無需排序，進而使性能更佳。

• 在建立新索引後，删除舊索引。在重建期間，各自的表空間内需要有足夠的空間以容

納新舊索引。

• 結果索引不包括任何已删除的項。是以，該索引可以更有效地使用空間。

• 在建立新索引的過程中，查詢可繼續使用現有索引。

可能的重建情況：

在下列情況下應重建索引：

• 需要将現有索引移到另外的表空間中。如果索引和表在同一表空間中或者需要跨磁盤重新分布對象時，可能需要執行此操作。

• 索引中包含很多已删除的項。這是滑動索引（如訂單表中的訂單号上的索引）存在的典型問題，完成的訂單已被删除，并将具有更高訂單号的新訂單添加到表中。如果有幾個舊訂單未完成，則可能有若幹個索引葉塊包含除幾個已删除項之外的全部項。

• 需要将現有正常索引轉換成反向鍵索引。在從Oracle 伺服器的早期發行版移植應用程式時，可能會出現這種情況。

• 已認證ALTER TABLE..MOVE TABLESPACE 指令将索引表移至其它表空間。

使用下列指令重建索引：

ALTER INDEX [schema.] index REBUILD

[ REVERSE | NOREVERSE ]

ALTER INDEX ...REBUILD 指令不能用于将位圖索引更改為B 樹索引，反之亦然。隻能為B 樹索引指定REVERSE 或NOREVERSE 關鍵字。

聯機重建索引

過程：

圖6

建立或重建索引是一項費時的任務，尤其當表非常大時更是如此。在Oracle8i 之前，建立或重建索引都需要鎖定表，并要防止并發的DML 操作。Oracle9i 允許在基表上進行并發操作的同時建立或重建索引，但不建議在此過程中執行大量的DML 操作。

注：仍存在DML 鎖，這意味着在聯機索引建立期間不能執行其它DDL 操作。

限制：

• 不能在臨時表中重建索引

• 不能重建整個分區索引。必須分别重建每個分區或子分區。

• 也不能回收未用空間。

• 不能整個更改索引的PCTFREE 參數值。

合并索引

圖7

1、掃描葉子節點。

2、把能合并成一個塊的合并。

遇到索引碎片時，可以重建或合并索引。執行上述任務前，應考慮每種選擇的成本和好處，然後選擇最适合自己情況的方案。索引合并是聯機完成的塊重建過程。

如果有可以釋放以供重用的B 樹索引葉塊，則可使用下列SQL 語句合并這些葉塊：

SQL> ALTER INDEX hr.employees_idx COALESCE;

圖5顯示了ALTER INDEX … COALESCE 語句對索引hr.employees_idx 的影響。

檢查索引及其有效性

分析索引以執行以下操作：

• 檢查所有的索引塊是否存在損壞。注意，此指令并不驗證索引項是否與表中的資料對應。

• 将檢查結果傳回給INDEX_STATS 視圖。

ANALYZE INDEX [ schema.]index VALIDATE STRUCTURE

運作此指令後，查詢INDEX_STATS 以擷取索引的有關資訊，如下例所示：

SQL> SELECT blocks, pct_used, distinct_keys,

2 lf_rows, del_lf_rows

3 FROM index_stats;

BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS

------ --------- -------- ------------

25 11 14 0

1 row selected.

如果索引中已删除行的比例很高，請重新組織該索引。例如：當DEL_LF_ROWS 占

LF_ROWS 的比率超過30% 時。

實驗：

SQL> select * from t;

ID NAME

---------- --------------------

0 aaa

1 aaa

3 aaa

4 aaa

5 aaa

6 aaa

7 aaa

8 aaa

9 aaa

2 aaa

已選擇10行。

SQL> analyze index t_id_idx validate structure;

索引已分析

SQL> select height,blocks,lf_rows,del_lf_rows from index_stats;

HEIGHT BLOCKS LF_ROWS DEL_LF_ROWS

---------- ---------- ---------- -----------

1 8 10 0

SQL> delete from t where id=4;

已删除 1 行。

SQL> commit;

送出完成。

1 8 10 1

SQL> alter index t_id_idx rebuild;

索引已更改。

未標明行

1 8 9 0

SQL> delete from t where id=9;

1 8 9 1

SQL> alter index t_id_idx coalesce;

删除索引：

DROP INDEX hr.deptartments_name_idx;

•在批量載入資料前可以先删除索引，載入完成再建立。

•删除不常用的索引，需要時再建立。

•删除并重新建立無效索引（注意：删除再建比重建索引效率低）。

• 當在某種類型的操作（如加載）期間出現例程失敗時，可能會将索引标記為INVALID。在這種情況下，需要删除并重建索引。

注意：除限制所需的索引，是以，必須先禁用或删除相關的限制。

監視索引（用于辨別未使用索引）：

• 要開始監視索引的使用，執行以下語句：

ALTER INDEX hr.dept_id_idx MONITORING USAGE

• 要停止監視索引的使用，執行以下語句：

ALTER INDEX hr.dept_id_idx NOMONITORING USAGE

SQL> select * from v$object_usage;

SQL> alter index t_id_idx monitoring usage;

SQL> set lines 300

SQL> col index_name format a20

SQL> col start_monitoring format a15

SQL> col end_monitoring format a15

INDEX_NAME TABLE MONITO USED START_MONITORIN END_MONITORING

-------------------- ----- ------ ------ --------------- ---------------

T_ID_IDX T YES NO 04/09/2010 01:43:52

SQL> set autot on exp

SQL> select * from t where id=5;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'T_ID_IDX' (NON-UNIQUE)

T_ID_IDX T YES YES 04/09/2010 01:43:52

已選擇8行。

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'T'

#全表掃描時不會用到索引。

SQL> alter index t_id_idx nomonitoring usage;

T_ID_IDX T NO YES 04/09/2010 01:43:52 04/09/2010 01:58:25

有用視圖：

• DBA_INDEXES：提供有關索引的資訊

• DBA_IND_COLUMNS：提供有關索引列的資訊

• V$OBJECT_USAGE：提供有關索引使用情況的資訊

本文轉自 d185740815 51CTO部落格，原文連結：http://blog.51cto.com/luotaoyang/293088，如需轉載請自行聯系原作者

我的Oracle 9i學習日志（16）-- 索引的管理

繼續閱讀

壓縮編碼M-JPEG、MPEG4、H.264

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

轉詳解C#資料庫存取圖檔三大方式

Oracle的基本操作

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離