基本的索引原理

1、本章內(nèi)容并不是針對數(shù)據(jù)庫專家或是那些想快速找到答案的讀者。本章主要是討論基本的索引原理(也可能是僅有的一章)。對初學者來說，最困難的就是如何找到那些可以填補最主要差距的信息，以及如何了解Oracle的索引功能。本章就是服務于這個目的。盡管市場上有大量面向中高級用戶的書籍，但面向初學者的資料卻非常少，而且需求量往往很高。Oracle提供了大量索引選項。知道在給定條件下使用哪個選項對于一個應用程序的性能來說非常重要。一個錯誤的選擇可能會引發(fā)死鎖，并導致數(shù)據(jù)庫性能急劇下降或進程終止。而如果做出正確的選擇，則可以合理使用資源，使那些已經(jīng)運行了幾小時甚至幾天的進程在幾分鐘內(nèi)得以完成，這樣會使您頗有成就感

2、。本章將討論每個索引選項，然后指出每個選項的優(yōu)缺點。本章主要內(nèi)容：       基本的索引概念       查找被索引的表以及具有連接索引的表       組合索引的使用方法       Oracle ROWID       基于函數(shù)的索引的使用方法    &

3、#160;  如何避免比較不匹配的數(shù)據(jù)類型，造成索引取消       作為索引策略的集群因子       使用INDEX_STATS視圖       索引的二元高度(Binary height)       使用直方圖       快速全局掃描    

4、;   使用索引跳躍式掃描特性的方法       B樹索引的解釋       使用位圖索引的時機       使用HASH索引的時機       使用索引順序表的時機       使用反轉(zhuǎn)鍵索引的時機       使用基

5、于函數(shù)的索引的時機       本地和全局分區(qū)索引2.1  基本的索引概念當從表中訪問數(shù)據(jù)時，Oracle提供了兩個選擇：從表中讀取每一行(即全表掃描)，或者通過ROWID一次讀取一行。當訪問大型表的少量行時，您可能想使用索引。例如，如果只訪問大型表中5%的行，并且使用索引標識讀取的塊，則可以執(zhí)行較少的I/O。如果沒有使用索引，則要讀取表中所有的塊。索引改進性能的程度部分取決于數(shù)據(jù)的選擇性以及在表的塊之間分布數(shù)據(jù)的方式。如果數(shù)據(jù)非常具有選擇性，則表中將只有很少的行匹配索引值(例如護照號碼)。Oracle將能夠快速查詢匹配索引

6、值的ROWID的索引，并且可以快速查詢少量的相關(guān)表塊。如果數(shù)據(jù)不是非常具有選擇性(例如國家名)，則索引可能返回許多ROWID，導致從表中查詢許多單獨的塊。如果數(shù)據(jù)非常具有選擇性，但是相關(guān)的行在表中的存儲位置并不互相靠近，則會進一步減少索引的益處。如果匹配索引值的數(shù)據(jù)分散在表的多個塊中，則必須從表中選擇多個單獨的塊以滿足查詢。在一些情況中，您會發(fā)現(xiàn)當數(shù)據(jù)分散在表的多個塊中時，最好是不使用索引，而是執(zhí)行全表掃描。執(zhí)行全表掃描時，Oracle使用多塊讀取以快速掃描表?；谒饕淖x取是單塊讀取，因此在使用索引時的目標是減少解決查詢所需的單個塊的數(shù)量。通過使用Oracle中的一些可用選項，比如分區(qū)、并行

7、DML、并行查詢操作以及使用db_file_multiblock_read_count進行更大的I/O操作，全表掃描和索引查找之間的平衡點發(fā)生了改變。硬件更為快速，磁盤可以在磁盤上的高速緩存中緩存更多的信息，內(nèi)存也變得更為廉價。與此同時，Oracle已經(jīng)增強的索引特性，包括了跳躍式掃描索引和其他減少檢索數(shù)據(jù)所需時間的操作。技巧：當升級Oracle版本時，確保測試應用程序的查詢以確定查詢的執(zhí)行路徑是否仍然使用在升級之前使用的索引。查看執(zhí)行路徑是否改變，并且查看這種改動的效果是更好還是更差。索引通常能提高查詢的性能。SELECT語句、UPDATE和DELETE命令的WHERE子句的性能(當訪問的行

8、較少時)可以從索引中獲益。一般來說，增加索引會降低INSERT語句的性能(因為需要同時對表和索引進行插入)。如果未索引列，則索引列的UPDATE操作將會減慢執(zhí)行速度，因為數(shù)據(jù)庫必須管理對表和索引的改動。此外，大量行的DELETE操作將會由于表中存在索引而減慢執(zhí)行速度。用于刪除表中一半數(shù)據(jù)的DELETE語句同時需要刪除所有這些行的索引(這種情況是非常耗時的)。通常，表中的每個索引都會使對表執(zhí)行的INSERT操作變慢兩倍；使用兩條索引通常會使插入操作變慢一倍(然而，一個由兩部分組成的單一索引并不比只有一個部分組成的單一索引差很多)。索引列的UPDATE和DELETE操作同樣也會變慢。您需要根據(jù)對數(shù)

