[Oracle]-[SORT AGGREGATE]-count与索引(二)
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".