♣
题目部分
在Oracle中，说说COUNT(*)计算行数有哪些优化手段？
♣
答案部分
手段 | 命令 | 执行计划 | 主要原理 | 详细说明 | 性能情况 |
全表扫描 | TABLE ACCESS FULL | 全表扫描 | OLTP中，通常是最慢的方式。 | 逻辑读为1139 | |
增加普通索引 | CREATE INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME); | INDEX FAST FULL SCAN | 从全表扫描转成全索引扫描。 | 因为索引一般比表小的多，所以全表扫描转成全索引扫描，性能能大幅度提升。 | 逻辑读为400 |
常数索引 | CREATE INDEX IDX_OBJECT_NAME ON T(0); | INDEX FAST FULL SCAN | 从全表扫描转成全索引扫描。 | 常数索引比普通索引更小。 | 逻辑读为151 |
常数压缩索引 | CREATE INDEX IDX_OBJECT_NAME ON T(0) COMPRESS; | INDEX FAST FULL SCAN | 从全表扫描转成全索引扫描。 | 常数压缩索引比常数索引更小。 | 逻辑读为129 |
位图索引 | CREATE BITMAP INDEX IDX_OBJECT_NAME ON T(OBJECT_NAME); | BITMAP INDEX FAST FULL SCAN | 从BTREE索引扫描转成位图索引扫描。 | 位图索引的大小比BTREE索引要小的多，所以位图索引扫描快。 | 逻辑读为5 |
物化视图 | CREATE MATERIALIZED VIEW MV_COUNT_T BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT COUNT(*) FROM T; | MAT_VIEW REWRITE ACCESS FULL | 空间换时间。 | 要注意，如果数据要求比较实时，就不适用。 | 逻辑读为3 |
缓存结果 | SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T; | RESULT CACHE | 直接把查询结果拿来用。 | 要注意，如果数据频繁更新，就不适用。 | 逻辑读为0 |
业务理解 | SELECT COUNT(*) FROM T WHERE ROWNUM=1; | 如果COUNT(*)只是为了判断条数，就加上ROWNUM=1来判断是否为1。 | 业务需求转换，获取条数有的时候，只是为了看看表是否为空，这时候是否是1条和是否大于0其实是一样的。 | 不言而喻 | |
分析需求 | 据说，这个COUNT(*)统计条数语句，是多余的！直接砍了这条语句，这里没有SQL! | 无敌！ | |||
位图索引可以按很高密度存储数据，因此往往比B树索引小很多，前提是在基数比较小（列重复度比较高）的情况下。位图索引是保存空值的，因此可以在COUNT中利用。位图索引不太适合OLTP类型数据库。物化视图是应用在数据要求不怎么及时的场景下。若表频繁更新，则不适合缓存结果集。
优化没有止境，对数据库了解越多，能想到的方法就越多。
1--无索引 2DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 3CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 4ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 5SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 6SET AUTOTRACE TRACEONLY 7SET LINESIZE 1000 8SET TIMING ON 9SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 10 11 12--普通索引 13DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 14CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 15ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 16CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 17SET AUTOTRACE TRACEONLY 18SET TIMING ON 19SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 20 21 22--唯一索引 23DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 24CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 25ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 26UPDATE T_20170704_COUNT_LHR_01 T SET T.OBJECT_NAME=T.OBJECT_NAME||ROWNUM; 27CREATE unique INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 28SET AUTOTRACE TRACEONLY 29SET TIMING ON 30SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 31 32 33--常数索引 34DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 35CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 36ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 37CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); 38SET AUTOTRACE TRACEONLY 39SET TIMING ON 40SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 41 42 43--常数压缩索引 44DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 45CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 46ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 47CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; 48SET AUTOTRACE TRACEONLY 49SET TIMING ON 50SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 51 52 53 54 55--位图索引 56DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 57CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 58UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; 59UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; 60CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 61SET AUTOTRACE TRACEONLY 62SET TIMING ON 63SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 64ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; 65SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 66 67 68-- 物化视图 69DROP MATERIALIZED VIEW MV_COUNT_T_LHR; 70DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 71CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 72UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; 73UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; 74CREATE MATERIALIZED VIEW MV_COUNT_T_LHR 75BUILD IMMEDIATE 76REFRESH ON COMMIT 77ENABLE QUERY REWRITE 78AS 79SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 80SET AUTOTRACE TRACEONLY 81SET LINESIZE 1000 82SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 83 84 85--缓存结果集 86DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 87CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 88SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 89SET LINESIZE 1000 90SET AUTOTRACE TRACEONLY 91SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; 92 93 94--业务分析 95SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
一、 普通表（无索引）
1DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 2CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 3ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 4 5LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 6 7 COUNT(*) 8---------- 9 79300 10 11LHR@orclasm > SET AUTOTRACE TRACEONLY 12LHR@orclasm > SET LINESIZE 1000 13LHR@orclasm > SET TIMING ON 14LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 15 16Elapsed: 00:00:00.01 17 18Execution Plan 19---------------------------------------------------------- 20Plan hash value: 1395805058 21 22-------------------------------------------------------------------------------------- 23| Id | Operation | Name | Rows | Cost (%CPU)| Time | 24-------------------------------------------------------------------------------------- 25| 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | 26| 1 | SORT AGGREGATE | | 1 | | | 27| 2 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 91429 | 317 (1)| 00:00:04 | 28-------------------------------------------------------------------------------------- 29 30Note 31----- 32 - dynamic sampling used for this statement (level=2) 33 34 35Statistics 36---------------------------------------------------------- 37 0 recursive calls 38 0 db block gets 39 1139 consistent gets 40 0 physical reads 41 0 redo size 42 527 bytes sent via SQL*Net to client 43 520 bytes received via SQL*Net from client 44 2 SQL*Net roundtrips to/from client 45 0 sorts (memory) 46 0 sorts (disk) 47 1 rows processed
二、 普通索引
1DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 2CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 3ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 4CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 5SET AUTOTRACE TRACEONLY 6SET TIMING ON 7 8LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 9 10Elapsed: 00:00:00.20 11 12Execution Plan 13---------------------------------------------------------- 14Plan hash value: 1178070731 15 16--------------------------------------------------------------------------------- 17| Id | Operation | Name | Rows | Cost (%CPU)| Time | 18--------------------------------------------------------------------------------- 19| 0 | SELECT STATEMENT | | 1 | 114 (1)| 00:00:02 | 20| 1 | SORT AGGREGATE | | 1 | | | 21| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 91429 | 114 (1)| 00:00:02 | 22--------------------------------------------------------------------------------- 23 24Note 25----- 26 - dynamic sampling used for this statement (level=2) 27 28 29Statistics 30---------------------------------------------------------- 31 0 recursive calls 32 0 db block gets 33 400 consistent gets 34 0 physical reads 35 0 redo size 36 527 bytes sent via SQL*Net to client 37 520 bytes received via SQL*Net from client 38 2 SQL*Net roundtrips to/from client 39 0 sorts (memory) 40 0 sorts (disk) 41 1 rows processed
1--1、主键索引（唯一索引） 2DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 3CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 4ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 5CREATE UNIQUE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 6SET AUTOTRACE TRACEONLY 7SET TIMING ON 8SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 9 10--2、常数索引 11DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 12CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 13ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 14CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); 15SET AUTOTRACE TRACEONLY 16SET TIMING ON 17SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 18LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 19 20Elapsed: 00:00:00.01 21 22Execution Plan 23---------------------------------------------------------- 24Plan hash value: 1178070731 25 26--------------------------------------------------------------------------------- 27| Id | Operation | Name | Rows | Cost (%CPU)| Time | 28--------------------------------------------------------------------------------- 29| 0 | SELECT STATEMENT | | 1 | 45 (3)| 00:00:01 | 30| 1 | SORT AGGREGATE | | 1 | | | 31| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 45 (3)| 00:00:01 | 32--------------------------------------------------------------------------------- 33 34Note 35----- 36 - dynamic sampling used for this statement (level=2) 37 38 39Statistics 40---------------------------------------------------------- 41 0 recursive calls 42 0 db block gets 43 151 consistent gets 44 0 physical reads 45 0 redo size 46 528 bytes sent via SQL*Net to client 47 520 bytes received via SQL*Net from client 48 2 SQL*Net roundtrips to/from client 49 0 sorts (memory) 50 0 sorts (disk) 51 1 rows processed 52 53 54--3、常数压缩索引 55DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 56CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 57ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; 58CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; 59SET AUTOTRACE TRACEONLY 60SET TIMING ON 61SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 62LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 63 64Elapsed: 00:00:00.00 65 66Execution Plan 67---------------------------------------------------------- 68Plan hash value: 1178070731 69 70--------------------------------------------------------------------------------- 71| Id | Operation | Name | Rows | Cost (%CPU)| Time | 72--------------------------------------------------------------------------------- 73| 0 | SELECT STATEMENT | | 1 | 38 (0)| 00:00:01 | 74| 1 | SORT AGGREGATE | | 1 | | | 75| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 38 (0)| 00:00:01 | 76--------------------------------------------------------------------------------- 77 78Note 79----- 80 - dynamic sampling used for this statement (level=2) 81 82 83Statistics 84---------------------------------------------------------- 85 0 recursive calls 86 0 db block gets 87 129 consistent gets 88 0 physical reads 89 0 redo size 90 528 bytes sent via SQL*Net to client 91 520 bytes received via SQL*Net from client 92 2 SQL*Net roundtrips to/from client 93 0 sorts (memory) 94 0 sorts (disk) 95 1 rows processed
三、 位图索引
试验如下：
1DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 2CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 3UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; 4UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; 5CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); 6SET AUTOTRACE TRACEONLY 7SET TIMING ON 8SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 9LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 10 11Elapsed: 00:00:00.00 12 13Execution Plan 14---------------------------------------------------------- 15Plan hash value: 1696023018 16 17----------------------------------------------------------------------------------------- 18| Id | Operation | Name | Rows | Cost (%CPU)| Time | 19----------------------------------------------------------------------------------------- 20| 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | 21| 1 | SORT AGGREGATE | | 1 | | | 22| 2 | BITMAP CONVERSION COUNT | | 91429 | 5 (0)| 00:00:01 | 23| 3 | BITMAP INDEX FAST FULL SCAN| IDX_OBJECT_NAME | | | | 24----------------------------------------------------------------------------------------- 25 26Note 27----- 28 - dynamic sampling used for this statement (level=2) 29 30 31Statistics 32---------------------------------------------------------- 33 0 recursive calls 34 0 db block gets 35 5 consistent gets 36 0 physical reads 37 0 redo size 38 527 bytes sent via SQL*Net to client 39 520 bytes received via SQL*Net from client 40 2 SQL*Net roundtrips to/from client 41 0 sorts (memory) 42 0 sorts (disk) 43 1 rows processed
1、 位图索引+并行
1ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; 2 3SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01;
并行技术可以加快执行速度，但一致性读有所增加，但并行还是能加快整体运行速度。
四、 物化视图
这主要是应用在数据库更新不是非常频繁场景，用的是空间换时间。
1DROP MATERIALIZED VIEW MV_COUNT_T_LHR; 2DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 3CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 4UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; 5UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; 6CREATE MATERIALIZED VIEW MV_COUNT_T_LHR 7BUILD IMMEDIATE 8REFRESH ON COMMIT 9ENABLE QUERY REWRITE 10AS 11SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 12SET AUTOTRACE TRACEONLY 13SET LINESIZE 1000 14SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 15LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 16 17Elapsed: 00:00:00.08 18 19Execution Plan 20---------------------------------------------------------- 21Plan hash value: 571421573 22 23----------------------------------------------------------------------------------------------- 24| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 25----------------------------------------------------------------------------------------------- 26| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | 27| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_COUNT_T_LHR | 1 | 13 | 3 (0)| 00:00:01 | 28----------------------------------------------------------------------------------------------- 29 30Note 31----- 32 - dynamic sampling used for this statement (level=2) 33 34 35Statistics 36---------------------------------------------------------- 37 0 recursive calls 38 0 db block gets 39 3 consistent gets 40 0 physical reads 41 0 redo size 42 531 bytes sent via SQL*Net to client 43 520 bytes received via SQL*Net from client 44 2 SQL*Net roundtrips to/from client 45 0 sorts (memory) 46 0 sorts (disk) 47 1 rows processed
五、 缓存结果
在Oracle 11g中提供了结果集缓存特性。该缓存是在共享内存中存储全部的结果集。如果一个查询SQL被执行，且它对应的结果集在缓存中，那么，该SQL的几乎全部开销都可以避免。
1DROP TABLE T_20170704_COUNT_LHR_01 PURGE; 2CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; 3SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; 4SET LINESIZE 1000 5SET AUTOTRACE TRACEONLY 6SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; 7LHR@orclasm > LHR@orclasm > SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; 8 9Elapsed: 00:00:00.00 10 11Execution Plan 12---------------------------------------------------------- 13Plan hash value: 1395805058 14 15------------------------------------------------------------------------------------------ 16| Id | Operation | Name | Rows | Cost (%CPU)| Time | 17------------------------------------------------------------------------------------------ 18| 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | 19| 1 | RESULT CACHE | 6pp2f468gdjnj9v3s3mfwffd7t | | | | 20| 2 | SORT AGGREGATE | | 1 | | | 21| 3 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 86597 | 317 (1)| 00:00:04 | 22------------------------------------------------------------------------------------------ 23 24Result Cache Information (identified by operation id): 25------------------------------------------------------ 26 27 1 - column-count=1; dependencies=(LHR.T_20170704_COUNT_LHR_01); attributes=(single-row); name="SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01" 28 29Note 30----- 31 - dynamic sampling used for this statement (level=2) 32 33 34Statistics 35---------------------------------------------------------- 36 0 recursive calls 37 0 db block gets 38 0 consistent gets 39 0 physical reads 40 0 redo size 41 528 bytes sent via SQL*Net to client 42 520 bytes received via SQL*Net from client 43 2 SQL*Net roundtrips to/from client 44 0 sorts (memory) 45 0 sorts (disk) 46 1 rows processed 47
六、 根据业务规则判断
若统计行数只是为了判断表中是否有记录，则可以使用ROWNUM=1，所以改写后的SQL变为：
1SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
该SQL无论表中数据多大，性能都不会太差。
八、 分析需求
仔细分析需求后，可能会发现，统计行数这条SQL根本就是多余的，那么这条SQL语句就可以直接砍掉了。
