[Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划
[Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划
谭老师的《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)。
也可以参考我的帖子上其它的回复：
腾讯云开发者
