[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;

------------------------------------------------------------------------------

| 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;

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 |

         29  consistent gets

        421  bytes sent via SQL*Net to client

它們的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)

Fetch        2      0.00       0.00         24         29          0           1

total        4      0.00       0.00         24         31          0           1

     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)

Parse        1      0.00       0.00          0          1          0           0

total        4      0.00       0.00          0        143          0           1

      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是有排序的，但這裡未顯示？