1、count(*)优化
索引存储列及rowid的特性：
构建表及索引：
SQL> drop table t purge;
表已删除。
SQL> create table t as select * from dba_objects;
表已创建。
SQL> create index idx1_object_id on t(object_id);
索引已创建。
SQL> select count(*) from t;
COUNT(*)
----------
72940
SQL> set autotrace on
SQL> set linesize 400
SQL> set timing on
SQL> select count(*) from t;
COUNT(*)
----------
72940
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 65748 | 291 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1043 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，虽然我建立了索引，但是使用的确实全表扫描，那么为什么没有使用到索引呢？因为我的字段显示是为空的，没有强制没不为空。
改变查询语句：select count(*) from t where object_id is not null
SQL> select count(*) from t where object_id is not null;
COUNT(*)
----------
72940
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 50 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 65748 | 834K| 50 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
232 consistent gets
162 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，使用了索引了，并且逻辑读为232，比全表扫描块很多。
那么有什么方法可以不使用where语句呢？因为我们没有告诉oracle此列不允许为空，所以oracle是不会冒险去使用索引的，而是使用全表扫描。
SQL> alter table t modify object_id not null;
表已更改。
已用时间: 00: 00: 00.31
SQL> select count(*) from t;
COUNT(*)
----------
72940
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1296839119
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 65748 | 49 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
212 recursive calls
0 db block gets
263 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
当我将建立了所以的索引列改为部位空时，oracle根据算法会自动使用索引扫描，所以onject_id列为主键，也可说明了非空属性。
