COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码：我准备了. 4个Demo...

COUNT(*)、COUNT(1)、COUNT(col_pk)、COUNT(col_bi)的查询性能比较

赵全文

2019/12/21

关于我

• 具有6年多的Oracle工作经验，擅长Oracle数据库的SQL脚本编写、故

障诊断和性能优化，并且乐于分享Oracle技术，在今年4月荣幸地成为

了Oracle ACE Associate；

• 曾在多个微信公众号上多次发表原创文章；

• 长期坚持在Github发布自己的Linux Shell脚本和SQL、PL/SQL代码；

• 从2018年开始在个人博客Word Press上发表原创文章，至今有35篇；

• Github：https://github.com/guestart

• Word Press：https://quanwenzhao.wordpress.com

• 热衷于和国内外专家朋友们交流Oracle技术；

https://github.com/guestart

https://quanwenzhao.wordpress.com/

联系我

• E-mail: [email protected]

• Q Q: 574266540

• WeChat: guestart

• LinkedIn: http://www.linkedin.com/in/quanwen-zhao-6ba9a4137/

• Twitter: https://www.twitter.com/quanwen_zhao

http://www.linkedin.com/in/quanwen-zhao-6ba9a4137/

https://www.twitter.com/quanwen_zhao

主要内容：

• 背景介绍

• Demo代码

• 查看真实的执行计划，分析10053生成的trc文件

• 通过PL/SQL查看消耗时间

背景介绍：UKOUG 主席 Martin Wildlake 在11月28日的 Twitter 上发起了一个主题贴的投票，

他是这么描述的。在表t上有一个主键列PK，在下面的几种情况当中，哪一个是最快的？

背景介绍：UKOUG 主席 Martin Wildlake 在11月28日的 Twitter 上发起了一个主题贴的投票，

他是这么描述的。在表t上有一个主键列PK，在下面的几种情况当中，哪一个是最快的？

Let us have fun!

主要内容：

• 背景介绍

• Demo代码



Demo代码：

我准备了4个Demo，每一个都有四个部分组成(在 Oracle 18.3.0.0.0 上测试)：

• 创建用户QWZ,表空间QWZ，临时表空间QWZ_TMP，并给用户QWZ授予一定的系统、角色和对

象权限；

• 创建4个不同的测试表，test(没有任何约束和索引)，test_pk(只有主键约束)，

test_bi(只有位图索引)，test_pk_bi(主键约束和位图索引都有)；每个表中都有5e7行的

数据，列全部是id和flag，其中id是从1递增到5e7，flag有4个重复值：low,mid,high和

unknown；

• 分别在4个不同的Session窗口通过查询COUNT(*),COUNT(1),COUNT(id),COUNT(flag)上面4

张表的返回行数来观察他们的真实执行计划，并通过10053生成的trc文件来真正理解

Oracle为什么会选择这样的执行计划；

• 通过一小段PL/SQL代码来观察COUNT(*),COUNT(1),COUNT(id),COUNT(flag)执行完成消耗

的时间有什么规律；

Demo代码：

第一部分

Demo代码：第二部分(1)

Demo代码：

第二部分(1)

Demo代码：第三部分(1)

Demo代码：

第四部分(1)

ER Diagram

Demo代码：

第四部分(1)

Demo代码：

第四部分(1)

第四部分(2)

Demo代码：

第四部分(3)

主要内容：

• 背景介绍

• Demo代码



操作步骤：1.执行Demo的第一、二部分

2.执行创建4个测试表的存储过程SQL> execute crt_tab_test;

PL/SQL procedure successfully completed.

Elapsed: 00:01:54.62

SQL> execute crt_tab_test_pk;


Elapsed: 00:02:42.14

SQL> execute crt_tab_test_bi;


Elapsed: 00:02:33.96

SQL> execute crt_tab_test_pk_bi;


Elapsed: 00:03:15.81

操作步骤：

3.执行Demo的第三部分

4.查看其执行计划和10053生成的trc文件

-- Session 1:

SQL_ID 068c88cnk1xkm, child number 1-------------------------------------SELECT COUNT(*) FROM test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.07 | 116K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.07 | 116K|| 2 | TABLE ACCESS FULL| TEST | 1 | 50M| 50M|00:00:01.05 | 116K|-------------------------------------------------------------------------------------

操作步骤：


-- Session 1:

SQL_ID 1axq6b0shb1q2, child number 0-------------------------------------SELECT COUNT(1) FROM test



操作步骤：


-- Session 1:

SQL_ID aycmyd7304cnf, child number 0-------------------------------------SELECT COUNT(id) FROM test



操作步骤：


-- Session 1:

SQL_ID 5rupd7t2fc8dw, child number 0-------------------------------------SELECT COUNT(flag) FROM test



-- Session 2:

SQL_ID 7u8r76f06gscq, child number 2-------------------------------------SELECT COUNT(*) FROM test_pk


---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:07.35 | 111K| 102K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:07.35 | 111K| 102K|| 2 | INDEX FAST FULL SCAN| TEST_ONLY_PK | 1 | 50M| 50M|00:00:05.44 | 111K| 102K|---------------------------------------------------------------------------------------------------------

