【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃
舉例：
create table t(id int);
create index t_idx on t(id);
SQL> select /*+ index(t t_idx) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 4075463224
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 3 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
這裡忽略了HINT，解釋是：因為我們要對錶的記錄求總數，我們建立的索引並沒有指定索引欄位T不能為空，所以如果CBO選擇在索引上做COUNT，當索引欄位上有空值時，COUNT的結果必然不準確。
SQL> select /*+ index(t, t_idx) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 4235589928
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| T_IDX | 3 | 39 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
這裡用到了HINT，解釋是：因為我們只是對X欄位做COUNT，id欄位是索引欄位，這個動作相當於COUNT索引上的所有id的鍵值，這個結果和對錶上id欄位做COUNT是一樣的。
這點我覺得不是很準確。
如果是唯一性索引，則count(*)==count(索引欄位)。
如果不是非唯一索引，則列中NULL值不會存入索引，因此count(*)>=count(索引欄位)。
再做個實驗：
CREATE TABLE TBL_SMALL
(ID NUMBER,
NAME VARCHAR2(5)
);
SQL> create index t_s_idx on tbl_small(id);
create table tbl_big as select rownum id, object_name name from dba_objects where rownum<1000;
SQL> create index t_b_idx on tbl_big(id);
insert into tbl_big values('', '');
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SQL> select * from tbl_small;
ID NAME
---------- -----
2 b
1 a
SQL> select count(*) from tbl_small;
COUNT(*)
----------
3
SQL> select count(id) from tbl_small;
COUNT(ID)
----------
2
SQL> select count(*) from tbl_big;
COUNT(*)
----------
1000
SQL> select count(id) from tbl_big;
COUNT(ID)
----------
999
SQL> set autot trace exp
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TBL_SMALL表：
SQL> select count(*) from tbl_small;
Execution Plan
----------------------------------------------------------
Plan hash value: 1452584873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TBL_SMALL | 3 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select count(id) from tbl_small;
Execution Plan
----------------------------------------------------------
Plan hash value: 1539159417
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| T_S_IDX | 3 | 39 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_small, t_s_idx) */ count(*) from tbl_small;
Execution Plan
----------------------------------------------------------
Plan hash value: 1452584873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TBL_SMALL | 3 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_small, t_s_idx) */ count(id) from tbl_small;
Execution Plan
----------------------------------------------------------
Plan hash value: 1539159417
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| T_S_IDX | 3 | 39 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
TBL_BIG表：
SQL> select count(*) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 475686685
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TBL_BIG | 1000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select count(id) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 2252048431
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX FAST FULL SCAN| T_B_IDX | 1000 | 13000 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_big, t_b_idx) */ count(*) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 475686685
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TBL_BIG | 1000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ index(tbl_big, t_b_idx) */ count(id) from tbl_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 1004523789
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| T_B_IDX | 1000 | 13000 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
不同的點：
小表對id欄位count，無論是否使用hint，都是INDEX FULL SCAN。
大表對id欄位count，不帶hint，是INDEX FAST FULL SCAN，對id欄位count帶hint，是INDEX FULL SCAN。(這裡我感覺不帶hint，CBO還能選擇FFS的方式可能更優，但如果帶了hint，則強制使用並不最優的FS)。
也可以參考我的帖子上其它的回覆：
http://www.itpub.net/thread-1794313-1-1.html
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7192724/viewspace-767153/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用hint改變執行計劃
- 使用Oracle Hint提示來更改執行計劃Oracle
- Oracle 索引和執行計劃Oracle索引
- 使用Oracle Hint提示來更改執行計劃 ZTOracle
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- mysql索引和執行計劃MySql索引
- Oracle訪問索引的執行計劃（一）Oracle索引
- Oracle訪問索引的執行計劃（二）Oracle索引
- Oracle訪問索引的執行計劃（三）Oracle索引
- Oracle訪問索引的執行計劃（四）Oracle索引
- Oracle訪問索引的執行計劃（五）Oracle索引
- 通過內部的hint來控制執行計劃
- 使用no_merge結合其它hint完全控制Oracle執行計劃Oracle
- mysql 執行計劃索引分析筆記MySql索引筆記
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 建立索引調整sql的執行計劃索引SQL
- 關於索引的執行計劃記載索引
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- 索引及排序對執行計劃的影響索引排序
- ORACLE執行計劃Oracle
- oracle執行計劃------未走索引，隱式轉換的坑Oracle索引
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 建了索引執行計劃會有區別了索引
- 理解索引：MySQL執行計劃詳細介紹索引MySql
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- Oracle索引HINT的使用Oracle索引
- 執行計劃-1：獲取執行計劃
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- clustering factor索引聚簇因子和執行計劃索引
- 分割槽索引（Partition Index）與SQL執行計劃（中）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（下）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（上）索引IndexSQL