9、據(jù)操作性能的影響平衡索引對查詢性能帶來的益處。查詢DBA_INDEXES視圖可獲得表上所有查詢的清單。同樣需要注意的是，可以通過訪問USER_INDEXES視圖檢索模式的索引。查詢ALL_INDEXES視圖可以查看已經(jīng)訪問的所有表的索引。例如，在EMP表上創(chuàng)建了一些索引，EMP表是Oracle的一個演示表。create index emp_id1 on emp(empno, ename, deptno);create index emp_id2 on emp (sal);當發(fā)出這些命令時，數(shù)據(jù)庫將在EMP表上創(chuàng)建兩個單獨的索引。每個索引將包含EMP表中的指定值以及匹配指定值的行的ROWID值。

10、如果需要查找Sal值為1000的EMP記錄，優(yōu)化器就會使用EMP_ID2索引查找該值，在索引中查找相關(guān)的ROWID，并且使用該ROWID在表中查找正確的行。下面的USER_INDEXES查詢顯示了EMP表上的新索引：select       table_name, index_namefrom         user_indexeswhere        table_name = &

11、#39;EMP' ;TABLE_NAME                            INDEX_NAME- -EMP                 

12、                    EMP_ID1EMP                             &

13、#160;       EMP_ID2輸出顯示了兩個索引，但是沒有顯示每個索引中的列。為了獲得給定表中被索引的特定列，可訪問USER_IND_COLUMNS視圖。同樣需要注意的是，DBA可以通過訪問DBA_IND_COLUMNS視圖檢索所有模式中被索引的列，而通過訪問ALL_IND_COLUMNS視圖則可以查看所有表的索引列。column index_name format a12column column_name format a8column table_name format a8select   

14、;  table_name, index_name, column_name, column_positionfrom       user_ind_columnsorder      by table_name, index_name, column_position;TABLE_NA  INDEX_NAME  COLUMN_N COLUMN_POSITION- - - -EMP      &#

15、160;  EMP_ID1       EMPNO                     1EMP         EMP_ID1       ENAME    

16、60;                 2EMP        EMP_ID1       DEPTNO                  

17、   3EMP         EMP_ID2       SAL                        1EMP表中有兩個索引。首先，EMP_ID1是一個組合(concatenated)索引，它對Empno、Ename和

18、Deptno這幾列進行索引。而第二個索引EMP_ID2只對Sal列進行索引。顯示在程序清單中的Column_Position顯示了組合索引中的列順序，即按照Empno、Ename和Deptno的順序。技巧：查詢DBA_INDEXES和DBA_IND _COLUMNS可以檢索到一個給定表的索引列表。對于您自己的模式，只能使用USER_INDEXES和USER_IND_COLUMNS來檢索信息。2.2  組合索引當某個索引包含有多個已索引的列時，我們稱這個索引為組合(concatenated)索引或是復合索引。雖然Oracle 9i引入的跳躍式掃描索引訪問方法增強了優(yōu)化器在使用組合索引時

19、的選擇，但是您應該謹慎地選擇索引中的列順序。一般來說，索引的第一列應該是最有可能在WHERE子句中使用的列，并且也是索引中最具選擇性的列。在引入跳躍式掃描功能之前，查詢只能在WHERE子句中使用索引的第一列時使用索引。考慮如下程序清單中的示例，其中表EMP有一個組合索引，該索引包含了Empno、Ename和Deptno。注意第一部分是Empno，第二部分則是Ename，最后是Deptno。如果沒有使用跳躍式掃描功能，除非在WHERE子句中對第一列(Empno)指定一個值，否則Oracle一般不會使用這個索引。select      job, &

20、#160;empnofrom        empwhere       ename = 'RICH'因為Ename不是索引的第一列，優(yōu)化器可能會選擇不使用該索引。隨著在Oracle 9i中引入了跳躍式掃描功能，即使在WHERE子句中沒有指定Empno值，優(yōu)化器也可能會選擇使用該索引。相反，優(yōu)化器可能會選擇索引的快速全局掃描或全表掃描。如果在WHERE子句中使用索引的第三列，也會產(chǎn)生相同的情況：select    job

21、,  empnofrom      empwhere     deptno = 30;在該程序清單中，WHERE子句指定了索引中第三列的值。優(yōu)化器可能選擇執(zhí)行索引快速掃描訪問、索引快速全局掃描或全表掃描。通過創(chuàng)建索引，您可以在執(zhí)行查詢時為數(shù)據(jù)庫提供更多的選擇。從而有希望改進整體的性能。注意，用戶的代碼沒有改變，優(yōu)化器可以識別該索引，并且根據(jù)每種替代方法的預期成本決定使用何種方法。在下面的示例中，使用了索引的一部分。將第一列Empno用作WHERE子句中的限制條件，以便Oracle可以使用該索引。

22、select    job,  empnofrom      empwhere     empno = 'RICH'兩種最常見的索引掃描類型是唯一掃描和范圍掃描。在唯一掃描中，數(shù)據(jù)庫知道索引包含一個唯一值列表。在范圍掃描中，數(shù)據(jù)庫將根據(jù)查詢標準從索引中返回多個值。在該示例中，emp_id1和emp_id2索引沒有被創(chuàng)建為唯一索引。Oracle將在檢索它們的值時執(zhí)行范圍掃描。在創(chuàng)建索引時，使用CREATE UNIQUE INDEX命令可以創(chuàng)建唯一索引。

