【DB笔试面试622】在Oracle中，说说COUNT(*)计算行数有哪些优化手段？
- 2019 年 10 月 10 日
- 笔记
题目部分
在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 REWRITEAS 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类型数据库。物化视图是应用在数据要求不怎么及时的场景下。若表频繁更新，则不适合缓存结果集。
优化没有止境，对数据库了解越多，能想到的方法就越多。
--无索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --普通索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --唯一索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; UPDATE T_20170704_COUNT_LHR_01 T SET T.OBJECT_NAME=T.OBJECT_NAME||ROWNUM; CREATE unique INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --常数索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --常数压缩索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --位图索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; -- 物化视图 DROP MATERIALIZED VIEW MV_COUNT_T_LHR; DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE MATERIALIZED VIEW MV_COUNT_T_LHR BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --缓存结果集 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET LINESIZE 1000 SET AUTOTRACE TRACEONLY SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; --业务分析 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
一、 普通表（无索引）
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; COUNT(*) ---------- 79300 LHR@orclasm > SET AUTOTRACE TRACEONLY LHR@orclasm > SET LINESIZE 1000 LHR@orclasm > SET TIMING ON LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1395805058 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 91429 | 317 (1)| 00:00:04 | -------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1139 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
二、 普通索引
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.20 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 114 (1)| 00:00:02 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 91429 | 114 (1)| 00:00:02 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 400 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
--1、主键索引（唯一索引） DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE UNIQUE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; --2、常数索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 45 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 45 (3)| 00:00:01 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 151 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed --3、常数压缩索引 DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; ALTER TABLE T_20170704_COUNT_LHR_01 MODIFY OBJECT_NAME NOT NULL; CREATE INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(0) COMPRESS; SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > LHR@orclasm > LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1178070731 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_OBJECT_NAME | 86597 | 38 (0)| 00:00:01 | --------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 129 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
三、 位图索引
试验如下：
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE BITMAP INDEX IDX_OBJECT_NAME ON T_20170704_COUNT_LHR_01(OBJECT_NAME); SET AUTOTRACE TRACEONLY SET TIMING ON SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1696023018 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | BITMAP CONVERSION COUNT | | 91429 | 5 (0)| 00:00:01 | | 3 | BITMAP INDEX FAST FULL SCAN| IDX_OBJECT_NAME | | | | ----------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 527 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
1、 位图索引+并行
ALTER INDEX IDX_OBJECT_NAME PARALLEL 8; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01;
并行技术可以加快执行速度，但一致性读有所增加，但并行还是能加快整体运行速度。
四、 物化视图
这主要是应用在数据库更新不是非常频繁场景，用的是空间换时间。
DROP MATERIALIZED VIEW MV_COUNT_T_LHR; DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='lhr'; UPDATE T_20170704_COUNT_LHR_01 SET OBJECT_NAME='ldd' WHERE ROWNUM<=20000; CREATE MATERIALIZED VIEW MV_COUNT_T_LHR BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET AUTOTRACE TRACEONLY SET LINESIZE 1000 SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.08 Execution Plan ---------------------------------------------------------- Plan hash value: 571421573 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_COUNT_T_LHR | 1 | 13 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 531 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
五、 缓存结果
在Oracle 11g中提供了结果集缓存特性。该缓存是在共享内存中存储全部的结果集。如果一个查询SQL被执行，且它对应的结果集在缓存中，那么，该SQL的几乎全部开销都可以避免。
DROP TABLE T_20170704_COUNT_LHR_01 PURGE; CREATE TABLE T_20170704_COUNT_LHR_01 AS SELECT * FROM DBA_OBJECTS; SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01; SET LINESIZE 1000 SET AUTOTRACE TRACEONLY SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; LHR@orclasm > LHR@orclasm > SELECT /*+ RESULT_CACHE */ COUNT(*) FROM T_20170704_COUNT_LHR_01; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1395805058 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 317 (1)| 00:00:04 | | 1 | RESULT CACHE | 6pp2f468gdjnj9v3s3mfwffd7t | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T_20170704_COUNT_LHR_01 | 86597 | 317 (1)| 00:00:04 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 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" Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
六、 根据业务规则判断
若统计行数只是为了判断表中是否有记录，则可以使用ROWNUM=1，所以改写后的SQL变为：
SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;
该SQL无论表中数据多大，性能都不会太差。
八、 分析需求
仔细分析需求后，可能会发现，统计行数这条SQL根本就是多余的，那么这条SQL语句就可以直接砍掉了。
& 说明：
有关COUNT的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141601/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
