[Oracle]-[sort aggregate]-count and Index

[Oracle]-[sort aggregate]-count and index 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; check whether indexes play a role in count query performance. Their Execution Plan: SQL> select count (*) from t_count; Execution Plan ---------------------------------------------------------- Plan hash value: 2197880521 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | ---------------------------------------------------------------------- | 0 | select statement | 1 | 39 (0) | 00:00:01 | 1 | SORT AGGREG ATE | 1 | 2 | table access full | T_COUNT | 12028 | 39 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement SQL> select count (object_name) from t_count; Execution Plan into Plan hash value: 2197880521 -------------------------------------------------------- ---------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 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 | ------------------------------------------------------------------------------ Sta Tistics defaults 0 recursive cballs 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 Bytes | 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 defaults 0 recursive cballs 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 their trace files: select count (*) fromt_count call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 1 0.00 0.00 0 2 0 0 Execute 1 0.00 0 0 0 0 Fetch 2 0.00 0 0.00 0 1 ------- ------ -------- ---------- total 4 0.00 0.00 0 144 0 1 Misses in library ca Che during parse: 1 Optimizer mode: ALL_ROWSParsing user id: 25 Rows Row Source Operation ------- running 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) fromt_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 24 29 0 1 ------- ------ -------- ---------- total 4 0.00 0.00 24 31 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWSParsing 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) fromt_count call count cpu elapsed disk query current rows ------- ------ -------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0 0 0 0 Fetch 2 0.00 0 0.00 0 1 ------------------------------------------------------- ------ ---------- Total 4 0.00 0.00 0 143 0 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWSParsing 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) can be obtained: 1. Using count (INDEX), you can indeed use index fast full scan, table access full is not required. 2. I noticed that SORT AGGREGATE seems to use sorting, but does not need to be sorted by count? Actually, the COST is empty, and there is no actual consumption. If sort is not available, the sorting will be performed. As one of the SORT options, sort AGGREGATE is special and does not do sort. Sort aggregate acts on all data sets for aggregate functions, such as sum, count, avg, min, and max. If the aggregate function does not work on any data set or on a different group, the operation type will change to SORT (group by), and sort will occur. ASKTOM also said: 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 ".