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.目標(biāo)目標(biāo) 完成這一課的學(xué)習(xí)后，您應(yīng)該能達(dá)到下列目標(biāo)：完成這一課的學(xué)習(xí)后，您應(yīng)該能達(dá)到下列目標(biāo)： 列出各種類型的索引及其用途列出各種類型的索引及其用途 創(chuàng)建各種類型的索引創(chuàng)建各種類型的索引 重新組

2、織索引重新組織索引 維護(hù)索引維護(hù)索引 監(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 樹索引樹索引 索引項(xiàng)標(biāo)題索引項(xiàng)標(biāo)題 鍵列長(zhǎng)度鍵列長(zhǎng)度 鍵列值鍵列值 行標(biāo)識(shí)行標(biāo)識(shí) 根根 分支分支 葉葉 索引項(xiàng)索引項(xiàng) 12-7Copyright Oracle Corporation, 2001. All rights reserved.B 樹索引樹索引 索引葉項(xiàng)的特征：索引葉項(xiàng)的特征： 在非分區(qū)表上的在非分區(qū)表上的 B 樹索引中：樹索引中： 如果多行具有相同的鍵值，除非對(duì)索引進(jìn)行了壓縮，否則鍵值重復(fù)。如果多行具有相同的鍵值，除非對(duì)索引進(jìn)行了壓縮，否則鍵值重復(fù)。 對(duì)于所有鍵列都為對(duì)于所有鍵列都為 NULL 的行，沒有對(duì)應(yīng)的索引項(xiàng)。因此，的行，沒有對(duì)應(yīng)的索引項(xiàng)。因此， WHERE 子子句

4、中索引所在的列值為句中索引所在的列值為 NULL，則，則始終進(jìn)行全表掃描。始終進(jìn)行全表掃描。 因?yàn)樗行卸紝儆谕欢危允褂檬芟扌袠?biāo)識(shí)指向表中的行。因?yàn)樗行卸紝儆谕欢?，所以使用受限行?biāo)識(shí)指向表中的行。 DML 操作對(duì)索引的影響：操作對(duì)索引的影響： 在表上執(zhí)行在表上執(zhí)行 DML 操作時(shí)，操作時(shí)，Oracle 服務(wù)器將維護(hù)所有的索引。下面解釋服務(wù)器將維護(hù)所有的索引。下面解釋 DML 命令對(duì)索引的影響：命令對(duì)索引的影響： 插入操作導(dǎo)致在對(duì)應(yīng)的索引塊中插入索引項(xiàng)。插入操作導(dǎo)致在對(duì)應(yīng)的索引塊中插入索引項(xiàng)。 刪除行只導(dǎo)致邏輯刪除索引項(xiàng)。刪除的行所用的空間仍不能用于新項(xiàng)，刪除行只導(dǎo)致邏輯刪除索引項(xiàng)。刪

5、除的行所用的空間仍不能用于新項(xiàng)，直到刪除塊中的所有項(xiàng)。直到刪除塊中的所有項(xiàng)。 更新鍵列將導(dǎo)致邏輯刪除和向索引插入項(xiàng)。更新鍵列將導(dǎo)致邏輯刪除和向索引插入項(xiàng)。12-8Copyright Oracle Corporation, 2001. All rights reserved.位圖索引位圖索引 關(guān)鍵字關(guān)鍵字 開始開始 行標(biāo)識(shí)行標(biāo)識(shí) 結(jié)束結(jié)束 行標(biāo)識(shí)行標(biāo)識(shí) 位圖位圖 表表 索引索引 塊塊 10塊塊 11塊塊 12文件文件 312-9Copyright Oracle Corporation, 2001. All rights reserved.位圖索引的結(jié)構(gòu)位圖索引的結(jié)構(gòu) 位圖索引也是一種位圖索引也是

6、一種 B 樹結(jié)構(gòu)，但葉節(jié)點(diǎn)存儲(chǔ)每個(gè)鍵值的位圖而非樹結(jié)構(gòu)，但葉節(jié)點(diǎn)存儲(chǔ)每個(gè)鍵值的位圖而非行標(biāo)識(shí)列表。位圖中的每一位對(duì)應(yīng)一個(gè)可能的行標(biāo)識(shí)，如果被置行標(biāo)識(shí)列表。位圖中的每一位對(duì)應(yīng)一個(gè)可能的行標(biāo)識(shí)，如果被置位，則意味著具有相應(yīng)行標(biāo)識(shí)的行包含鍵值。位，則意味著具有相應(yīng)行標(biāo)識(shí)的行包含鍵值。 如圖所示，位圖索引的葉節(jié)點(diǎn)包含下列幾項(xiàng)：如圖所示，位圖索引的葉節(jié)點(diǎn)包含下列幾項(xiàng)： 項(xiàng)標(biāo)題，包含行數(shù)和鎖的信息項(xiàng)標(biāo)題，包含行數(shù)和鎖的信息 鍵值由每個(gè)鍵列鍵值由每個(gè)鍵列的的 “長(zhǎng)度長(zhǎng)度 - 值值” 雙值組成（本例中，關(guān)鍵字僅包雙值組成（本例中，關(guān)鍵字僅包含一列，第一項(xiàng)的鍵值為含一列，第一項(xiàng)的鍵值為 “學(xué)士學(xué)士”）。）。 開

