關於驗證表中有無資料的方法比較
在平時的工作中，有時候需要準備一些指令碼，比如能夠簡單驗證一下表是否可訪問，或者驗證表中有無資料等。
今天在測試環境進行了簡單的模擬，發現還是有很大的差別。
簡單來說，要實現如上的需求有兩種方式，一種是透過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
今天在測試環境進行了簡單的模擬，發現還是有很大的差別。
簡單來說，要實現如上的需求有兩種方式，一種是透過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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1347071/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於表的資料塊驗證 DUMP 工具 使用
- 關閉資料庫比較好的方法資料庫
- EJBQL中關於日期型資料的比較
- 關於資料庫物件版本比較的指令碼資料庫物件指令碼
- 1.6.3.1. 關於資料庫管理員身份驗證方法資料庫
- oracle資料庫兩表資料比較Oracle資料庫
- 關於資料庫管理員的驗證小結資料庫
- 表單資料驗證
- 比較兩個表的資料差別
- 關於重寫 Laravel 表單驗證器 Request 返回 JSON 的資料格式LaravelJSON
- 關於資料泵impdp引數驗證（一）
- beego表達資料驗證Go
- 批量更新資料方法比較測試
- 關於谷歌賬號註冊手機號無法驗證的解決方法谷歌
- 關於Oracle字元型別的比較Oracle字元型別
- 有關資料驗證的原則
- 關於9i RMAN 增量備份中的資料塊SCN比較
- 關於Laravel的表單驗證分層設計以及驗證場景的應用Laravel
- 關於Go tools的比較有用的flagsGo
- 資料關係比較：相關性 vs 因果關係
- sap資料--關於收到部分還款SAP系統兩種處理方法的說明和比較
- 令人比較失落的IT圈子-關於華為裁員
- 如何比較兩個資料庫表結構的不同資料庫
- 生活資料：關於撒謊，你心中有“數”嗎？
- ACCESS 關於表設計中,驗證規則的使用技巧
- 比較兩個mysql資料庫裡面的表是否相同的一個校驗指令碼MySql資料庫指令碼
- angular中的表單資料自定義驗證Angular
- 關於Java Mail的身份驗證！JavaAI
- DataTable資料批量寫入資料庫三種方法比較資料庫
- 物件導向建模與資料表建模兩種分析設計方法的比較的思考物件
- 比較兩個資料庫的表結構差異(轉)資料庫
- 關於plsql，crt登入比較慢SQL
- 關於c、c++之前比較模糊的概念C++
- 關於SQL 2008資料庫壓縮後收縮（database shrink）的測試實驗和效能比較SQL資料庫Database
- angular中關於表單動態驗證的一種新思路Angular
- 使用表單驗證，建立資料驗證層，Ajax 統一返回驗證錯誤資訊
- 圖資料庫比較資料庫
- 資料結構比較資料結構