Oracle 單表訪問路徑

ORACLE 採用三種訪問表中記錄的方式 :

1 全表掃描（ Full Table Scans, FTS ）

2 透過 ROWID 訪問表（ table access by ROWID ）

3 索引掃描（ Index scan ）

1) 索引範圍掃描(INDEX RANGE SCAN)

2) 索引唯一掃描(INDEX UNIQUE SCAN)

3) 索引全掃描(index full scan)

4) 索引快速掃描(index fast full scan)

5) 索引跳躍式掃描(index skip scan)

一 全表掃描（ Full Table Scans, FTS ）

1 全表掃描優勢：

ORACLE 採用一次讀入多個資料塊 (database block) 的方式最佳化全表掃描，而不是隻讀取一個資料塊，這極大的減少了 I/O 總次數，提高了系統的吞吐量。所以利用多塊讀的方法可以十分高效地實現全表掃描。

需要注意的是隻有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個資料塊只被讀一次。

2 使用 FTS 的前提條件：

在較大的表上不建議使用全表掃描，除非取出資料的比較多，超過總量的5% -- 10%，或你想使用並行查詢功能時。

3 查詢資料庫中哪些執行過哪些全表掃描的 SQL

1) 查詢某一使用者下執行過的全表掃描的地址

select * from v$sql_plan v

where v.operation = 'TABLE ACCESS'

and v.OPTIONS = 'FULL'

and v.OBJECT_OWNER = 'SCOTT'; -- 指定使用者下

2) 透過 hash_value 找出對應的 SQL

select * from v$sql where hash_value='2475760909';

或者

select * from v$sqlarea where hash_value='2475760909';

4 全表掃描例項（ TABLE ACCESS FULL ）

SQL> conn scott/tiger

SQL> create table t1 as select level as id,sysdate as hiredate,level*10000 as sal from dual connect by level<=100000;

表已建立。

SQL> set autotrace traceonly

SQL> set linesize 200

SQL> select * from t1 where id=23;

執行計劃

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

Plan hash value: 3617692013

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

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

| 0 | SELECT STATEMENT | | 4 | 140 | 77 (4)| 00:00:01 |

|* 1 | TABLE ACCESS FULL | T1 | 4 | 140 | 77 (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID"=23)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

5 recursive calls

0 db block gets

393 consistent gets

0 physical reads

0 redo size

524 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

從查詢計劃我們可以看到所採用的查詢方式是“ TABLE ACCESS FULL ”，這就是全表掃描。也正是因為採用全表掃描，所以 consistent gets 會很大。

二透過 ROWID 訪問表（ table access by ROWID ）

原理：

使用 ROWID 進行查詢的前提是我們明確知道了一個正確的 ROWID ，然後透過這個 ROWID 進行查詢。所以這裡所提到的所有 ROWID 必須是真實存在的，否則會報錯。

ROWID 指出了該行所在的資料檔案、資料塊以及行在該塊中的位置，所以透過 ROWID 來存取資料可以快速定位到目標資料上，是 Oracle 存取單行資料的最快方法。

為了透過 ROWID 存取表， Oracle 首先要獲取被選擇行的 ROWID ，或者從語句的 WHERE 子句中得到，或者透過表的一個或多個索引的索引掃描得到。 Oracle 然後以得到的 ROWID 為依據定位每個被選擇的行。

下面給出使用 rowid 訪問表的例項。

1) 單個 rowid 的情形

SQL> select a.*,rowid from t1 a where id=23;

ID HIREDATE SAL ROWID

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

23 12-8 月 -15 230000 AAAnqVAAEAAEH9sAAW

SQL> select * from t1 where rowid=' AAAnqVAAEAAEH9sAAW ';

執行計劃

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

Plan hash value: 487051824

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

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

| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |

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

統計資訊

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

0 recursive calls

0 db block gets

1 consistent gets

0 physical reads

0 redo size

524 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

查詢計劃中說明該查詢是的表訪問方式是” TABLE ACCESS BY USER ROWID “，也就是直接透過 USER ROWID 來訪問，這也是為什麼只需要 1 次 consistent gets 的原因。

2) 多個 rowid 的傾向

SQL> select a.*,rowid from t1 a where id in(23,999);

ID HIREDATE SAL ROWID

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

23 12-8 月 -15 230000 AAAnqVAAEAAEH9sAAW