23、在創(chuàng)建主鍵約束或UNIQUE約束時，Oracle將基于指定的列自動創(chuàng)建唯一索引(除非使用DISABLE子句創(chuàng)建約束)。如果創(chuàng)建多列的主鍵，Oracle將創(chuàng)建組合索引，其中的列按照在創(chuàng)建主鍵時指定的順序排列。通過提供每個行的ROWID，類似于EMP_ID1和EMP_ID2的索引為Oracle提供了訪問單行數(shù)據(jù)的能力。ROWID其實就是直接指向單獨行的物理位置的指針。技巧：將Oracle的ROWID硬編碼成特定代碼時，一定要小心謹慎。因為不同版本的ROWID結(jié)構(gòu)會有所不同，而且在將來的版本里可能還會有所改變。我建議不要對ROWID進行硬編碼。2.3  限制索引限制索引是一些沒有經(jīng)驗的開發(fā)

24、人員經(jīng)常犯的錯誤之一。在SQL中有很多陷阱會使一些索引無法使用。后文的各小節(jié)將討論一些常見的問題。Oracle優(yōu)化器在后臺工作，選擇并使用可能最有效的數(shù)據(jù)檢索方法。例如，在許多情況下不需要指定WHERE子句，從而Oracle可以使用索引。如果查詢索引列的MIN或MAX值，Oracle將從索引(而不是表)中檢索該值。同樣，如果對索引列執(zhí)行COUNT函數(shù)，Oracle可以使用索引而不是該列。在下面的小節(jié)中，您將看到WHERE子句的邏輯阻止Oracle使用索引的情況。2.3.1  使用不等于運算符(<>、!=)索引只能用于查找表中已有的數(shù)據(jù)。每當在WHERE子句中使用不等于運算

25、符時，都將無法使用所引用的列的索引。請考慮下文對CUSTOMERS表的查詢，CUSTOMERS表中的CUST_RATING列有一個索引。下面的語句仍會執(zhí)行一次全表掃描(因為大多數(shù)記錄都可以被檢索到)，即使列CUST_RATING上存在索引。select       cust_id,   cust_namefrom         customerswhere       

26、60;cust_rating <> 'aa'當分析表時，Oracle收集表中數(shù)據(jù)分布的相關(guān)統(tǒng)計信息。通過使用這種分析，基于成本的優(yōu)化器就可以決定在WHERE子句中對一些值使用索引，而對其他的值不使用索引。在應用程序開發(fā)和測試期間，應該使用具有代表性的行集，從而可以模擬產(chǎn)品環(huán)境中實際的數(shù)據(jù)值分布。技巧：通過使用CREATE INDEX命令的COMPUTE STATISTICS子句，可以在一個步驟中創(chuàng)建索引并分析它們。也可以從產(chǎn)品數(shù)據(jù)庫中導入統(tǒng)計信息以測試執(zhí)行路徑(參考10gR2 Database Performance Tuning Guide (Part Numbe

27、r B14211-01)的14.5.2節(jié))。2.3.2  使用 IS NULL或IS NOT NULL在WHERE子句中使用 IS NULL或IS NOT NULL同樣會限制索引的使用，因為NULL值并沒有被定義。數(shù)據(jù)庫中沒有值等于NULL值；甚至NULL也不等于NULL。在SQL語句中使用NULL會有很多麻煩。如果被索引的列在某些行中存在NULL值，在索引中就不會有相應的條目(例外情況是位圖索引，這是位圖索引對于NULL搜索通常較為快速的原因)。一般情況下，下面的語句將造成執(zhí)行全表掃描，即使Sal列被索引。select      &

28、#160;empno,  ename,  deptnofrom          empwhere        sal is null;如果要在上面的三列中禁用NULL值，可以在創(chuàng)建或修改表時使用NOT NULL。注意，如果表中已經(jīng)包含數(shù)據(jù)，只有在表中每一行都有非NULL值或是使用ALTER TABLE命令的DEFAULT子句時，才可以為列設置NOT NULL屬性。下面的程序清單顯示了修改EMP表的Sal列以禁用NULL值：

29、alter  table  emp  modify(sal  not  null);注意，如果想嘗試在Sal列中插入一個NULL值，會返回一個錯誤信息。技巧：在創(chuàng)建表時對列指定NOT NULL后會禁用NULL值，而且可以避免與使用NULL值相關(guān)的性能問題。下面的創(chuàng)建表語句為Deptno列提供了一個默認值。如果在執(zhí)行INSERT操作時該列沒有指定的值，就會使用默認值。如果指定了默認值，并且您確實需要使用NULL值，則需要在該列中插入NULL。create table employee(empl_id number(8) not null, fir

30、st_name varchar2(20) not null,last_name varchar2(20) not null, deptno number(4) default 10);insert into employee(empl_id, first_name, last_name)values (8100, 'REGINA', 'NIEMIEC');1 row created.select        *from        

31、;  employee;EMPL_ID FIRST_NAME             LAST_NAME                   DEPTNO- - - -8100   REGINA        

32、          NIEMIEC                         10insert into employeevalues (8200, 'RICH', 'NIEMIEC', NULL);1 row creat

33、ed.select       *from         employee;EMPL_ID FIRST_NAME              LAST_NAME               

34、     DEPTNO-  - - -8100   REGINA                   NIEMIEC                    &#

