Oracle效能分析5：資料訪問方式之索引結構和掃描方式介紹，oracle索引

Oracle效能分析5：資料訪問方式之索引結構和掃描方式介紹，oracle索引

上篇文章講述了全掃描，這篇文章將介紹索引的結構和掃描方式，在後面將開始講述每一種掃描方式。
當Oracle通過索引檢索具體的一列或多列的列值時，就會執行索引掃描。首先我們來看看索引節點包含的資料。

索引節點包含的資料

索引可以被建立在表的單列或者多列上，索引中包含了這些列的值、rowid和一些其它資訊，我們關心的只有列值和rowid。由於索引帶有列值，應此如果你的SQL語句只涉及到索引的列，那麼Oracle就只從索引本身檢索列值，而不需要訪問表。如果查詢涉及到索引列以外的列，Oracle就需要使用rowid來訪問表。
下面是一個rowid的例子：

AAAN0+AABAAAPIqABj

rowid中包含了檔案編號、資料區塊編號和行號，通過下面的SQL我們可以將rowid分解為可讀的具體的資訊，使用先前建立的表T2：

select t.rowid,       (select file_name          from dba_data_files         where file_id =               dbms_rowid.rowid_to_absolute_fno(t.rowid, user, 'T2')) file_name,       dbms_rowid.rowid_block_number(t.rowid) bokc_no,       dbms_rowid.rowid_row_number(t.rowid) row_no  from t2 t

執行後得到結果：

ROWIDFILE_NAMEBOKC_NOROW_NOAAAN0+AABAAAPIqAAAE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619940AAAN0+AABAAAPIqAABE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619941AAAN0+AABAAAPIqAACE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619942......AAAN0+AABAAAPIqAJdE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994605AAAN0+AABAAAPIqAJeE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994606AAAN0+AABAAAPIqAJfE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994607AAAN0+AABAAAPIqAJgE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994608AAAN0+AABAAAPIqAJhE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994609AAAN0+AABAAAPIqAJiE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994610AAAN0+AABAAAPIqAJjE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61994611AAAN0+AABAAAPIrAAAE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619950AAAN0+AABAAAPIrAABE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619951AAAN0+AABAAAPIrAACE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619952AAAN0+AABAAAPIrAADE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619953AAAN0+AABAAAPIrAAEE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619954......AAAN0+AABAAAPIrAJVE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995597AAAN0+AABAAAPIrAJWE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995598AAAN0+AABAAAPIrAJXE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995599AAAN0+AABAAAPIrAJYE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995600AAAN0+AABAAAPIrAJZE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995601AAAN0+AABAAAPIrAJaE:\ORACLE\ORADATA\LY\SYSTEM01.DBF61995602AAAN0+AABAAAPIsAAAE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619960AAAN0+AABAAAPIsAABE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619961AAAN0+AABAAAPIsAACE:\ORACLE\ORADATA\LY\SYSTEM01.DBF619962......

這裡將得到表T2中所有行的具體位置，包括所在的檔案、資料區塊號和塊內的行號。我們可以看到資料的分布情況。
需要注意的是dbms_rowid.rowid_to_absolute_fno函數，定義如下：

function dbms_rowid.rowid_to_absolute_fno(rowid in rowid,schema_name in varchar2,object_name in varchar2)return number

 1）rowid：rowid；
 2）schema_name：使用者名稱，這裡是目前使用者（user）
 3）object_name：對象名，這裡是T2
 
那麼，通過上面的描述我們就可以得到通過索引掃描尋找資料的步驟：
 1）得到索引的資料區塊，得到索引列和rowid；
 2）如果查詢只涉及到索引列，則查詢結束；
 3）否則通過rowid找到資料區塊，並通過行號定位到資料。

索引結構和索引掃描類型介紹

在這裡只討論B-樹索引，B-樹索引是一個樹狀結構。表剛建立時是一個空白，對應的索引將只存在一個根節點，索引高度是1，另外索引還有一個blevel的統計資訊用來表示一個索引中的分支層級數，該值為0，通過下面的查詢可以得到：

select index_name,blevel from user_indexes where index_name =upper( 'index_name');

隨著新的資料插入到表中，新的索引條目將被增加到塊中，直到塊滿，這時，Oracle將會分配兩個新的索引塊並將索引條目加入這兩個新的葉子塊中，先前的索引塊將變為指向新索引塊的指標，這個指標包含指向新索引塊的相對資料區塊地址（Relative Block Address,RBA）和相關葉子塊中最低索引值。到這時，索引的高度將變為2，blevel值將變為1。
隨著表中資料的繼續增長，索引塊會進一步分裂，高度會繼續增長，最終形成一個樹狀結構：

瞭解了索引的結構，很容易就能理解索引掃描，索引掃描有很多種不同的類型，但都必須遍曆索引結構以搜尋到匹配的葉子節點。首先通過一次單塊讀來擷取索引的根塊，然後通過多次的單塊讀來擷取路徑節點的塊，直到葉子節點所在的塊（匹配的塊），從匹配的葉子節點中擷取資料的rowid，在通過rowid使用單塊讀擷取一行資料，因此，如果索引結構的高度為4，則查詢一行資料需要讀取5個塊，4個索引塊和1個表資料區塊。
索引掃描類型包括：索引範圍掃描、索引唯一掃描、索引全掃描、索引跳躍掃描和索引快速全掃描。在後面將詳細講述每一種掃描方式的特點和應用範圍。

