ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】-【SORT AGGREGATE】-count与索引

【Oracle】-【SORT AGGREGATE】-count与索引

原创 Linux操作系统 作者:bisal 时间:2013-07-26 10:03:40 0 删除 编辑

Oracle10g:
create table t_count as select * from dba_objects;
create index t_count_i on t_count(object_id):


分别用:
select count(*) from t_count;
select count(object_id) from t_count;
select count(object_name) from t_count;
查看是否使用索引对count查询性能起到作用。


它们的执行计划:
SQL> select count(*) from t_count;

Execution Plan
----------------------------------------------------------
Plan hash value: 2197880521
----------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |           1 |          39   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |           1 |                  |             |
|   2 |   TABLE ACCESS FULL| T_COUNT | 12028 |          39   (0)| 00:00:01 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


SQL> select count(object_name) from t_count;



Execution Plan
----------------------------------------------------------
Plan hash value: 2197880521
------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |     19 |          39   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |     19 |                  |          |
|   2 |   TABLE ACCESS FULL| T_COUNT  | 10976 |    203K|          39   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        142  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select count(object_id) from t_count;

Execution Plan
----------------------------------------------------------
Plan hash value: 3107438994

--------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     5 |        8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     5 |               |
|   2 |   INDEX FAST FULL SCAN| T_COUNT_I | 10976 | 54880 |        8   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         29  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


它们的trace文件:
select count(*)
from
t_count

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        142          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        144          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=142 pr=0 pw=0 time=1848 us)
  10976   TABLE ACCESS FULL T_COUNT (cr=142 pr=0 pw=0 time=55 us)


elect count(object_id)
from
t_count

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00         24         29          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00         24         31          0           1


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25
     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=29 pr=24 pw=0 time=2648 us)
  10976   INDEX FAST FULL SCAN T_COUNT_I (cr=29 pr=24 pw=0 time=455 us)(object id 12404)


select count(object_name)
from
t_count

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        142          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        143          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 25

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=142 pr=0 pw=0 time=2037 us)
  10976   TABLE ACCESS FULL T_COUNT (cr=142 pr=0 pw=0 time=153 us)


可以得出:
1、使用count(索引)确实可以用INDEX FAST FULL SCAN,不用TABLE ACCESS FULL。
2、注意到这里SORT AGGREGATE,看似好像用到了排序,但count不需要排序啊?实际再看,它的COST是空的,实际没有任何消耗。不是有sort就会排序。

SORT AGGREGATE做为sort的option之一比较特殊,它并不做sort。
SORT AGGREGATE作用于所有的data set上,用于aggregate function,例如sum, count, avg, min, max。
如果aggregate function不是作用于与所有的data set上,还是作用于不同的group上,那么操作类型将会变为SORT (GROUP BY),这时会有sort发生。

ASKTOM也说过:
it hasn't anything to sort, it is just aggregating. The step however is called "sort aggregate" it knows there is just one row to "sort".


唯一还有点疑问的就是INDEX FAST FULL SCAN是有排序的,但这里未显示?

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7192724/viewspace-767157/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2623835