35、160;  108200   RICH                      NIEMIEC技巧：NULL值通常會限制索引。在創(chuàng)建表時對某一列指定NOT NULL或DEFAULT，對于避免可能出現(xiàn)的性能問題很有幫助。2.3.3  使用函數(shù)如果不使用基于函數(shù)的索引，那么在SQL語句的WHERE子句中對存在索引的列使用函數(shù)時，會使優(yōu)化器忽略掉這些索引。一些常

36、見的函數(shù)，如TRUNC、SUBSTR、TO_DATE、TO_CHAR和INSTR等，都能改變列的值。因此，無法使用已被函數(shù)引用的索引和列。下面的語句會執(zhí)行一次全表掃描，即使hire_date列上存在索引(只要它不是基于函數(shù)的索引)。select        empno, ename, deptnofrom          empwhere         trunc(hir

37、edate) = '01-MAY-01'把上面的語句改成如下所示的語句，這樣就可以通過索引進行查找。select        empno, ename, deptnofrom          empwhere         hiredate > '01-MAY-01'and      

38、      hiredate < (TO_DATE('01-MAY-01') + 0.99999);技巧：通過改變所比較的列上的值，而不用改變列本身，就可以啟用索引用。這樣可避免全表掃描。關(guān)于基于函數(shù)的索引的更多詳情，可查看本章后面的“基于函數(shù)的索引”一節(jié)。2.3.4  比較不匹配的數(shù)據(jù)類型比較不匹配的數(shù)據(jù)類型也是比較難于發(fā)現(xiàn)的性能問題之一。Oracle并不會對那些不匹配的數(shù)據(jù)報錯 事實正好相反。例如，Oracle可以隱式地把VARCHAR2列的數(shù)據(jù)轉(zhuǎn)換成要被比較的數(shù)值型數(shù)據(jù)類型?？紤]如下的示例，其中accoun

39、t_number就是一個VARCHAR2類型。如果Account_Number列使用VARCHAR2數(shù)據(jù)類型，下面的語句將執(zhí)行全表掃描，即便是索引account_number列：select      bank_name, address, city, state, zipfrom         bankswhere        account_number = 990354;Oracle可以自動把WH

40、ERE子句變成to_number(account_number)=990354這樣就限制了索引的使用。這個查詢的EXPLAIN PLAN僅顯示通過“全表掃描”訪問這個表(對編程人員來說通常都很迷惑)。對一些DBA或開發(fā)人員來說，這樣的情況可能很少見，但在很多系統(tǒng)中，數(shù)字型值可以用零填充，然后指定為VARCHAR2類型。前面的語句可以改寫成如下語句，這樣可以正確地對這個字段使用單引號，以使用賬號上的索引。select      bank_name, address, city, state, zipfrom    &

41、#160;   bankswhere       account_number = '000990354'作為選擇，可以定義Account_Number列使用NUMBER數(shù)據(jù)類型，前提是前置的0不是該列的關(guān)鍵信息。技巧：不匹配數(shù)據(jù)類型之間的比較會讓Oracle自動限制索引的使用。即便對這個查詢執(zhí)行EXPLAIN PLAN也不能讓您明白為什么做了一次“全表掃描”。只有了解關(guān)于數(shù)據(jù)類型的知識才能幫助您解決這個問題。2.4  選擇性Oracle根據(jù)查詢和數(shù)據(jù)，提供了多種方法來判斷使用索

42、引的價值。第一個方法就是判斷索引中的唯一鍵或不同鍵的數(shù)量。可以對表或索引進行分析來確定不同鍵的數(shù)量?？梢圆樵僓SER_INDEXES視圖的Distinct_Keys列來研究分析的結(jié)果。比較一下唯一鍵的數(shù)量和表中的行數(shù)(如USER_INDEXES視圖的Num_Rows列所示)，就可以判斷索引的選擇性。選擇性越高，索引返回的行數(shù)就越少，該索引就越好。技巧：索引的選擇性可以幫助基于成本的優(yōu)化器來判斷執(zhí)行路徑。索引的選擇性越高，針對每個不同值返回的行數(shù)也越少。對于組合索引在索引中添加額外的列不會顯著改善選擇性，并且使用額外列的成本會超出收益。2.5  集群因子(Clustering Fact

43、or)集群因子是索引與它所基于的表相比較而得出的有序性度量，它用于檢查在索引訪問之后執(zhí)行的表查找的成本(將集群因子與選擇性相乘即可得到該操作的成本)。集群因子記錄在掃描索引時將讀取的塊數(shù)量。如果使用的索引具有較大的集群因子，則必須訪問更多的表數(shù)據(jù)塊才可以獲得每個索引塊中的行(因為鄰近行位于不同的塊中)。如果集群因子接近于表中的塊數(shù)量，則表示索引適當排序；但是，如果集群因子接近于表中的行數(shù)量，則表示索引沒有適當排序。集群因子的計算簡要介紹如下：(1) 按順序掃描索引。(2) 將當前索引值指向的ROWID的塊部分與以前的索引值進行比較(比較索引中的鄰近行)。(3) 如果ROWID指向不同的TABL

