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

♣

题目部分

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

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

一、 普通表（无索引）

COUNT(*)

Execution Plan

| 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

Statistics

二、 普通索引

Execution Plan

| 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

Statistics

Execution Plan

| 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

Statistics

Execution Plan

| 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

Statistics

三、 位图索引

Execution Plan

| 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

Statistics

1、 位图索引+并行

四、 物化视图

Execution Plan

| 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

Statistics

五、 缓存结果

Execution Plan

| 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):

Note

Statistics

六、 根据业务规则判断

八、 分析需求

一、普通表（无索引）

| 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 |

二、普通索引

| 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 |

| 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 |

| 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 |

三、位图索引

| 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 | | | |

1、位图索引+并行

四、物化视图

| 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 |

五、缓存结果

| 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 |

六、根据业务规则判断

八、分析需求