999 12-8 月 -15 9990000 AAAnqVAAEAAEH9uAEs

SQL> select * from t1 where rowid in('AAAnqVAAEAAEH9sAAW','AAAnqVAAEAAEH9uAEs');

執行計劃

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

Plan hash value: 379112920

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

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

| 0 | SELECT STATEMENT | | 1 | 47 | 1 (0)| 00:00:01 |

| 1 | INLIST ITERATOR | | | | | |

| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 47 | 1 (0)| 00:00:01 |

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

Note

-----

- dynamic sampling used for this statement

統計資訊

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

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

568 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

查詢計劃分析：

上面的執行計劃中出現了 INLIST ITERATOR ，即 INLIST 迭代，該操作說明其子操作多次重複時，會出現該操作。

由於我們使用了 in 運算，且傳遞了 2 個 rowid ，故出現 INLIST 迭代操作，迭代操作意味著條件中的物件列表一個接一個的迭代傳遞給子操作。

Inlist iterator ：

Inlist iterator 操作是 oracle 為 inlist 查詢提供的一種解決方案：即查詢謂詞為：

where indexed_column in (:1, :2, :3)

對於這種查詢， oracle 一般有 兩種解決方法 ： inlist iterator 和 concatenation .

1)concatenation( 級聯 )

對於 concatenation( 級聯 ), 就是為 inlist 中的每個值執行一次索引查詢，然後將結果集採用 union all 的方式合併。

2)inlist iterator(inlist 迭代 )

而 inlist iterator,oracle 的解釋是：

“ The INLIST ITERATOR operation iterates over the next operation in the plan for each value in the IN-list predicate. ”。

按 oracle 的說法是這種演算法更高效。實際測試中也會發現 inlist iterator 操作的 cpu cost 和 buffer reads 更低。

其他：

a> 若希望禁用 inlist iterator, 可設定 10157 等待事件：

alter session set events ‘10157 trace name context forever, level 1’;

b> use_concat 也可用來禁用 inlist iterator 操作，而強制其使用 concatenation 操作。但實際中會發現該 hint 常常不能如預期地生效，所以使用後還要注意驗證結果是否如預期發生了變化。

三 索引掃描（ Index scan ）

我們先透過 index 查詢到資料對應的 rowid 值 ( 對於非唯一索引可能返回多個 rowid 值 ) ，

然後根據 rowid 直接從表中得到具體的資料，這種查詢方式稱為索引掃描或索引查詢 (index lookup) 。

一個 rowid 唯一的表示一行資料，該行對應的資料塊是透過一次 i/o 得到的，在此情況下該次 i/o 只會讀取一個資料庫塊。

在索引中，除了儲存每個索引的值外，索引還儲存具有此值的行對應的 ROWID 值。

索引掃描可以由 2 步組成：

(1) 掃描索引得到對應的 rowid 值。

(2) 透過找到的 rowid 從表中讀出具體的資料。

根據索引的型別與 where 限制條件的不同，有 4 種型別的索引掃描。

1 索引範圍掃描 (INDEX RANGE SCAN)

使用 index rang scan 的 3 種情況：

1)在唯一索引列上使用了range運算子(> < <> >= <= between)

2)在組合索引上，只使用部分列進行查詢，導致查詢出多行

3)對非唯一索引列上進行的任何查詢。

INDEX RANGE SCAN 是範圍掃描，舉個例子，有 1 到 100 ，分 5 個範圍，要查詢 45 就要到第 3 的範圍裡查，這樣會很快

Index Unique Scan 和 Index Range Scan 在 B Tree 上的搜尋路徑是一樣的，只是 Index Unique Scan 在找到應該含有要找的 Index Key 的 block 後便停止 了搜尋，因為該鍵是唯一的而 Index Range Scan 還要循著指標繼續找下去 直到條件不滿足時。並且， Index Range Scan 只是索引上的查詢，與是否掃描表沒有關係。 如果所選擇的列都在 index 上就不用去scan table(因為直接透過索引就能得到想要的資料)，如果掃描到表 , 必然還有一個 table access by rowed( 回表 ) ，正如上例所展示的，透過 index range scan 訪問的表可以透過按照索引順序重新建立表來提高效率；

原因有二：

1) 如果你只讀一部分資料，假設 20% ，如果表資料順序混亂，實際上可能把整個表都讀進來了，如果表順序和索引一致，則只需要讀進 20% 的表的 block 就夠了。這是簡單情況。

