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