ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】-【索引-HINT,执行计划】-带HINT的索引执行计划

【Oracle】-【索引-HINT,执行计划】-带HINT的索引执行计划

原创 Linux操作系统 作者:bisal 时间:2013-07-26 10:00:24 0 删除 编辑
谭老师的《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字段做COUNTid字段是索引字段,这个动作相当于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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2622739