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

2019-09-29 16:11:20 浏览数 (1)
♣

题目部分

在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类型数据库。物化视图是应用在数据要求不怎么及时的场景下。若表频繁更新，则不适合缓存结果集。

优化没有止境，对数据库了解越多，能想到的方法就越多。

代码语言：javascript复制
--无索引
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;
一、 普通表（无索引）
代码语言：javascript复制
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
二、 普通索引
代码语言：javascript复制
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
代码语言：javascript复制
--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
三、 位图索引

试验如下：

代码语言：javascript复制
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、 位图索引 并行
代码语言：javascript复制
ALTER INDEX IDX_OBJECT_NAME PARALLEL 8;

SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01;

并行技术可以加快执行速度，但一致性读有所增加，但并行还是能加快整体运行速度。

四、 物化视图

这主要是应用在数据库更新不是非常频繁场景，用的是空间换时间。

代码语言：javascript复制
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的几乎全部开销都可以避免。

代码语言：javascript复制
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变为：

代码语言：javascript复制
SELECT COUNT(*) FROM T_20170704_COUNT_LHR_01 WHERE ROWNUM=1;

该SQL无论表中数据多大，性能都不会太差。

八、 分析需求

仔细分析需求后，可能会发现，统计行数这条SQL根本就是多余的，那么这条SQL语句就可以直接砍掉了。

& 说明：

有关COUNT的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2141601/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

缓存 云推荐引擎 oracle sql 数据库

0 人点赞