[Oracle]-[SORT AGGREGATE]-count与索引

最新推荐文章于 2024-07-21 02:09:07 发布

原创最新推荐文章于 2024-07-21 02:09:07 发布 · 7.2k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

技术之我见专栏收录该内容

291 篇文章

订阅专栏

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