44、E塊，則增加集群因子(對整個索引執(zhí)行該操作)。Clustering_Factor列位于USER_INDEXES視圖中，該列反映了數(shù)據(jù)相對于已索引的列是否顯得有序。如果Clustering_Factor列的值接近于索引中的樹葉塊(leaf block)的數(shù)目，表中的數(shù)據(jù)就是有序的。索引的樹葉塊存儲索引值以及它們指向的ROWID。例如，CUSTOMERS表上Customer_Id列的值可以由序列生成器產(chǎn)生，而且是表CUSTOMERS上的主鍵。Customer_Id的索引的集群因子就有可能非常接近于樹葉塊數(shù)(表示有序)。當往數(shù)據(jù)庫中添加客戶數(shù)據(jù)時，它們就按照序列產(chǎn)生器所產(chǎn)生的序列值有序地存儲在表中。

45、然而，因為整個表的客戶名字排列是隨機的，所以customer_name上的索引會有一個很高的集群因子。集群因子對執(zhí)行范圍掃描的SQL語句有一定的影響。如果集群因子很低(相對于樹葉塊的數(shù)量)，需要讀取的表中塊的數(shù)量就可以減少很多。這樣也增加了相同的數(shù)據(jù)塊已經(jīng)存在于內(nèi)存中的可能性。一個較高的集群因子(相對于樹葉塊的數(shù)量)會增加滿足基于索引列的范圍查詢所需的數(shù)據(jù)塊數(shù)目。技巧：可以使用表中數(shù)據(jù)的集群，這樣可以提高執(zhí)行范圍掃描類型操作的語句的性能。如果要決定如何在語句中使用列，對列進行索引是最好的選擇。2.6  二元高度(binary height)索引的二元高度對把ROWID返回給用戶進程時

46、所要求的I/O數(shù)量起到關(guān)鍵作用。二元高度的每個級別都會增加一個額外的讀取塊，而且由于這些塊不能按順序讀取，它們都要求一個獨立的I/O操作。在圖2-1中，我們檢索一個二元高度為3的索引，這樣會返回一行數(shù)據(jù)給用戶，同時有4個塊被讀?。?個來自索引，一個來自表。隨著索引的二元高度的增加，檢索數(shù)據(jù)所要求的I/O數(shù)量也會隨之增加。在對索引進行分析后，可以通過查詢DBA_INDEXES的blevel列查看它的二元高度：圖2-1  具有二元高度或blevel=3的索引(級別3是樹葉塊駐留的級別)EXECUTE DBMS_STATS.GATHER_INDEX_STATS ('SCOTT

47、9;,'EMP_ID1');PL/SQL procedure successfully completed.select      blevel, index_namefrom        dba_indexeswhere       index_name = 'EMP_ID1'BLEVEL   INDEX_NAME- -0   EMP_ID1技巧：對索引或者表進

48、行分析可以得到索引的二元高度。使用USER_INDEXES視圖里的blevel列可以檢查所有索引的二元高度。二元高度主要隨著表中索引列的非NULL值數(shù)量以及索引列中值的范圍狹窄程度而變化。索引上如果有大量被刪除的行，它的二元高度也會增加。重建索引可能會降低二元高度。雖然這些步驟可以減少針對索引執(zhí)行的I/O數(shù)量，但對性能的改進卻很小。如果一個索引中被刪除的行接近2025，重建索引會降低二元高度以及在一次I/O中所讀取的空閑空間。技巧：一般來說，數(shù)據(jù)庫塊尺寸越大，索引的二元高度就越低。二元高度中的每個額外級別(blevel)在DML操作期間會增加額外的性能成本。2.7  使用直方圖在分析

49、表或索引時，直方圖用于記錄數(shù)據(jù)的分布。通過獲得該信息，基于成本的優(yōu)化器就可以決定使用將返回少量行的索引，而避免使用基于限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構(gòu)建直方圖。構(gòu)造直方圖最主要的原因就是幫助優(yōu)化器在表中數(shù)據(jù)嚴重偏斜時做出更好的規(guī)劃：例如，如果一到兩個值構(gòu)成了表中的大部分數(shù)據(jù)(數(shù)據(jù)偏斜)，相關(guān)的索引就可能無法幫助減少滿足查詢所需的I/O數(shù)量。創(chuàng)建直方圖可以讓基于成本的優(yōu)化器知道何時使用索引才最合適，或何時應該根據(jù)WHERE子句中的值返回表中80的記錄。要創(chuàng)建直方圖，首先要確定好它的尺寸。該尺寸與直方圖所需的存儲桶(bucket)數(shù)相關(guān)。每個存儲桶包含列值

50、和行數(shù)的相關(guān)信息。EXECUTE DBMS_STATS.GATHER_TABLE_STATS('scott','company', METHOD_OPT => 'FOR COLUMNS SIZE 10 company_code');PL/SQL procedure successfully completed.前面的查詢會在COMPANY表上創(chuàng)建一個帶有10個存儲桶的直方圖，如圖2-2所示。圖中COMPANY_CODE列的所有值被分成10個存儲桶。這個例子中有一個占大部分的company_code值(大約80，即1430)。同樣如圖中所示，

