在平时的工作中，有时候需要准备一些脚本，比如能够简单验证一下表是否可访问，或者验证表中有无数据等。
今天在测试环境进行了简单的模拟，发现还是有很大的差别。
简单来说，要实现如上的需求有两种方式，一种是通过count来判断，另外一种是通过rowid来判断。
举个例子。
先来看一个大表，但是某个分区没有数据的情况。
select count(1) from APP_TMP.INVOICE partition(A8_B8) where rownum
Execution Plan
----------------------------------------------------------
Plan hash value: 1238501171
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 1 (0)| 00:00:01 | 39 | 39 |
| 4 | INDEX FULL SCAN | INVOICE_1IX | 1 | 1 (0)| 00:00:01 | 39 | 39 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM
Statistics
----------------------------------------------------------
1736 recursive calls
0 db block gets
7308 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
18 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select rowid from APP_TMP.INVOICE partition(A8_B8) where rownum
2 /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1950573833
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 12 | 1 (0)| 00:00:01 | 39 | 39 |
| 3 | INDEX FULL SCAN | INVOICE_1IX | 1 | 12 | 1 (0)| 00:00:01 | 39 | 39 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
大体来说，查询时间都基本一致，可能使用Rowid的方式效率要略微好一些，这两种方式采用的执行计划也是不同的。注意如上标黄的部分。
再来测试一个大表中分区数据最多的。
SQL> alter session force parallel query parallel 16;
Session altered.
Elapsed: 00:00:00.00
SQL> select count(1) from AGREEMENT_PARAM partition(AMAXVALUE);
78085245
Elapsed: 00:00:04.89
数据有7千多万，算比较多的了。
然后再次尝试count,和rowid方式
SQL> select count(1) from AGREEMENT_PARAM partition(AMAXVALUE) where rownum
Execution Plan
----------------------------------------------------------
Plan hash value: 2234036749
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41914 (1)| 00:08:23 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 78M| 41914 (1)| 00:08:23 | 11 | 11 |
| 4 | INDEX FULL SCAN | AGREEMENT_PARAM_PK | 78M| 41914 (1)| 00:08:23 | 11 | 11 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM
Statistics
----------------------------------------------------------
162 recursive calls
0 db block gets
234 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select rowid from AGREEMENT_PARAM partition(AMAXVALUE) where rownum
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 4116254344
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 12 | 1 (0)| 00:00:01 | 11 | 11 |
| 3 | INDEX FULL SCAN | AGREEMENT_PARAM_PK | 1 | 12 | 1 (0)| 00:00:01 | 11 | 11 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
537 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
可以看到，rowid的优势就出来了，查询速度要快的多。时间上提高了很多倍。逻辑读也少了很了很多。
所以大家在平时准备类似的脚本的时候，可以优先考虑rowid，毕竟这是oracle底层支持比较好的方案。
最后有的朋友，可能疑惑为什么不适用rowid=0这种方式呢。可能效果还要好些。
测试结果如下。我就不等待它执行完成了，执行了40秒还是没有反应。
1* select count(1) from AGREEMENT_PARAM partition(AMAXVALUE) where rownum=0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:00:39.94