Oracle管理索引

1、12Copyright Oracle Corporation, 2001. All rights reserved.離離DEADLINE還有還有35天天12Copyright Oracle Corporation, 2001. All rights reserved.管理索引管理索引 12-3Copyright Oracle Corporation, 2001. All rights reserved.目標目標 完成這一課的學習后，您應該能達到下列目標：完成這一課的學習后，您應該能達到下列目標： 列出各種類型的索引及其用途列出各種類型的索引及其用途 創(chuàng)建各種類型的索引創(chuàng)建各種類型的索引 重新組

2、織索引重新組織索引 維護索引維護索引 監(jiān)視索引的使用監(jiān)視索引的使用 12-4Copyright Oracle Corporation, 2001. All rights reserved.索引分類索引分類 邏輯邏輯 單列或串接單列或串接 唯一或非唯一唯一或非唯一 基于函數(shù)基于函數(shù) 物理物理 分區(qū)或非分區(qū)分區(qū)或非分區(qū) B 樹樹 正常或反向鍵正?；蚍聪蜴I 位圖位圖 12-5Copyright Oracle Corporation, 2001. All rights reserved.12-6Copyright Oracle Corporation, 2001. All rights reserve

3、d.B 樹索引樹索引 索引項標題索引項標題 鍵列長度鍵列長度 鍵列值鍵列值 行標識行標識 根根 分支分支 葉葉 索引項索引項 12-7Copyright Oracle Corporation, 2001. All rights reserved.B 樹索引樹索引 索引葉項的特征：索引葉項的特征： 在非分區(qū)表上的在非分區(qū)表上的 B 樹索引中：樹索引中： 如果多行具有相同的鍵值，除非對索引進行了壓縮，否則鍵值重復。如果多行具有相同的鍵值，除非對索引進行了壓縮，否則鍵值重復。 對于所有鍵列都為對于所有鍵列都為 NULL 的行，沒有對應的索引項。因此，的行，沒有對應的索引項。因此， WHERE 子子句

4、中索引所在的列值為句中索引所在的列值為 NULL，則，則始終進行全表掃描。始終進行全表掃描。 因為所有行都屬于同一段，所以使用受限行標識指向表中的行。因為所有行都屬于同一段，所以使用受限行標識指向表中的行。 DML 操作對索引的影響：操作對索引的影響： 在表上執(zhí)行在表上執(zhí)行 DML 操作時，操作時，Oracle 服務器將維護所有的索引。下面解釋服務器將維護所有的索引。下面解釋 DML 命令對索引的影響：命令對索引的影響： 插入操作導致在對應的索引塊中插入索引項。插入操作導致在對應的索引塊中插入索引項。 刪除行只導致邏輯刪除索引項。刪除的行所用的空間仍不能用于新項，刪除行只導致邏輯刪除索引項。刪

5、除的行所用的空間仍不能用于新項，直到刪除塊中的所有項。直到刪除塊中的所有項。 更新鍵列將導致邏輯刪除和向索引插入項。更新鍵列將導致邏輯刪除和向索引插入項。12-8Copyright Oracle Corporation, 2001. All rights reserved.位圖索引位圖索引 關鍵字關鍵字 開始開始 行標識行標識 結束結束 行標識行標識 位圖位圖 表表 索引索引 塊塊 10塊塊 11塊塊 12文件文件 312-9Copyright Oracle Corporation, 2001. All rights reserved.位圖索引的結構位圖索引的結構 位圖索引也是一種位圖索引也是

6、一種 B 樹結構，但葉節(jié)點存儲每個鍵值的位圖而非樹結構，但葉節(jié)點存儲每個鍵值的位圖而非行標識列表。位圖中的每一位對應一個可能的行標識，如果被置行標識列表。位圖中的每一位對應一個可能的行標識，如果被置位，則意味著具有相應行標識的行包含鍵值。位，則意味著具有相應行標識的行包含鍵值。 如圖所示，位圖索引的葉節(jié)點包含下列幾項：如圖所示，位圖索引的葉節(jié)點包含下列幾項： 項標題，包含行數(shù)和鎖的信息項標題，包含行數(shù)和鎖的信息 鍵值由每個鍵列鍵值由每個鍵列的的 “長度長度 - 值值” 雙值組成（本例中，關鍵字僅包雙值組成（本例中，關鍵字僅包含一列，第一項的鍵值為含一列，第一項的鍵值為 “學士學士”）。）。 開