7、始行標(biāo)識(shí)，本例中的開始行標(biāo)識(shí)包含文件號(hào)開始行標(biāo)識(shí)，本例中的開始行標(biāo)識(shí)包含文件號(hào) 3、塊號(hào)、塊號(hào) 10 和行號(hào)和行號(hào) 0 結(jié)束行標(biāo)識(shí)，本例中的結(jié)束行標(biāo)識(shí)包含文件號(hào)結(jié)束行標(biāo)識(shí)，本例中的結(jié)束行標(biāo)識(shí)包含文件號(hào) 3、塊號(hào)、塊號(hào) 12 和行號(hào)和行號(hào) 8 位圖段，由位串組成（對(duì)應(yīng)的行包含鍵值時(shí)設(shè)置位；不包含鍵值時(shí)位圖段，由位串組成（對(duì)應(yīng)的行包含鍵值時(shí)設(shè)置位；不包含鍵值時(shí)不設(shè)置位。不設(shè)置位。Oracle 服務(wù)器使用專利壓縮技術(shù)存儲(chǔ)位圖段。）服務(wù)器使用專利壓縮技術(shù)存儲(chǔ)位圖段。）12-10Copyright Oracle Corporation, 2001. All rights reserved.12-11Co

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

9、All rights reserved.創(chuàng)建索引：原則創(chuàng)建索引：原則 平衡查詢和平衡查詢和 DML 存放在單獨(dú)的表空間中存放在單獨(dú)的表空間中 使用統(tǒng)一的區(qū)大?。簤K數(shù)是使用統(tǒng)一的區(qū)大?。簤K數(shù)是 5 的倍數(shù)或?qū)Ρ砜臻g的倍數(shù)或?qū)Ρ砜臻g使用使用 MINIMUM EXTENT 大小大小 對(duì)于大型索引，請(qǐng)考慮使用對(duì)于大型索引，請(qǐng)考慮使用 NOLOGGING 通常，通常，INITRANS 在索引中比在對(duì)應(yīng)的表中高。在索引中比在對(duì)應(yīng)的表中高。 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ù)值 存儲(chǔ)子句存儲(chǔ)子句 LOGGING | NOLOGGING NOSORT 12-15Copyright Oracle Corporation, 2001. All rights reserve

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

12、操作和在索引上執(zhí)行的后續(xù)操作（這是缺省值。）的后續(xù)操作（這是缺省值。） NOLOGGING：指定在重做日志文件中不記錄創(chuàng)建操作和某些類型的指定在重做日志文件中不記錄創(chuàng)建操作和某些類型的數(shù)據(jù)加載操作數(shù)據(jù)加載操作 NOSORT：指定將行按升序存儲(chǔ)在數(shù)據(jù)庫(kù)中，這樣，指定將行按升序存儲(chǔ)在數(shù)據(jù)庫(kù)中，這樣，Oracle 服務(wù)器在服務(wù)器在創(chuàng)建索引時(shí)不必對(duì)行進(jìn)行排序創(chuàng)建索引時(shí)不必對(duì)行進(jìn)行排序 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 關(guān)鍵字。關(guān)鍵字。 12-23Copyright Oracle Corporation, 2001. All rights reserved.重建索引重建索引 使用使用 ALTER INDEX 命令執(zhí)行以下操作：命令執(zhí)行以下操作： 將索引

17、移到另一個(gè)表空間中將索引移到另一個(gè)表空間中 通過移除已刪除的項(xiàng)，提高空間的使用率通過移除已刪除的項(xiàng)，提高空間的使用率 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)機(jī)重建索引聯(lián)機(jī)重建索引 可以使用最小限度的表鎖定來重建索引?？梢允褂米钚∠薅鹊谋礞i定來重建索引。 限制：限制： 不能在臨

18、時(shí)表中重建索引不能在臨時(shí)表中重建索引 不能重建整個(gè)分區(qū)索引。必須分別重建每個(gè)分區(qū)或子分區(qū)。不能重建整個(gè)分區(qū)索引。必須分別重建每個(gè)分區(qū)或子分區(qū)。 也不能回收未用空間。也不能回收未用空間。 不能整個(gè)更改索引的不能整個(gè)更改索引的 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;如果索引中已刪除行的比例很高，請(qǐng)重新組織該索引。例如：當(dāng)如果索引中已刪除行的比例很高，請(qǐng)重新組織該索引。例如：當(dāng) DEL_LF_ROWS 占占 LF_ROWS 的比率超過的比率超過 30% 時(shí)時(shí)12-28Copyrigh

20、t Oracle Corporation, 2001. All rights reserved.12-29Copyright Oracle Corporation, 2001. All rights reserved.標(biāo)識(shí)索引標(biāo)識(shí)索引 要開始監(jiān)視索引的使用，請(qǐng)執(zhí)行以下語句：要開始監(jiān)視索引的使用，請(qǐng)執(zhí)行以下語句： 要停止監(jiān)視索引的使用，請(qǐng)執(zhí)行以下語句：要停止監(jiān)視索引的使用，請(qǐng)執(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.刪除索引刪除索引 在批量加載前，請(qǐng)刪除并重新創(chuàng)建索引。在批量加載前，請(qǐng)刪除并重新創(chuàng)建索引。 刪除不常用的索引，并在需要時(shí)創(chuàng)建這些索引。刪除不常用的索引，并在需要時(shí)創(chuàng)建這些索引。 刪除并重新創(chuàng)建無效的索引。刪除并重新創(chuàng)建無效的索引。 DROP INDEX C_NAME;12-31Copyright Oracle Corporation, 2001. All rights reserved.12-32Copyright