在平時的工作中，有時候需要準備一些腳本，比如能夠簡單驗證一下表是否可通路，或者驗證表中有無資料等。
今天在測試環境進行了簡單的模拟，發現還是有很大的差别。
簡單來說，要實作如上的需求有兩種方式，一種是通過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
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 |
1 - filter(ROWNUM
5 recursive calls
4 consistent gets
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 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
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 |
162 recursive calls
234 consistent gets
8 sorts (memory)
SQL> select rowid from AGREEMENT_PARAM partition(AMAXVALUE) where rownum
Elapsed: 00:00:00.01
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 |
1 recursive calls
537 bytes sent via SQL*Net to client
可以看到，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
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)