2) 複雜情況下，順序混亂的時候 block 可能在整個查詢的不同時間點多次反覆訪問 , 當再次要訪問這個塊的時候說不定已經被換出去了，或者被修改過了，那代價更大 , 而如果順序一樣，對同一個 block 的訪問集中在一段連續的很短的時間內，變數少，不會對同一個 block 產生多次 IO 。

-- 為 t1 表中的 id 欄位建立索引 IX_T1_ID

SQL> show user

USER 為 "SCOTT"

SQL> create index ix_t1_id on t1(id);

索引已建立。

-- 查詢 ID 在 99 到 999 之間的記錄

SQL> select * from t1 where id between 99 and 999;

已選擇 901 行。

執行計劃

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

Plan hash value: 897798478

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

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

| 0 | SELECT STATEMENT | | 901 | 31535 | 6 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 901 | 31535 | 6 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IX_T1_ID | 901 | | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID">=99 AND "ID"<=999)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

0 recursive calls

0 db block gets

127 consistent gets

0 physical reads

0 redo size

27210 bytes sent via SQL*Net to client

1045 bytes received via SQL*Net from client

62 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

901 rows processed

SQL> select * from t1 where id=9999;

執行計劃

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

Plan hash value: 897798478

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 35 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IX_T1_ID | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=9999)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

9 recursive calls

0 db block gets

79 consistent gets

1 physical reads

0 redo size

530 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

2 索引唯一掃描 (INDEX UNIQUE SCAN)

透過 唯一索引 查詢一個數值經常返回單個 ROWID 。如果存在 UNIQUE 或 PRIMARY KEY 約束 ( 它保證了語句只存取單行 ) 的話， Oracle 經常實現唯一性掃描。

SQL> show user

USER 為 "SCOTT"

SQL> drop index ix_t1_id;

索引已刪除。

SQL> alter table t1 add constraint pk_t1_id primary key(id);

表已更改。

SQL> select * from t1 where id=9999;

執行計劃

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

Plan hash value: 3736029472

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 35 | 2 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_T1_ID | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("ID"=9999)

統計資訊

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

1 recursive calls

0 db block gets

3 consistent gets

1 physical reads

0 redo size

434 bytes sent via SQL*Net to client

374 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

3 索引全掃描 (index full scan)(ifs)

Full Index Scan

In a full index scan, the database reads the entire index in order.

A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified.

A full scan can eliminate sorting because the data is ordered by index key.

原理：

1)ORACLE 定位到索引的ROOT BLOCK

2) 然後到BRANCH BLOCK （如果有的話）

3) 再定位到第一個LEAF BLOCK, 然後根據LEAF BLOCK 的雙向連結串列順序讀取。

它所讀取的塊都是有順序的，也是經過排序的，不能並行訪問索引。

關於 INDEX FULL SCAN 和 INDEX FAST FULL SCAN 的區別在於：

前者在對索引進行掃描的時候會考慮大索引的結構，而且會按照索引的排序，

而後者則不會， INDEX FAST FULL SCAN 不會去掃描根塊和分支塊，對索引像訪問堆表一樣訪問，所以這兩個掃描方式用在不同的場合。

如果存在 ORDER BY 這樣的排序， INDEX FULL SCAN 是合適的，如果不需要排序，那 INDEXFAST FULL SCAN 效率是更高的。

SQL> SELECT id from t1 where id is not null order by id;

已選擇 100000 行。

執行計劃

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

Plan hash value: 333785393

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

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

| 0 | SELECT STATEMENT | | 111K| 1413K| 239 (2)| 00:00:03 |

|* 1 | INDEX FULL SCAN | IX_T1_ID | 111K| 1413K| 239 (2)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID" IS NOT NULL)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

4 recursive calls

0 db block gets

6948 consistent gets

0 physical reads

0 redo size

1455961 bytes sent via SQL*Net to client

73711 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100000 rows processed

Hint

SELECT /*+ index_ifs(t1 ix_t1_id) */ id from t1 where id is not null order by id;

4 索引快速掃描 (index fast full scan)(ffs)

Fast Full Index Scan

A fast full index scan is a full index scan in which the database reads the index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint.

A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.

The database cannot perform fast full index scans of bitmap indexes.

原理：

從段頭開始，讀取包含點陣圖塊，ROOT BLOCK ，所有的BRANCH BLOCK ，LEAF BLOCK ，

