SQL> create table t1 as select * from dba_objects;
表已创建。

SQL> create index idx1_object_id on t1(object_id);
索引已创建。

SQL> select count(*) from t1;
COUNT(*)
----------
49821

思考一个问题，count(*)是否走索引?验证一下
SQL> select count(*) from t1;

COUNT(*)
----------
49821
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 158 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |   TABLE ACCESS FULL| T1 | 47795 | 158 (2)| 00:00:02 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
    690 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

为什么不走索引?索引列是不存储空值的，索引并不知道表是否有空值，如果表有空值，走索引，count(*)将不准确.
如果想走索引，可以有以下两种解决方法：
1)明确索引列非空
SQL> select count(*)from t1 where object_id is not null;

COUNT(*)
----------
49821

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1296839119
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 |   INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 606K| 29 (4)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
  117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2)修改索引列为非空
SQL> desc t1;
名称是否为空? 类型
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> alter table t1 modify object_id not null;

表已更改。
SQL> desc t1;
名称是否为空? 类型
----------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*)from t1;

COUNT(*)
----------
49821

已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1296839119

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 |   INDEX FAST FULL SCAN| IDX1_OBJECT_ID | 47795 | 29 (4)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

ITPub博客

count(*)优化

最新文章