COUNT（rowid）比COUNT（*）快吗？

在Oracle论坛和新闻组中长期存在的讨论一直是使用count（*）从给定表返回行计数的效率。现在，该讨论中出现的新问题是引入了count（rowid）作为更有效的替代方法。该参数指出count（*）扩展了整个列列表，就像“ select * …”一样，因此，当所需表中存在CLOB列时，它可能是资源宿。让我们看一下该论点，看看它是否成立。让我们开始创建并填充一个包含CLOB列的表：

SQL>
SQL> create table count_test(
  2  id      number,
  3  val     varchar2(40),
  4  clb     clob);

Table created.

SQL>
SQL> begin
  2          for z in 1..1000000 loop
  3             insert into count_test
  4             values(z, 'Record '||z, 'Clob value '||z);
  5          end loop;
  6
  7          commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

接下来，将事件10053设置为转储优化器跟踪，以便我们可以看到Oracle计划如何执行count（）查询：

SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

设置了阶段，让我们运行count（）的一些变体以查看Oracle的行为。首先，我们将执行一个直接count(*) 并显示计划：

SQL> select count(*) from count_test;

  COUNT(*)
----------
   1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(*) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K |       |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+      
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>

查看生成的跟踪文件，Oracle仅按原样使用count（*）返回结果：

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "BING"."COUNT_TEST" "COUNT_TEST"          
...
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K |       |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+                 
Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------
1 - SEL$1
2 - SEL$1                / "COUNT_TEST"@"SEL$1"
------------------------------------------------------------
Predicate Information:
------------------------

SQL>

那里没有惊喜；注意，Oracle不会将“ *”扩展到表中的所有列，在这种情况下，“ *”表示将对所有行进行计数。如果提供了实际的列名，那么Oracle将对指定列中的值进行计数。现在让我们看一下Oracle对count（rowid）查询的作用：

SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(rowid) from count_test;

COUNT(ROWID)
------------
     1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(rowid) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |  3582 |           |
| 1   |  SORT AGGREGATE     |           |     1 |    12 |       |           |
| 2   |   TABLE ACCESS FULL | COUNT_TEST|  848K | 9941K |  3582 |  00:00:43 |
----------------------------------------+-----------------------------------+           

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(ROWID)[22]
   2 - (rowset=256) ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>

Oracle为表中的每一行生成一个rowid值，该操作将消耗一些CPU资源。由于查询返回的时间大致与count（*）版本相同，因此性能“命中”似乎可以忽略不计。添加主键会稍微更改计划，但不会更改查询文本：

SQL> alter table count_test add constraint count_pk primary key(id);

Table altered.                                                                                                                            
SQL>
SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(*) from count_test;

  COUNT(*)
----------
   1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(*) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 371675025

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   589   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| COUNT_PK |   848K|   589   (2)| 00:00:01 |
--------------------------------------------------------------------------        

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (rowset=1019)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>
SQL>
SQL> alter session set events = '10053 trace  name context forever, level 2';

Session altered.

SQL> select count(rowid) from count_test;

COUNT(ROWID)
------------
     1000000

SQL> alter session set events = '10053 trace name context off';

Session altered.

SQL> explain plan for select count(rowid) from count_test;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'projection'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 371675025

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |    12 |   589   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |    12 |            |          |
|   2 |   INDEX FAST FULL SCAN| COUNT_PK |   848K|  9941K|   589   (2)| 00:00:01 |
----------------------------------------------------------------------------------                 
Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(ROWID)[22]
   2 - (rowset=256) ROWID[ROWID,10]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

19 rows selected.

SQL>
SQL> spool off
commit;

添加主键后，10053跟踪详细信息没有更改。

看来从该实验中已经收集到两条信息-当表包含CLOB列并且count（*）不会将列列表扩展为 select *时，count（rowid）并不比count（*）好。这样做（并且没有理由认为应该这样做）。

作者：David Fitzjarrell
文章来源：https://www.databasejournal.com/features/oracle/is-countrowid-faster-than-count.html