[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是有排序的，但这里未显示？