【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃

bisal發表於2013-07-26
Oracle索引
譚老師的《Oracle 10g 效能分析與優化思路》第六章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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章