51、多數(shù)寬度均衡的存儲桶都只有3行記錄；有一個存儲桶卻有73行記錄。在高度均衡圖中，每個存儲桶有相同數(shù)目的行，多數(shù)存儲桶的終點都是1430，這也反映了數(shù)據(jù)的偏斜分布。Oracle的直方圖是高度均衡的，而不是寬度均衡。也就是說，直方圖里的所有存儲桶都有相同的行數(shù)。存儲桶的起點和終點取決于包含這些值的行數(shù)。寬度均衡的直方圖則著重于確定每個存儲桶的值的范圍，然后統(tǒng)計出這個范圍內(nèi)的行數(shù)。這并不是一個理想的選擇。圖2-2  構(gòu)建在Company_Code字段上的直方圖(具有10個存儲桶)技巧：如果表中的數(shù)據(jù)分布得較不均勻，直方圖會為基于成本的優(yōu)化器提供一個數(shù)據(jù)分布的均衡圖(把數(shù)據(jù)平均分布到各個存儲

52、桶)。在不是很偏斜的列上使用直方圖并不會提高性能。 技巧：默認情況時，Oracle的直方圖會產(chǎn)生75個存儲桶?？梢园裇IZE的值指定在1254之間。2.8  快速全局掃描在索引的快速全局掃描期間，Oracle讀取B樹索引上的所有樹葉塊。這個索引可以按順序讀取，這樣可以一次讀取多個塊。初始化文件中的DB_FILE_MULTIBLOCK _READ_COUNT參數(shù)可以控制同時被讀取的塊的數(shù)目。相比于全表掃描，快速全局掃描通常需要較少的物理I/O，并且允許更快速地處理查詢。如果表查詢中的所有列都被包括在索引里，而索引的前置列并不在WHERE條件中，就可以使用快速全局掃描(必須指定

53、第7章講到的INDEX_FFS提示)。在下面的示例中用到了emp表。它有一個組合索引，包括列empno、ename和deptno。select     empno, ename, deptnofrom        empwhere      deptno = 30;由于SQL語句中的所有列都包括在索引中，因此可以執(zhí)行快速全局掃描。通常在只查詢索引連接鍵列的連接期間執(zhí)行索引快速全局掃描。作為選擇，Oracle可能執(zhí)行索引的跳躍式掃描訪問

54、；優(yōu)化器應該考慮Deptno列的直方圖(如果有可用的直方圖)，并且確定哪個可用的訪問路徑可以產(chǎn)生最低的性能成本。技巧：如果索引相對于表的總體尺寸來說很小，快速全局掃描就可以使應用程序的性能陡增。如果表中有一個包含了大部分列的組合索引，索引可能要比真實的表要大，這樣快速全局掃描反而會降低性能。2.9  跳躍式掃描本章前面的“組合索引”一節(jié)中介紹過，索引跳躍式掃描特性允許優(yōu)化器使用組合索引，即便索引的第一列沒有出現(xiàn)在WHERE子句中。索引跳躍式掃描比全索引掃描快得多，這是因為它只需要執(zhí)行很少量的讀取。例如，下面的查詢顯示了索引全掃描和跳躍掃描之間的區(qū)別。參考第6章，可以更好地了解什么是執(zhí)

55、行計劃或后面的程序清單中列出的統(tǒng)計數(shù)據(jù)。在該程序清單中，EMP5有成百上千的行。跟隨查詢的執(zhí)行，該程序清單顯示了查詢花費的時間、它在數(shù)據(jù)庫中的執(zhí)行路徑，以及顯示處理該查詢所需的邏輯讀數(shù)量(一致的獲取)和物理讀數(shù)量的統(tǒng)計數(shù)據(jù)。create index skip1 on emp5(job,empno);Index created.select count(*)from     emp5where    empno = 7900;Elapsed: 00:00:03.13 (Result is a single rownot displayed)

56、60;Execution Plan0           SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)1        0    SORT (AGGREGATE)2        1      INDEX (F

57、AST FULL SCAN) OF 'SKIP1' (NON-UNIQUE)Statistics6826    consistent gets6819    physical readsselect /*+ index(emp5 skip1) */ count(*)from     emp5where    empno = 7900;Elapsed: 00:00:00.56Execution Plan0     

58、; SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)1    0   SORT (AGGREGATE)2    1     INDEX (SKIP SCAN) OF 'SKIP1' (NON-UNIQUE)Statistics21   consistent gets17   physical reads如同該程序清單所示，第二個選項使用INDEX

59、 (SKIP SCAN)操作讀取索引。該執(zhí)行路徑需要21個邏輯讀，這些邏輯讀又需要17個物理I/O操作。第一個選項執(zhí)行INDEX (FAST FULL SCAN)操作，該操作需要更多數(shù)量的邏輯和物理I/O。為了讓優(yōu)化器選擇跳躍式掃描，可能需要在查詢中使用提示，如同該程序清單所示。提示影響了優(yōu)化器，使其偏向您所指定的執(zhí)行路徑。技巧：對于那些有組合索引的大型表而言，索引跳躍式掃描特性可以提供一個快速訪問，即使索引的第一列沒有在限制條件中使用。2.10  索引的類型下面列出了本節(jié)要討論的索引類型：       B樹索引 

60、      位圖索引       HASH索引       索引組織表索引       反轉(zhuǎn)鍵(reverse key)索引       基于函數(shù)的索引       分區(qū)索引(本地和全局索引)     

61、;  位圖連接索引2.10.1  B樹索引B樹索引在Oracle中是一個通用索引。在創(chuàng)建索引時它就是默認的索引類型。B樹索引可以是一個列的(簡單)索引，也可以是組合/復合(多個列)的索引。B樹索引最多可以包括32列。在圖2-3的例子中，B樹索引位于雇員表的last_name列上。這個索引的二元高度為3；接下來，Oracle會穿過兩個樹枝塊(branch block)，到達包含有ROWID的樹葉塊。在每個樹枝塊中，樹枝行包含鏈中下一個塊的ID號。樹葉塊包含了索引值、ROWID，以及指向前一個和后一個樹葉塊的指針。Oracle可以從兩個方向遍歷這個二叉樹。B樹索引保存了在索引列

