ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 4999|回复: 4

[笔记] 【优化】COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非...

[复制链接]
认证徽章
论坛徽章:
15
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51目光如炬
日期:2017-11-12 22:00:01火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07火眼金睛
日期:2017-02-28 22:00:00火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29目光如炬
日期:2016-10-30 22:00:00罗罗诺亚·索隆
日期:2016-10-17 12:26:14
发表于 2017-3-30 13:36 | 显示全部楼层 |阅读模式
1.1  BLOG文档结构图
1.2  前言部分1.2.1  导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① COUNT(1)和COUNT(*)的区别(重点)
② 10046和10053的使用
③ “SELECT COUNT(列)”和“SELECT 列”在选择索引方面的区别
④ COUNT计数的优化
Tips:
① 本文在itpub([url=]http://blog.itpub.net/26736162[/url])、博客园([url=]http://www.cnblogs.com/lhrbest[/url])和微信公众号(xiaomaimiaolhr)上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载,小麦苗的云盘地址见:[url=]http://blog.itpub.net/26736162/viewspace-1624453/[/url]
③ 若网页文章代码格式有错乱,请下载pdf格式的文档来阅读。
④ 在本篇BLOG中,代码输出部分一般放在一行一列的表格中。
⑤ 本文适合于Oracle初中级人员阅读,Oracle大师请略过本文。

第二章 实验部分2.1  实验环境介绍
项目
source db
db 类型
RAC
db version
11.2.0.3.0
db 存储
ASM
OS版本及kernel版本
RHEL 6.5
2.2  实验目标
弄清楚COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)之间的区别,以及它们之间的效率问题。
2.3  实验过程2.3.1  实验脚本
--创建1W行的表
DROP TABLE T_COUNT_LHR;
CREATE TABLE T_COUNT_LHR AS
SELECT OBJECT_ID,
       OBJECT_NAME,
       OWNER,
       DATA_OBJECT_ID,
       OBJECT_TYPE,
       LAST_DDL_TIME
  FROM DBA_OBJECTS D
WHERE D.OBJECT_ID IS NOT NULL
   AND D.OBJECT_NAME IS NOT NULL
   AND ROWNUM <= 10000;
--更新空值,
UPDATE T_COUNT_LHR t SET t.object_type='' WHERE Rownum<=5;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME=T.LAST_DDL_TIME+ROWNUM;
UPDATE T_COUNT_LHR t SET t.LAST_DDL_TIME='' WHERE Rownum<=1;
COMMIT;
--添加主键、非空约束、唯一索引、普通索引
ALTER TABLE T_COUNT_LHR ADD CONSTRAINT PK_OBJECT_ID PRIMARY KEY(OBJECT_ID);
ALTER TABLE T_COUNT_LHR MODIFY OBJECT_NAME NOT NULL;
CREATE UNIQUE INDEX IDX_LDT ON T_COUNT_LHR(LAST_DDL_TIME);
CREATE  INDEX IDX_DATA_OBJECT_ID ON T_COUNT_LHR(DATA_OBJECT_ID);
CREATE  INDEX IDX_DATA_OWNER ON T_COUNT_LHR(OWNER);
ALTER TABLE T_COUNT_LHR MODIFY OWNER NOT NULL;
--收集统计信息
EXEC dbms_stats.gather_table_stats(USER,'T_COUNT_LHR');
SELECT d.COLUMN_NAME,d.DATA_TYPE,d.NUM_NULLS,d.NUM_DISTINCT,d.LAST_ANALYZED FROM cols d WHERE d.TABLE_NAME='T_COUNT_LHR';
表的信息如下所示:
列名
是否主键
是否允许为空
是否有索引
数据类型
空值的行数
不同值的行数
总行数
OBJECT_ID
Y
N
唯一索引
NUMBER
0
10000
10000
OBJECT_NAME
N
VARCHAR2
0
8112
10000
OWNER
N
普通索引(IDX_OWNER)
VARCHAR2
0
5
10000
DATA_OBJECT_ID
Y
普通索引(IDX_DATA_OBJECT_ID)
NUMBER
7645
2318
10000
OBJECT_TYPE
Y
VARCHAR2
5
20
10000
LAST_DDL_TIME
Y
唯一索引(IDX_LDT)
DATE
1
9999
10000
需要统计如下几种情况:
SELECT COUNT(1) FROM T_COUNT_LHR;--走索引
SELECT COUNT(*) FROM T_COUNT_LHR;--走索引
SELECT COUNT(ROWID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_ID) FROM T_COUNT_LHR; --走索引
SELECT COUNT(OBJECT_NAME) FROM T_COUNT_LHR;--走索引
SELECT COUNT(OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.DATA_OBJECT_ID) FROM T_COUNT_LHR D; --走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D; --不走索引
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D;--不走索引
SELECT COUNT(DISTINCT OWNER) FROM T_COUNT_LHR D;--走索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D WHERE DATA_OBJECT_ID IS NOT NULL ;--走索引