7、始行標識，本例中的開始行標識包含文件號開始行標識，本例中的開始行標識包含文件號 3、塊號、塊號 10 和行號和行號 0 結束行標識，本例中的結束行標識包含文件號結束行標識，本例中的結束行標識包含文件號 3、塊號、塊號 12 和行號和行號 8 位圖段，由位串組成（對應的行包含鍵值時設置位；不包含鍵值時位圖段，由位串組成（對應的行包含鍵值時設置位；不包含鍵值時不設置位。不設置位。Oracle 服務器使用專利壓縮技術存儲位圖段。）服務器使用專利壓縮技術存儲位圖段。）12-10Copyright Oracle Corporation, 2001. All rights reserved.12-11Co

8、pyright Oracle Corporation, 2001. All rights reserved.比較比較 B 樹索引和樹索引和 位圖索引位圖索引 B 樹樹 適用于高基數(shù)列適用于高基數(shù)列 更新關鍵字的成本相對較低更新關鍵字的成本相對較低 使用使用 OR 謂詞進行查詢時謂詞進行查詢時效率較低效率較低 對對 OLTP 很有用很有用 位圖位圖 適用于低基數(shù)列適用于低基數(shù)列 更新鍵列的成本非常高更新鍵列的成本非常高 使用使用 OR 謂詞進行查詢時謂詞進行查詢時效率較高效率較高 對數(shù)據(jù)倉庫很有用對數(shù)據(jù)倉庫很有用 12-12Copyright Oracle Corporation, 2001.

9、All rights reserved.創(chuàng)建索引：原則創(chuàng)建索引：原則 平衡查詢和平衡查詢和 DML 存放在單獨的表空間中存放在單獨的表空間中 使用統(tǒng)一的區(qū)大?。簤K數(shù)是使用統(tǒng)一的區(qū)大?。簤K數(shù)是 5 的倍數(shù)或?qū)Ρ砜臻g的倍數(shù)或?qū)Ρ砜臻g使用使用 MINIMUM EXTENT 大小大小 對于大型索引，請考慮使用對于大型索引，請考慮使用 NOLOGGING 通常，通常，INITRANS 在索引中比在對應的表中高。在索引中比在對應的表中高。 12-13Copyright Oracle Corporation, 2001. All rights reserved.12-14Copyright Oracle C

10、orporation, 2001. All rights reserved.創(chuàng)建索引創(chuàng)建索引CREATE UNIQUE|BITMAP INDEX 用戶名用戶名.索引名索引名ON 用戶名用戶名.表名表名(列名列名 ASC|DESC ,列名列名 ASC|DESC ) TABLESPACE 表空間名表空間名 PCTFREE 正整數(shù)值正整數(shù)值 INITRANS 正整數(shù)值正整數(shù)值 MAXTRANS 正整數(shù)值正整數(shù)值 存儲子句存儲子句 LOGGING | NOLOGGING NOSORT 12-15Copyright Oracle Corporation, 2001. All rights reserve

11、d.創(chuàng)建索引創(chuàng)建索引 PCTFREE：創(chuàng)建索引時為容納新的索引項而在每塊中保留的空間大小創(chuàng)建索引時為容納新的索引項而在每塊中保留的空間大小 INITRANS：指定每塊中預先分配的事務處理項的數(shù)目（缺省值和最指定每塊中預先分配的事務處理項的數(shù)目（缺省值和最小值為小值為 2。）。） MAXTRANS：限制可以為每個塊分配的事務處理項數(shù)（缺省值為限制可以為每個塊分配的事務處理項數(shù)（缺省值為 255。）。） STORAGE 子句：標識確定如何為索引分配區(qū)的存儲子句子句：標識確定如何為索引分配區(qū)的存儲子句 LOGGING：指定在重做日志文件中記錄索引創(chuàng)建操作和在索引上執(zhí)行指定在重做日志文件中記錄索引創(chuàng)建

12、操作和在索引上執(zhí)行的后續(xù)操作（這是缺省值。）的后續(xù)操作（這是缺省值。） NOLOGGING：指定在重做日志文件中不記錄創(chuàng)建操作和某些類型的指定在重做日志文件中不記錄創(chuàng)建操作和某些類型的數(shù)據(jù)加載操作數(shù)據(jù)加載操作 NOSORT：指定將行按升序存儲在數(shù)據(jù)庫中，這樣，指定將行按升序存儲在數(shù)據(jù)庫中，這樣，Oracle 服務器在服務器在創(chuàng)建索引時不必對行進行排序創(chuàng)建索引時不必對行進行排序 12-16Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建正常的創(chuàng)建正常的 B 樹索引樹索引 create index c_name on course

13、(cname) storage( initial 100k next 100k maxextents 100)tablespace test1;12-17Copyright Oracle Corporation, 2001. All rights reserved.12-18Copyright Oracle Corporation, 2001. All rights reserved.創(chuàng)建位圖索引創(chuàng)建位圖索引 create bitmap index c_name on course(cname) storage( initial 100k next 100k maxextents 100)ta