62、上有值的每個數(shù)據(jù)行的ROWID值。Oracle不會對索引列上包含NULL值的行進行索引。如果索引是多個列的組合索引，而其中列上包含NULL值，這一行就會處于包含NULL值的索引列中，且將被處理為空(視為NULL)。圖2-3  B樹索引創(chuàng)建過程技巧：索引列的值都存儲在索引中。因此，可以建立一個組合(復合)索引，這些索引可以直接滿足查詢，而不用訪問表。這就不用從表中檢索數(shù)據(jù)，從而減少了I/O量。2.10.2  位圖索引位圖索引非常適合于決策支持系統(tǒng)(Decision Support System，DSS)和數(shù)據(jù)倉庫，它們不應該用于通過事務處理應用程序訪問的表。它們可以使用較少到

63、中等基數(shù)(不同值的數(shù)量)的列訪問非常大的表。盡管位圖索引最多可達30個列，但通常它們都只用于少量的列。例如，您的表可能包含一個稱為Sex的列，它有兩個可能值：男和女。這個基數(shù)只為2，如果用戶頻繁地根據(jù)Sex列的值查詢該表，這就是位圖索引的基列。當一個表內(nèi)包含了多個位圖索引時，您可以體會到位圖索引的真正威力。如果有多個可用的位圖索引，Oracle就可以合并從每個位圖索引得到的結(jié)果集，快速刪除不必要的數(shù)據(jù)。下面的程序清單給出了一個創(chuàng)建位圖索引的例子：create bitmap index dept_idx2_bm on dept (deptno);Index created.技巧：對于有較低基數(shù)的

64、列需要使用位圖索引。性別列就是這樣一個例子，它有兩個可能值：男或女(基數(shù)僅為2)。位圖對于低基數(shù)(少量的不同值)列來說非常快，這是因為索引的尺寸相對于B樹索引來說小了很多。因為這些索引是低基數(shù)的B樹索引，所以非常小，因此您可以經(jīng)常檢索表中超過半數(shù)的行，并且仍使用位圖索引。當大多數(shù)條目不會向位圖添加新的值時，位圖索引在批處理(單用戶)操作中加載表(插入操作)方面通常要比B樹做得好。當多個會話同時向表中插入行時不應該使用位圖索引，在大多數(shù)事務處理應用程序中都會發(fā)生這種情況。位圖索引示例下面來看一個示例表PARTICIPANT，該表包含了來自個人的調(diào)查數(shù)據(jù)。列Age_Code、Income_Leve

65、l、Education_Level和Marital_Status都包括了各自的位圖索引。圖2-4顯示了每個直方圖中的數(shù)據(jù)平衡情況，以及對訪問每個位圖索引的查詢的執(zhí)行路徑。圖中的執(zhí)行路徑顯示了有多少個位圖索引被合并，可以看出性能得到了顯著的提高。圖2-4  位圖索引創(chuàng)建過程如圖2-4所示，優(yōu)化器依次使用4個單獨的位圖索引，這些索引的列在WHERE子句中被引用。每個位圖記錄指針(例如0或1)，用于指示表中的哪些行包含位圖中的已知值。有了這些信息后，Oracle就執(zhí)行BITMAP AND操作以查找將從所有4個位圖中返回哪些行。該值然后被轉(zhuǎn)換為ROWID值，并且查詢繼續(xù)完成剩余的處理工作。注

66、意，所有4個列都有非常低的基數(shù)，使用索引可以非?？焖俚胤祷仄ヅ涞男小＜记桑涸谝粋€查詢中合并多個位圖索引后，可以使性能顯著提高。位圖索引使用固定長度的數(shù)據(jù)類型要比可變長度的數(shù)據(jù)類型好。較大尺寸的塊也會提高對位圖索引的存儲和讀取性能。下面的查詢可顯示索引類型。B樹索引作為NORMAL列出；而位圖索引的類型值為BITMAP。select index_name, index_typefrom user_indexes;技巧：如果要查詢位圖索引列表，可以在USER _INDEXES視圖中查詢index_type列。建議不要在一些聯(lián)機事務處理(OLTP)應用程序中使用位圖索引。B樹索引的索引值中包含ROW

67、ID，這樣Oracle就可以在行級別上鎖定索引。位圖索引存儲為壓縮的索引值，其中包含了一定范圍的ROWID，因此Oracle必須針對一個給定值鎖定所有范圍內(nèi)的ROWID。這種鎖定類型可能在某些DML語句中造成死鎖。SELECT語句不會受到這種鎖定問題的影響。位圖索引有很多限制，如下所示：       基于規(guī)則的優(yōu)化器不會考慮位圖索引。       當執(zhí)行ALTER TABLE語句并修改包含有位圖索引的列時，會使位圖索引失效。    &#