操作步骤：


操作步骤：


操作步骤：


-- Session 2:

SQL_ID gu1kdcchtdc2d, child number 0-------------------------------------SELECT COUNT(1) FROM test_pk


------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.88 | 111K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.88 | 111K|| 2 | INDEX FAST FULL SCAN| TEST_ONLY_PK | 1 | 50M| 50M|00:00:04.00 | 111K|------------------------------------------------------------------------------------------------

操作步骤：


-- Session 2:

SQL_ID 1a55zxg4za05m, child number 0-------------------------------------SELECT COUNT(id) FROM test_pk


------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.94 | 111K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.94 | 111K|| 2 | INDEX FAST FULL SCAN| TEST_ONLY_PK | 1 | 50M| 50M|00:00:04.05 | 111K|------------------------------------------------------------------------------------------------

操作步骤：


-- Session 2:

SQL_ID 1ca9t4kpd04zh, child number 0-------------------------------------SELECT COUNT(flag) FROM test_pk


----------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.88 | 116K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.88 | 116K|| 2 | TABLE ACCESS FULL| TEST_PK | 1 | 50M| 50M|00:00:01.79 | 116K|----------------------------------------------------------------------------------------

操作步骤：


操作步骤：


-- Session 3:

SQL_ID c6313xfz7kyn6, child number 1-------------------------------------SELECT COUNT(*) FROM test_bi


--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.05 | 1046 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 1046 || 2 | BITMAP CONVERSION COUNT | | 1 | 50M| 2065 |00:00:00.05 | 1046 || 3 | BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI | 1 | | 2065 |00:00:00.03 | 1046 |--------------------------------------------------------------------------------------------------------

操作步骤：4.查看其执行计划和10053生成的trc文件

操作步骤：


-- Session 3:

SQL_ID 6br8rnjjc4xvb, child number 0-------------------------------------SELECT COUNT(1) FROM test_bi


--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 1046 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 1046 || 2 | BITMAP CONVERSION COUNT | | 1 | 50M| 2065 |00:00:00.03 | 1046 || 3 | BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI | 1 | | 2065 |00:00:00.01 | 1046 |--------------------------------------------------------------------------------------------------------

操作步骤：


-- Session 3:

SQL_ID a7gq7x9hyhcg0, child number 1-------------------------------------SELECT COUNT(id) FROM test_bi


----------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.09 | 116K|| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.09 | 116K|| 2 | TABLE ACCESS FULL| TEST_BI | 1 | 50M| 50M|00:00:01.99 | 116K|----------------------------------------------------------------------------------------

操作步骤：


-- Session 3:

SQL_ID 1xkds9uk68hmq, child number 1-------------------------------------SELECT COUNT(flag) FROM test_bi


--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.27 | 1046 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.27 | 1046 || 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 50M| 50M|00:00:02.92 | 1046 || 3 | BITMAP INDEX FAST FULL SCAN| TEST_ONLY_BI | 1 | | 2065 |00:00:00.01 | 1046 |--------------------------------------------------------------------------------------------------------

操作步骤：


-- Session 4:

SQL_ID djgsn24c8hpb2, child number 0-------------------------------------SELECT COUNT(*) FROM test_pk_bi


---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.05 | 1046 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.05 | 1046 || 2 | BITMAP CONVERSION COUNT | | 1 | 50M| 2065 |00:00:00.05 | 1046 || 3 | BITMAP INDEX FAST FULL SCAN| TEST_BI | 1 | | 2065 |00:00:00.03 | 1046 |---------------------------------------------------------------------------------------------------

操作步骤：


操作步骤：


-- Session 4:

SQL_ID 93s6bvzpqgxbt, child number 0-------------------------------------SELECT COUNT(1) FROM test_pk_bi



操作步骤：


-- Session 4:

SQL_ID bsu77u1gch17z, child number 0-------------------------------------SELECT COUNT(id) FROM test_pk_bi



操作步骤：


-- Session 4:

SQL_ID 3awj9bwtsbms9, child number 0-------------------------------------SELECT COUNT(flag) FROM test_pk_bi


---------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:05.28 | 1046 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:05.28 | 1046 || 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 50M| 50M|00:00:02.92 | 1046 || 3 | BITMAP INDEX FAST FULL SCAN| TEST_BI | 1 | | 2065 |00:00:00.01 | 1046 |---------------------------------------------------------------------------------------------------

主要内容：

• 背景介绍

• Demo代码



通过PL/SQL查看消耗时间：

1.执行Demo的第四部分

2.查看表cnt_method的内容

SQL> SELECT * FROM cnt_method ORDER BY ROWID;

MARK----*1idflag

3.查看表tab_stru的内容

SQL> SELECT * FROM tab_stru;

NAME MARK---------- ----------test no_pk_bitest_pk only_pktest_bi only_bitest_pk_bi both_pk_bi


4.执行存储过程cnt_tab_spd_time

SQL> EXECUTE cnt_tab_spd_time;


Elapsed: 00:00:16.33

5.查看表cnt_spd_time的内容

SQL> select * from cnt_spd_time;