14、blespace test1;12-19Copyright Oracle Corporation, 2001. All rights reserved.12-20Copyright Oracle Corporation, 2001. All rights reserved.分配和回收索引空間分配和回收索引空間 ALTER INDEX schema.indexALLOCATE EXTENT (SIZE integer K|M DATAFILE filename )| DEALLOCATE UNUSED KEEP integer K|M 12-21Copyright Oracle Corporat

15、ion, 2001. All rights reserved.分配和回收索引空間分配和回收索引空間 ALTER INDEX c_nameALLOCATE EXTENT (SIZE 200KDATAFILE );ALTER INDEX c_nameDEALLOCATE UNUSED;12-22Copyright Oracle Corporation, 2001. All rights reserved.重建索引重建索引 ALTER INDEX schema. index REBUILD TABLESPACE tablespace PCTFREE integer INITRANS integer

16、MAXTRANS integer storage-clause LOGGING| NOLOGGING REVERSE | NOREVERSE ALTER INDEX .REBUILD 命令不能用于將位圖索引更改為命令不能用于將位圖索引更改為 B 樹索引，反之亦然。樹索引，反之亦然。只能為只能為 B 樹索引指定樹索引指定 REVERSE 或或 NOREVERSE 關鍵字。關鍵字。 12-23Copyright Oracle Corporation, 2001. All rights reserved.重建索引重建索引 使用使用 ALTER INDEX 命令執(zhí)行以下操作：命令執(zhí)行以下操作： 將索引

17、移到另一個表空間中將索引移到另一個表空間中 通過移除已刪除的項，提高空間的使用率通過移除已刪除的項，提高空間的使用率 ALTER INDEX orders_region_id_idx REBUILDTABLESPACE indx02;12-24Copyright Oracle Corporation, 2001. All rights reserved.12-25Copyright Oracle Corporation, 2001. All rights reserved.聯(lián)機重建索引聯(lián)機重建索引 可以使用最小限度的表鎖定來重建索引?？梢允褂米钚∠薅鹊谋礞i定來重建索引。 限制：限制： 不能在臨

18、時表中重建索引不能在臨時表中重建索引 不能重建整個分區(qū)索引。必須分別重建每個分區(qū)或子分區(qū)。不能重建整個分區(qū)索引。必須分別重建每個分區(qū)或子分區(qū)。 也不能回收未用空間。也不能回收未用空間。 不能整個更改索引的不能整個更改索引的 PCTFREE 參數(shù)值。參數(shù)值。 ALTER INDEX orders_id_idx REBUILD ONLINE;12-26Copyright Oracle Corporation, 2001. All rights reserved.合并索引合并索引 合并前合并前 合并后合并后 ALTER INDEX c_name COALESCE;12-27Copyright Ora

19、cle Corporation, 2001. All rights reserved.檢查索引及其有效性檢查索引及其有效性 ANALYZE INDEX c_nameVALIDATE STRUCTURE;INDEX_STATSSELECT blocks, pct_used, distinct_keys, lf_rows, del_lf_rows FROM index_stats;如果索引中已刪除行的比例很高，請重新組織該索引。例如：當如果索引中已刪除行的比例很高，請重新組織該索引。例如：當 DEL_LF_ROWS 占占 LF_ROWS 的比率超過的比率超過 30% 時時12-28Copyrigh

20、t Oracle Corporation, 2001. All rights reserved.12-29Copyright Oracle Corporation, 2001. All rights reserved.標識索引標識索引 要開始監(jiān)視索引的使用，請執(zhí)行以下語句：要開始監(jiān)視索引的使用，請執(zhí)行以下語句： 要停止監(jiān)視索引的使用，請執(zhí)行以下語句：要停止監(jiān)視索引的使用，請執(zhí)行以下語句： 查看索引使用情況查看索引使用情況ALTER INDEX c_name MONITORING USAGE;ALTER INDEX c_name NOMONITORING USAGE;SELECT * FROM V$OBJECT_USAGE;12-30Copyright Oracle Corporation, 2001. All rights reserved.刪除索引刪除索引 在批量加載前，請刪除并重新創(chuàng)建索引。在批量加載前，請刪除并重新創(chuàng)建索引。 刪除不常用的索引，并在需要時創(chuàng)建這些索引。刪除不常用的索引，并在需要時創(chuàng)建這些索引。 刪除并重新創(chuàng)建無效的索引。刪除并重新創(chuàng)建無效的索引。 DROP INDEX C_NAME;12-31Copyright Oracle Corporation, 2001. All rights reserved.12-32Copyright