2.3.2  执行计划
介绍
SQL命令
执行计划
返回行数
COUNT(1)和COUNT(常量)是一样的,SELECT COUNT(*) "COUNT(1)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(1) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |     7   (0)| 00:00:01 |
|-----------------------------------------------------------------------------
10000
SELECT COUNT(*) "COUNT(*)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(*) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |     7   (0)| 00:00:01 |
|-----------------------------------------------------------------------------
10000
SELECT COUNT("T_COUNT_LHR".ROWID) "COUNT(ROWID)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(ROWID) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    12 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |    12 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |   117K|     7   (0)| 00:00:01 |
|-------------------------------------------------------------------------------------
10000
SELECT COUNT(*) "COUNT(OBJECT_ID)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(OBJECT_ID) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |     7   (0)| 00:00:01 |
|-----------------------------------------------------------------------------
10000
SELECT COUNT(*) "COUNT(OBJECT_NAME)" FROM "LHR"."T_COUNT_LHR" "T_COUNT_LHR"
SELECT COUNT(OBJECT_NAME) FROM T_COUNT_LHR;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |     7   (0)| 00:00:01 |
|-----------------------------------------------------------------------------
10000
OWNER列含有索引,非空列,选择主键索引
SELECT COUNT(OWNER) FROM T_COUNT_LHR D;
Plan hash value: 1265209789
|-----------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 10000 |     7   (0)| 00:00:01 |
|-----------------------------------------------------------------------------
10000
DATA_OBJECT_ID列允许为空,含有普通索引
SELECT COUNT(D.DATA_OBJECT_ID) FROM T_COUNT_LHR D;
Plan hash value: 2404962198
|-------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
|-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |     2 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                    |     1 |     2 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_DATA_OBJECT_ID | 10000 | 20000 |     3   (0)| 00:00:01 |
|-------------------------------------------------------------------------------------------
2355
注意,COUNT(列)计算的是列值为非空的行数
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D;
Plan hash value: 887614938
|--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     8 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |     8 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_LDT | 10000 | 80000 |     9   (0)| 00:00:01 |
|--------------------------------------------------------------------------------
9999
SELECT COUNT(D.LAST_DDL_TIME) FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;
Plan hash value: 887614938
|--------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     8 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |         |     1 |     8 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_LDT |  9999 | 79992 |     9   (0)| 00:00:01 |
|--------------------------------------------------------------------------------
9999
由于列中存在空值,所以不会选择索引。加上IS NOT NULL就可以选择索引了。
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D;
Plan hash value: 2392171920
|--------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             | 10000 | 80000 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_COUNT_LHR | 10000 | 80000 |    22   (0)| 00:00:01 |
|--------------------------------------------------------------------------------
10000
SELECT D.LAST_DDL_TIME FROM T_COUNT_LHR D WHERE D.LAST_DDL_TIME IS NOT NULL;
Plan hash value: 2419516343
|-------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  9999 | 79992 |     9   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_LDT |  9999 | 79992 |     9   (0)| 00:00:01 |
|-------------------------------------------------------------------------------
9999
DISTINCT允许为空列不会选择索引,而DISTINCT非空列会选择索引
SELECT COUNT(DISTINCT DATA_OBJECT_ID) FROM T_COUNT_LHR D;
Plan hash value: 3258478826
|-----------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    13 |    23   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE      |             |     1 |    13 |            |          |
|   2 |   VIEW               | VW_DAG_0    |  2318 | 30134 |    23   (5)| 00:00:01 |
|   3 |    HASH GROUP BY     |             |  2318 |  4636 |    23   (5)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T_COUNT_LHR | 10000 | 20000 |    22   (0)| 00:00:01 |
|-----------------------------------------------------------------------------------
2318
SELECT COUNT(DISTINCT OWNER) FROM T_COUNT_LHR D;
Plan hash value: 4008695099
|-----------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                |     1 |    17 |     9  (12)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                |     1 |    17 |            |          |
|   2 |   VIEW                  | VW_DAG_0       |     5 |    85 |     9  (12)| 00:00:01 |
|   3 |    HASH GROUP BY        |                |     5 |    30 |     9  (12)| 00:00:01 |
|   4 |     INDEX FAST FULL SCAN| IDX_DATA_OWNER | 10000 | 60000 |     8   (0)| 00:00:01 |
|-----------------------------------------------------------------------------------------
5
2.3.3  10046事件