TAB_NUM TAB_MARK CNT_ SPD_TIME T---------- ---------- ---- ---------- -

50000000 no_pk_bi * 1.782916 s50000000 no_pk_bi 1 .836581 s50000000 no_pk_bi id 1.384254 s50000000 no_pk_bi flag 1.389433 s50000000 only_pk * 2.052727 s50000000 only_pk 1 1.650235 s50000000 only_pk id 1.641972 s50000000 only_pk flag 1.805216 s50000000 only_bi * .022012 s50000000 only_bi 1 .012348 s50000000 only_bi id 1.778004 s50000000 only_bi flag .935891 s50000000 both_pk_bi * .022172 s50000000 both_pk_bi 1 .012238 s50000000 both_pk_bi id .011847 s50000000 both_pk_bi flag .93866 s

16 rows selected.


6.分别从横向和纵向两个维度，统计和分析查询测试表返回总行数需要的时间

(1)横向维度：

SQL> SELECT * FROM cnt_spd_time WHERE tab_mark = 'no_pk_bi'ORDER BY ROWID;


50000000 no_pk_bi * 1.782916 s50000000 no_pk_bi 1 .836581 s50000000 no_pk_bi id 1.384254 s50000000 no_pk_bi flag 1.389433 s



(1)横向维度：

SQL> SELECT * FROM cnt_spd_time WHERE tab_mark = 'only_pk'ORDER BY ROWID;


50000000 only_pk * 2.052727 s50000000 only_pk 1 1.650235 s50000000 only_pk id 1.641972 s50000000 only_pk flag 1.805216 s



(1)横向维度：

SQL> SELECT * FROM cnt_spd_time WHERE tab_mark = 'only_bi'ORDER BY ROWID;


50000000 only_bi * .022012 s50000000 only_bi 1 .012348 s50000000 only_bi id 1.778004 s50000000 only_bi flag .935891 s



(1)横向维度：

SQL> SELECT * FROM cnt_spd_time WHERE tab_mark = 'both_pk_bi'ORDER BY ROWID;


50000000 both_pk_bi * .022172 s50000000 both_pk_bi 1 .012238 s50000000 both_pk_bi id .011847 s50000000 both_pk_bi flag .93866 s



(2)纵向维度：

SQL> SELECT * FROM cnt_spd_time WHERE cnt_mark = '*'ORDER BY ROWID;


50000000 no_pk_bi * 1.782916 s50000000 only_pk * 2.052727 s50000000 only_bi * .022012 s50000000 both_pk_bi * .022172 s



(2)纵向维度：

SQL> SELECT * FROM cnt_spd_time WHERE cnt_mark = '1'ORDER BY ROWID;


50000000 no_pk_bi 1 .836581 s50000000 only_pk 1 1.650235 s50000000 only_bi 1 .012348 s50000000 both_pk_bi 1 .012238 s



(2)纵向维度：

SQL> SELECT * FROM cnt_spd_time WHERE cnt_mark = 'id'ORDER BY ROWID;


50000000 no_pk_bi id 1.384254 s50000000 only_pk id 1.641972 s50000000 only_bi id 1.778004 s50000000 both_pk_bi id .011847 s



(2)纵向维度：

SQL> SELECT * FROM cnt_spd_time WHERE cnt_mark = 'flag'ORDER BY ROWID;


50000000 no_pk_bi flag 1.389433 s50000000 only_pk flag 1.805216 s50000000 only_bi flag .935891 s50000000 both_pk_bi flag .93866 s

结论：

• 每一个测试表test,test_pk,test_bi,test_pk_bi无论在查询

COUNT(*),COUNT(1),COUNT(id),COUNT(flag)具体使用了哪种访问路径，这些不重要；

• 最重要的是，首先去了解你要查询的表的逻辑结构，有哪些列，列的数据类型是什么，

列上带有哪些约束和索引；

• 其次再写特定业务场景的SQL语句；

• 而现实生活中，我们首先做的是第3步，往往忽略第1、2步；

参考文章：

• https://jonathanlewis.wordpress.com/2008/10/31/count/

• https://jonathanlewis.wordpress.com/2012/10/19/count-3/

• Measuring Elapsed Time to Run a SQL Query in PL/SQL

• https://community.oracle.com/thread/4305493

• https://community.oracle.com/thread/4304992

https://jonathanlewis.wordpress.com/2008/10/31/count/

https://jonathanlewis.wordpress.com/2012/10/19/count-3/

https://livesql.oracle.com/apex/livesql/file/content_H74H3DKGHTTOP7TA7VOTAXSOW.html

https://community.oracle.com/thread/4305493

https://community.oracle.com/thread/4304992

谢谢聆听 !

COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码：我准备了. 4个Demo...

Documents

Transcript of COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码：我准备了. 4个Demo...

COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码： 我准备了. 4个Demo...

Documents

Transcript of COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码： 我准备了. 4个Demo...

COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码：我准备了. 4个Demo...

Transcript of COUNT(*) COUNT(1) COUNT(col pk) COUNT(col bi)的查询性 …...Demo代码：我准备了. 4个Demo...