Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

0    95    1

Tags:

👉 本文共约3257个字,系统预计阅读时间或需13分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,\~O(∩_∩)O\~:

① COUNT(1)和COUNT(*)的区别(重点)

② 10046和10053的使用

③ “SELECT COUNT(列)”和“SELECT 列”在选择索引方面的区别

④ COUNT计数的优化

实验环境介绍

项目source db
db 类型RAC
db version11.2.0.3.0
db 存储ASM
OS版本及kernel版本RHEL 6.5

实验目标

弄清楚COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)之间的区别,以及它们之间的效率问题。

实验过程

实验脚本

Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

表的信息如下所示:

列名是否主键是否允许为空是否有索引数据类型空值的行数不同值的行数总行数
OBJECT_IDYN唯一索引NUMBER01000010000
OBJECT_NAMENVARCHAR20811210000
OWNERN普通索引(IDX_OWNER)VARCHAR20510000
DATA_OBJECT_IDY普通索引(IDX_DATA_OBJECT_ID)NUMBER7645231810000
OBJECT_TYPEYVARCHAR252010000
LAST_DDL_TIMEY唯一索引(IDX_LDT)DATE1999910000

需要统计如下几种情况:

执行计划

Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

Oracle之COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)、COUNT(允许为空列)、COUNT(DISTINCT 列名)等写法性能比较

10046事件

10053事件

alter session set events '10053 trace name context forever, level 12';

alter session set events '10053 trace name context OFF';

搜索关键词“Final”、“Starting”:

COUNT(COL1)和SELECT COL1的区别

其实在2014年的时候,小麦苗发布过一篇博客(http://blog.itpub.net/26736162/viewspace-1329880/),里边对这个问题有详细的实验。今天就把这个实验搬过来吧

实验结论

COUNT()函数是Oracle中的聚合函数,用于统计结果集的行数。其语法形式如下所示:

IMG_256

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

  1. COUNT(1)、COUNT(*)、COUNT(常量)、COUNT(主键)、COUNT(ROWID)、COUNT(非空列)
  2. COUNT(允许为空列)
  3. COUNT(DISTINCT 列名)

下面分别从查询结果和效率方面做个比较:

  1. 结果区别
  2. COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)这几种方式统计的行数是表中所有存在的行的总数,包括值为NULL的行和非空行。所以,这几种方式的执行结果相同。这里的常量可以为数字或字符串,例如,COUNT(2)、COUNT(333)、COUNT('x')、COUNT('xiaomaimiao')。需要注意的是:这里的COUNT(1)中的“1”并不表示表中的第一列,它其实是一个表达式,可以换成任意数字或字符或表达式。
  3. COUNT(允许为空列) 这种方式统计的行数不会包括字段值为NULL的行。
  4. COUNT(DISTINCT 列名) 得到的结果是除去值为NULL和重复数据后的结果。
  5. “SELECT COUNT(''),COUNT(NULL) FROM T_COUNT_LHR;”返回0行。
  6. 效率、索引
  7. 如果存在主键或非空列上的索引,那么COUNT(1)、COUNT(*)、COUNT(ROWID)、COUNT(常量)、COUNT(主键)、COUNT(非空列)会首先选择主键上的索引快速全扫描(INDEX FAST FULL SCAN)。若主键不存在则会选择非空列上的索引。若非空列上没有索引则肯定走全表扫描(TABLE ACCESS FULL)。其中,COUNT(ROWID)在走索引的时候比其它几种方式要慢。通过10053事件可以看到这几种方式除了COUNT(ROWID)之外,其它最终都会转换成COUNT(*)的方式来执行。
  8. 对于COUNT(COL1)来说,只要列字段上有索引则会选择索引快速全扫描(INDEX FAST FULL SCAN)。而对于“SELECT COL1”来说,除非列上有NOT NULL约束,否则执行计划会选择全表扫描。
  9. COUNT(DISTINCT 列名) 若列上有索引,且有非空约束或在WHERE子句中使用IS NOT NULL,则会选择索引快速全扫描。其余情况选择全表扫描。

关于COUNT的优化

统计记录条数,如何才能最快?xb_audit_ddl_lhr表有2303262记录。

  1. 全表扫描:

主键索引

位图索引

在数据量超大的表上,还可以采用位图索引并行的方式。优化无止境,根据场景选择最适合的才是最好的。

参考文章

How the Oracle CBO Chooses a Path for the SELECT COUNT(*) Command (文档 ID 124717.1)

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

You Asked

and we said...

nothing, they are the same, incur the same amount of work -- do the same thing, take the same amount of resources.

You can see this via:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

Same number of blocks read/written/processed, same cpu times (basically) same elapsed times (basically).

they are identical.

Anyone who thinks different (and I know you are out there) will have to post a test case like the above or some scientific proof otherwise to be taken seriously....

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

6 + 14 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部