oracle中的資料結構是那樣的

基本資料結構

表

表是關聯式資料庫中的一個基本資料結構。表就是行的集合。每行（row）包含一個或多個列。

從Oracle8企業版以後，就提供了分區選件，它允許將表和索引進行分區。利用分區，Oracle可從以下兩方面改善效能：

。Oracle不用去訪問那些不滿足查詢條件的分區

。如果分區中所有資料都滿足查詢條件，那麼Oracle將選擇全部資料而不需要對每行均進行字句檢查。

視圖

視圖（view）是Oracle中的一種由SQL語句構造的資料結構。SQL語句儲存在資料庫中，在查詢中使用一個視圖時，所儲存的查詢將得以執行，並向使用者返回基表（base table）中的資料。

視圖不包含資料，而是表示一些方法可以查看查詢所指定的基表資料。

視圖有以下幾種用途：

。簡化對多個表資料的訪問

。可以保證表中資料的安全（如，建立包含WHERE子句的視圖就可以限制訪問表中的資料）

。將應用與表中某些特定的結構分離

視圖建立在基表集合的基礎之上，基表包括Oracle資料庫中的事實表或者其他視圖。如果視圖中的任何一個基表進行修改，那麼該視圖將無法繼續使用它們，因此視圖本身也無法再使用。

索引

索引（index）是用來加快訪問資料庫中記錄速度的一種資料結構。一個索引與一個特定的表相關，而且包含該表的一個或多個列的資料。

建立索引的SQL基本文法：

CREAT INDEX emp_idx1 on emp （ename，job）；

其中，emp_idx1時索引名，emp是建立索引的表，ename和job時構成索引的列值。

除了索引資料以外，索引項目中還為其相關行儲存了ROWID.ROWID是擷取資料庫行的最快方式，因此隨後資料庫行的擷取都是以這種最佳方式來完成。

Oracle中使用的4中類型的索引結構：標準B*-樹索引、反向鍵索引、位元影像索引以及Oracle8i引入的基於函數的索引。Oracle使你可以對錶中的資料進行彙總，從而改善效能。

其它資料結構

序列（Sequence）

在多使用者資料庫中經常出現的大問題，就是很難為鍵或標識符提供唯一的序號。在這種情況下，Oracle允許建立序列對象。

序號可以用名字，一個遞增值或有關序列的其他一些資訊。序列獨立於任何錶，因此多個表可以使用同樣的序號。

同義字（Synonym）

所有的Oracle資料庫的資料結構都儲存在一個特定的模式（schema）。模式是和一個特定的使用者名稱相關聯的，所有對象都通過帶有對象名的模式名得到引用。

例如，模式DEMO中有一個表名為EMP.如果想參考資料表EMP，那麼應該通過完整名DEMO.EMP來引用。如果沒有提供特定的模式名，那麼Oracle假定該結構處於目前使用者名的模式中。

叢集（Cluster）

叢集是一種能夠改善擷取效能的資料結構。叢集和索引一樣，不會影響表的邏輯視圖。

散列叢集（Hash Cluster）

資料設計

約束

約束（constraint）強制資料庫中某些資料的完整性。當給某列增加一個約束，Oracle自動確保不滿足此約束的資料是絕不能被接受的。

約束可以在建立或增加包括某列（通過關鍵字）的表時與列相關聯，或者在表建立後通過SQL命令ALTER TABLE來實現......餘下全文>>
 
Oracle中不使用索引的幾種情況分析

Oracle不使用b*tree索引的情況大致如下1：where條件中和null比較可能導致不使用索引2：count，sum，ave，max，min等聚集操作時可能導致不使用索引3：顯示或者隱式的函數轉換導致不使用索引4：在cbo模式下，統計資訊過於陳舊導致不使用索引5：複合式索引中沒有使用前置列導致沒有使用索引6：訪問的資料量超過一定的比例導致不使用索引下面就其中的幾點做一些說明一：Null可以使用索引嗎一般情況下，where條件中和null比較將會導致fulltablescan，實際上，如果table中索引建列的值都為null，那麼該行在索引（此處指b*tree，位元影像索引和聚簇索引可以有空值）中就不會存在，因此oracle為了保證查詢結構的準確性，就會用full table scan代替index scan，這樣理解，不走索引也就在情理之中。當然，如果某個索引列上有定義為not null，在這種情況下，不存在所有索引列都為空白的情況，所以此種情況下，是可以走index scan的，因此，對於where條件中含有類似is null，=null的情況，是否走索引，還是要看索引建中是否有某個列定義為not null。具體實驗如下：SQL> create table t(x char(3),y char(5));SQL> insert into t(x,y) values ('001','xxxxx');SQL> insert into t(x,y) values ('002',null);SQL> insert into t(x,y) values (null,'yyyyy');SQL> insert into t(x,y) values (null,null);SQL> commit;SQL> create unique index t_idx on t(x,y);SQL> analyze table t compute statistics for table for all indexes;SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t_idx'); BLEVEL LEAF_BLOCKS NUM_ROWS---------- ----------- ---------- 0 1 3isnert四條記錄，但索引只儲存3條，最後一條沒有儲存在索引中SQL> set autotrace traceonly explain;SQL> select * from t where x is null;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8) 1 0 TABLE ACCESS (FULL)OF 'T' (Cost=2 Card=1 Bytes=8) SQL> create table t1(x char(3),y char(5) not null);SQL> insert into t1(x,y) values ('001','xx......餘下全文>>