[Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划
[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)。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。