讀取的順序完全有物理儲存位置決定，並採取多塊讀，每次讀取DB_FILE_MULTIBLOCK_READ_COUNT 個塊。

查詢某個表記錄總數的時候，往往基於PRIMARY KEY 的INDEX FAST FULL SCAN 是最有效的。

2 、強制使用 INDEX FAST FULL SCAN

SELECT /*+ index_ffs(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL order by n1;

select /*+ index_ffs(t1 ix_t1_id) */ id,hiredate from t1 where id=5000;

SQL> select id from t1 where id>=5000;

已選擇 95001 行。

執行計劃

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

Plan hash value: 923087333

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

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

| 0 | SELECT STATEMENT | | 105K| 1344K| 56 (6)| 00:00:01 |

|* 1 | INDEX FAST FULL SCAN | IX_T1_ID | 105K| 1344K| 56 (6)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("ID">=5000)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

9 recursive calls

0 db block gets

6628 consistent gets

0 physical reads

0 redo size

1387824 bytes sent via SQL*Net to client

70048 bytes received via SQL*Net from client

6335 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

95001 rows processed

5 索引跳躍式掃描 (index skip scan)

Index Skip Scans

Index skip scans improve index scans by no nprefix columns. Often, scanning index blocks is faster than sc anning table data blocks.

Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageo us if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

當查詢可以透過組合索引得到結果，而且返回結果很少，並且 where 條件中沒有包含索引引導列的時候，可能執行 index skip scan 。

索引跳躍式掃描發生的條件 :

1) 必須是組合索引。

2) 引導列沒有出現在where 條件中。

1. 索引跳躍式掃描適合於組合索引，比如（ gender ， age ）

2. 當根據組合索引的第一個列 gender （ leading 列）做查詢時，通常是可以用的到索引的

3. 當你想使用第二個列 age 作為篩選條件時， oracle 可以使用索引跳躍式掃描

4. 跳躍式掃描適合於第一個列值重複比較多，第二個列唯一值比較多的情況

hint

SQL> select /*+ index_ss(t1,ix_type_id) */ object_id from t1 where object_id=10;

執行計劃

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

Plan hash value: 2199569833

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

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

| 0 | SELECT STATEMENT | | 8 | 104 | 232 (0)| 00:00:03 |

|* 1 | INDEX SKIP SCAN | IX_TYPE_ID | 8 | 104 | 232 (0)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_ID"=10)

filter("OBJECT_ID"=10)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

0 recursive calls

0 db block gets

21 consistent gets

0 physical reads

0 redo size

409 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

全表掃描

SQL> select /*+ full (t1)*/ object_id from t1 where object_id=10;

執行計劃

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

Plan hash value: 3617692013

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

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

| 0 | SELECT STATEMENT | | 8 | 104 | 168 (2)| 00:00:03 |

|* 1 | TABLE ACCESS FULL | T1 | 8 | 104 | 168 (2)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=10)

Note

-----

- dynamic sampling used for this statement

統計資訊

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

0 recursive calls

0 db block gets

739 consistent gets

0 physical reads

0 redo size

409 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

ORACLE 官方說，在前導列唯一值較少的情況下，才會用到 index skip can 。這個其實好理解，就是入口要少。

ORACLE 也承認skip scan 沒有直接索引查詢快，但可以這樣說，相比於整個表掃描(table scan) ，索引跳躍式掃描的速度要快得多。

HINT 總結：

1. 全表掃描 hint full(table_name)

2. 索引 hint index(table_name index_name)

3. 索引快速掃描 hint index_ffs(table_name index_name)

FAST FULL SCN 。

4. 索引跳躍掃描 hint index_ss(table_name index_name)

INDEX SKIP SCAN

5. 表關聯 hint user_nl(table_name1 table_name2)

6. 表關聯 hint use_hash(table_name1 table_name2)

7. 表關聯 hint user_merge(table_name1 table_name2)

8. 表順序 hint leading(table_name1 table_name2)

9. 資料載入 hint append()

10. 資料返回模式 hint first_rows

使用hint 時切記檢視錶名是否使用了別名，如果使用了別名，記得要在hint 中也要使用別名，否則hint 是沒有作用的（切記）

參考： http://www.cnblogs.com/xwdreamer/archive/2012/06/13/2547825.html

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 單表訪問路徑

Oracle 單表訪問路徑

相關文章