【DB笔试面试622】在Oracle中，说说COUNT(*)计算行数有哪些优化手段？

AiDBA宝典

发布于 2019-09-29 16:11:20

8920

发布于 2019-09-29 16:11:20

文章被收录于专栏：小麦苗的DB宝专栏

♣

题目部分

在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程序员面试笔试宝典》，作者：李华荣。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-08-21，如有侵权请联系 cloudcommunity@tencent.com 删除

缓存

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度