[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

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

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

| 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

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

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

| 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

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

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

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

上一个：dbms_lock.relase无法释放自定义的锁解决
下一个：ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table

更多图片编程知识:

更多Oracle疑问解答：: 运行exp备份oracle数据库提示oracle-12154错误; 有没有，生产Oracle Rman 备份脚本的工具啊！; 初学orcle，希望有大大帮忙解说一下详细步骤，从登录oracle到创建表的过程; oracle语句问题：一张user表，三个字段，id,name,time，插入记录比如：张三2007，李四2008，张三2011; 如何写一个ORACLE触发器同步两个表中的数据？; oracle 如何查看一个服务器上有多少个数据库.; oracle 创建包的时候错误求解; oracle 重复列的问题; oracle 中如何查处2星期前的数据; 请教oracle数据库安装中的问题; 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?; 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd); 我的是ORACLE 10G，在RMAN中如何按指定的时间恢复数据文件啊？; oracle为什么没有自动增长列; oracle快捷键都有哪些啊？