关于验证表中有无数据的方法比较(r2笔记54天)

在平时的工作中，有时候需要准备一些脚本，比如能够简单验证一下表是否可访问，或者验证表中有无数据等。 今天在测试环境进行了简单的模拟，发现还是有很大的差别。 简单来说，要实现如上的需求有两种方式，一种是通过count来判断，另外一种是通过rowid来判断。 举个例子。 先来看一个大表，但是某个分区没有数据的情况。 select count(1) from APP_TMP.INVOICE partition(A8_B8) where rownum<=1; 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<=1) 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<=1 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<=1) 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<1; 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<1) 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<2; 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<2) 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