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

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】-【SORT AGGREGATE】-count與索引

相關文章