[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

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

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

| 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

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

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

| 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 |
------------------------------------------------------------------------