2.4  实验结论
COUNT()函数是Oracle中的聚合函数,用于统计结果集的行数。其语法形式如下所示:
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.
我们把COUNT的使用情况分为以下3类:
① COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)
② COUNT(允许为空列)
③ COUNT(DISTINCT 列名)
下面分别从查询结果和效率方面做个比较:
(一)结果区别
① COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)这几种方式统计的行数是表中所有存在的行的总数,包括值为NULL的行和非空行。所以,这几种方式的执行结果相同。这里的常量可以为数字或字符串,例如,COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是:这里的COUNT(1)中的“1”并不表示表中的第一列,它其实是一个表达式,可以换成任意数字或字符或表达式。
② COUNT(允许为空列) 这种方式统计的行数不会包括字段值为NULL的行。
③ COUNT(DISTINCT 列名) 得到的结果是除去值为NULL和重复数据后的结果。
④ “SELECT COUNT(''),COUNT(NULL) FROM T_COUNT_LHR;”返回0行。
(二)效率、索引
① 如果存在主键或非空列上的索引,那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)会首先选择主键上的索引快速全扫描(INDEX FAST FULL SCAN)。若主键不存在则会选择非空列上的索引。若非空列上没有索引则肯定走全表扫描(TABLE ACCESS FULL)。其中,COUNT(ROWID)在走索引的时候比其它几种方式要慢。通过10053事件可以看到这几种方式除了COUNT(ROWID)之外,其它最终都会转换成COUNT(*)的方式来执行。
② 对于COUNT(COL1)来说,只要列字段上有索引则会选择索引快速全扫描(INDEX FAST FULL SCAN)。而对于“SELECT COL1”来说,除非列上有NOT NULL约束,否则执行计划会选择全表扫描。
③ COUNT(DISTINCT 列名) 若列上有索引,且有非空约束或在WHERE子句中使用IS NOT NULL,则会选择索引快速全扫描。其余情况选择全表扫描。







非常感谢各位朋友支持小麦苗。
认证徽章
论坛徽章:
15
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51目光如炬
日期:2017-11-12 22:00:01火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07火眼金睛
日期:2017-02-28 22:00:00火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29目光如炬
日期:2016-10-30 22:00:00罗罗诺亚·索隆
日期:2016-10-17 12:26:14
发表于 2017-3-30 13:37 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
0
发表于 2017-4-20 11:45 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
20
SQL大赛参与纪念
日期:2013-12-06 14:03:45生肖徽章:狗
日期:2013-12-09 14:28:47生肖徽章:猪
日期:2013-12-09 14:28:472010系统架构师大会纪念
日期:2015-08-03 13:54:362015年中国系统架构师大会纪念徽章
日期:2015-08-03 13:54:362011系统架构师大会纪念章
日期:2015-08-03 13:54:362012系统架构师大会纪念章
日期:2015-08-03 13:54:362009架构师大会纪念徽章
日期:2015-08-03 13:54:362013系统架构师大会纪念章
日期:2015-08-03 13:54:36生肖徽章:鸡
日期:2013-12-09 14:28:47
发表于 2017-6-3 13:10 | 显示全部楼层
经常会有人面试中会提问到count(1)和count(*)那个更快,如果回答一样快,会被认为不懂Oracle的解析规则。 实际上作者给了我们很好的答案, “如果存在主键或非空列上的索引,那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)会首先选择主键上的索引快速全扫描(INDEX FAST FULL SCAN)。若主键不存在则会选择非空列上的索引。若非空列上没有索引则肯定走全表扫描(TABLE ACCESS FULL) ” ,感谢

使用道具 举报

回复
认证徽章
论坛徽章:
15
青年奥林匹克运动会-高尔夫
日期:2014-09-10 14:54:51目光如炬
日期:2017-11-12 22:00:01火眼金睛
日期:2017-09-30 22:00:01目光如炬
日期:2017-09-03 22:00:01火眼金睛
日期:2017-09-01 17:00:07火眼金睛
日期:2017-02-28 22:00:00火眼金睛
日期:2017-02-06 01:02:33人气徽章
日期:2016-11-09 15:56:29目光如炬
日期:2016-10-30 22:00:00罗罗诺亚·索隆
日期:2016-10-17 12:26:14
发表于 2017-6-5 13:35 | 显示全部楼层
lanhuiboy 发表于 2017-6-3 13:10
经常会有人面试中会提问到count(1)和count(*)那个更快,如果回答一样快,会被认为不懂Oracle的解析规则。  ...

哈哈,COUNT(1)和COUNT(*)的问题终于可以终结了

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表