当前位置:数据库 > Oracle >>

[Oracle]-[SORT AGGREGATE]-count与索引

[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)
 
Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,