68、160;  位圖索引不包含任何列數(shù)據(jù)，并且不能用于任何類型的完整性檢查。       位圖索引不能被聲明為唯一索引。       位圖索引的最大長度為30。技巧：不要在繁重的OLTP環(huán)境中使用位圖索引2.10.3  HASH索引使用HASH索引必須要使用HASH集群。建立一個集群或HASH集群的同時，也就定義了一個集群鍵。這個鍵告訴Oracle如何在集群上存儲表。在存儲數(shù)據(jù)時，所有與這個集群鍵相關(guān)的行都被存儲在一個數(shù)據(jù)庫塊上。如果數(shù)據(jù)都存儲在同一個數(shù)據(jù)庫塊

69、上，并且將HASH索引作為WHERE子句中的確切匹配，Oracle就可以通過執(zhí)行一個HASH函數(shù)和I/O來訪問數(shù)據(jù) 而通過使用一個二元高度為4的B樹索引來訪問數(shù)據(jù)，則需要在檢索數(shù)據(jù)時使用4個I/O。如圖2-5所示，其中的查詢是一個等價查詢，用于匹配HASH列和確切的值。Oracle可以快速使用該值，基于HASH函數(shù)確定行的物理存儲位置。HASH索引可能是訪問數(shù)據(jù)庫中數(shù)據(jù)的最快方法，但它也有自身的缺點。集群鍵上不同值的數(shù)目必須在創(chuàng)建HASH集群之前就要知道。需要在創(chuàng)建HASH集群的時候指定這個值。低估了集群鍵的不同值的數(shù)字可能會造成集群的沖突(兩個集群的鍵值擁有相同的HASH值)。這種沖突是非常

70、消耗資源的。沖突會造成用來存儲額外行的緩沖溢出，然后造成額外的I/O。如果不同HASH值的數(shù)目已經(jīng)被低估，您就必須在重建這個集群之后改變這個值。ALTER CLUSTER命令不能改變HASH鍵的數(shù)目。HASH集群還可能浪費空間。如果無法確定需要多少空間來維護某個集群鍵上的所有行，就可能造成空間的浪費。如果不能為集群的未來增長分配好附加的空間，HASH集群可能就不是最好的選擇。如果應用程序經(jīng)常在集群表上進行全表掃描，HASH集群可能也不是最好的選擇。由于需要為未來的增長分配好集群的剩余空間量，全表掃描可能非常消耗資源。圖2-5  使用HASH索引的例子在實現(xiàn)HASH集群之前一定要小心。

71、您需要全面地觀察應用程序，保證在實現(xiàn)這個選項之前已經(jīng)了解關(guān)于表和數(shù)據(jù)的大量信息。通常，HASH對于一些包含有序值的靜態(tài)數(shù)據(jù)非常有效。技巧：HASH索引在有限制條件(需要指定一個確定的值而不是一個值范圍)的情況下非常有用。2.10.4  索引組織表索引組織表會把表的存儲結(jié)構(gòu)改成B樹結(jié)構(gòu)，以表的主鍵進行排序。這種特殊的表和其他類型的表一樣，可以在表上執(zhí)行所有的DML和DDL語句。由于表的特殊結(jié)構(gòu)，ROWID并沒有被關(guān)聯(lián)到表的行上。對于一些涉及精確匹配和范圍搜索的語句，索引組織表提供了一種基于鍵的快速數(shù)據(jù)訪問機制?；谥麈I值的UPDATE和DELETE語句的性能也同樣得以提高，這是因為行在

72、物理上有序。由于鍵列的值在表和索引中都沒有重復，存儲所需要的空間也隨之減少。如果不會頻繁地根據(jù)主鍵列查詢數(shù)據(jù)，則需要在索引組織表中的其他列上創(chuàng)建二級索引。不會頻繁根據(jù)主鍵查詢表的應用程序不會了解到使用索引組織表的全部優(yōu)點。對于總是通過對主鍵的精確匹配或范圍掃描進行訪問的表，就需要考慮使用索引組織表。技巧：可以在索引組織表上建立二級索引。2.10.5  反轉(zhuǎn)鍵索引當載入一些有序數(shù)據(jù)時，索引肯定會碰到與I/O相關(guān)的一些瓶頸。在數(shù)據(jù)載入期間，某部分索引和磁盤肯定會比其他部分使用頻繁得多。為了解決這個問題，可以把索引表空間存放在能夠把文件物理分割在多個磁盤上的磁盤體系結(jié)構(gòu)上。為了解決這個問題

73、，Oracle還提供了一種反轉(zhuǎn)鍵索引的方法。如果數(shù)據(jù)以反轉(zhuǎn)鍵索引存儲，這些數(shù)據(jù)的值就會與原先存儲的數(shù)值相反。這樣，數(shù)據(jù)1234、1235和1236就被存儲成4321、5321和6321。結(jié)果就是索引會為每次新插入的行更新不同的索引塊。不能對位圖索引和索引組織表進行反轉(zhuǎn)鍵處理。技巧：如果您的磁盤容量有限，同時還要執(zhí)行大量的有序載入，就可以使用反轉(zhuǎn)鍵索引。不可以將反轉(zhuǎn)鍵索引與位圖索引或索引組織表結(jié)合使用。2.10.6  基于函數(shù)的索引可以在表中創(chuàng)建基于函數(shù)的索引。如果沒有基于函數(shù)的索引，任何在列上執(zhí)行了函數(shù)的查詢都不能使用這個列的索引。例如，下面的查詢就不能使用JOB列上的索引，除非它是基于函數(shù)的索引：select   *from      empwhere    UPPER(job) = 'MGR'下面的查詢使用JOB列上的索引