使用ROWNUM將導致查詢結果集的固化
今天一個朋友找我看看一個現象是不是bug,如果直接查詢報錯,如果巢狀一層加上ROWNUM則不會報錯,如果去掉ROWNUM就會導致錯誤重現,不管ROWNUM中指定查詢多少行,都會避免錯誤的產生。
首先來看這個錯誤:
SQL> SELECT COUNT(*)
2 FROM T_BID_ITEM A, T_BID B
3 WHERE A.BID_ORGID = B.BID_ORGID
4 AND A.INVITE_ID = '3003'
5 AND B.INVITE_ID = '3003'
6 AND B.ENCRYPT_FLAG = 'N'
7 AND TO_NUMBER(BID_PRICE) > 1;
AND TO_NUMBER(BID_PRICE) > 1
*
第 7 行出現錯誤:
ORA-01722: 無效數字
SQL> SELECT COUNT(*)
2 FROM (SELECT A.BID_PRICE
3 FROM T_BID_ITEM A, T_BID B
4 WHERE A.BID_ORGID = B.BID_ORGID
5 AND A.INVITE_ID = '3003'
6 AND B.INVITE_ID = '3003'
7 AND ROWNUM < 10000000000
8 AND B.ENCRYPT_FLAG = 'N')
9 WHERE BID_PRICE > 1;
COUNT(*)
----------
26559
SQL> SELECT COUNT(*)
2 FROM (SELECT A.BID_PRICE
3 FROM T_BID_ITEM A, T_BID B
4 WHERE A.BID_ORGID = B.BID_ORGID
5 AND A.INVITE_ID = '3003'
6 AND B.INVITE_ID = '3003'
7 --AND ROWNUM < 10000000000
8 AND B.ENCRYPT_FLAG = 'N')
9 WHERE BID_PRICE > 1;
WHERE BID_PRICE > 1
*
第 9 行出現錯誤:
ORA-01722: 無效數字
SQL> SELECT COUNT(*) FROM T_BID_ITEM;
COUNT(*)
----------
26824
SQL> SELECT COUNT(*) FROM T_BID;
COUNT(*)
----------
4131
正如文章開頭描述的,改變了查詢的寫法後,錯誤有可能消失。使用巢狀子查詢的方式,並使用ROWNUM,錯誤就不再出現,而如果去掉ROWNUM,則錯誤出現。錯誤出現與否只與是否包含ROWNUM有關,而和ROWNUM指定的記錄數無關。可以看到,兩個表的記錄數都不大,即使笛卡兒積的結果也不會超過ROWNUM指定的10000000000。
其實產生這種情況的原因很簡單,ROWNUM有固定結果集的功能,在一個子查詢中使用ROWNUM,會導致結果集被固化,有點類似Oracle中NO_MERGE加NO_PUSH_PRED提示的功能。檢查這兩個SQL的執行計劃就一目瞭然了:
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*)
3 FROM (SELECT A.BID_PRICE
4 FROM T_BID_ITEM A, T_BID B
5 WHERE A.BID_ORGID = B.BID_ORGID
6 AND A.INVITE_ID = '3003'
7 AND B.INVITE_ID = '3003'
8 --AND ROWNUM < 10000000000
9 AND B.ENCRYPT_FLAG = 'N')
10 WHERE BID_PRICE > 1;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 1595260930
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 322 | 19 (6)|
| 1 | SORT AGGREGATE | | 1 | 322 | |
|* 2 | HASH JOIN | | 30 | 9660 | 19 (6)|
|* 3 | TABLE ACCESS BY INDEX ROWID| T_BID_ITEM | 28 | 8148 | 4 (0)|
|* 4 | INDEX RANGE SCAN | T_BID_ITEM_INVITE | 225 | | 1 (0)|
|* 5 | TABLE ACCESS FULL | T_BID | 1059 | 32829 | 14 (0)|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."BID_ORGID"="B"."BID_ORGID")
3 - filter(TO_NUMBER("A"."BID_PRICE")>1)
4 - access("A"."INVITE_ID"=3003)
5 - filter("B"."INVITE_ID"=3003 AND "B"."ENCRYPT_FLAG"='N')
已選擇20行。
為了顯示方便,將執行計劃中的時間部分去掉了。
可以看到,Oracle執行索引掃描後,在掃描T_BID_ITEM表的時候執行TO_NUMBER(BID_PRICE)報錯。
而如果加上ROWNUM則執行計劃變為:
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*)
3 FROM (SELECT A.BID_PRICE
4 FROM T_BID_ITEM A, T_BID B
5 WHERE A.BID_ORGID = B.BID_ORGID
6 AND A.INVITE_ID = '3003'
7 AND B.INVITE_ID = '3003'
8 AND ROWNUM < 10000000000
9 AND B.ENCRYPT_FLAG = 'N')
10 WHERE BID_PRICE > 1;
已解釋。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 3192764333
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 252 | 171 (3)|
| 1 | SORT AGGREGATE | | 1 | 252 | |
|* 2 | VIEW | | 27484 | 6763K| 171 (3)|
|* 3 | COUNT STOPKEY | | | | |
|* 4 | HASH JOIN | | 27484 | 8642K| 171 (3)|
|* 5 | TABLE ACCESS FULL| T_BID | 1059 | 32829 | 14 (0)|
|* 6 | TABLE ACCESS FULL| T_BID_ITEM | 25946 | 7373K| 156 (2)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("BID_PRICE")>1)
3 - filter(ROWNUM<10000000000)
4 - access("A"."BID_ORGID"="B"."BID_ORGID")
5 - filter("B"."INVITE_ID"=3003 AND "B"."ENCRYPT_FLAG"='N')
6 - filter("A"."INVITE_ID"=3003)
Note
-----
- dynamic sampling used for this statement
已選擇26行。
這時的限定條件TO_NUMBER(BIT_PRICE) > 1在第2步執行,這步發生在HASH JOIN操作完成之後。由於ROWNUM語句的存在使得子查詢結果集固化,外層的查詢條件TO_NUMBER(BIT_PRICE) > 1沒有被推到內層查詢中,使得導致錯誤產生的記錄在進行HASH JOIN的時候被過濾掉,因此這個查詢可以順利執行。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-662899/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 等於NULL的查詢條件導致查詢結果不正確Null
- 巧用臨時表將大結果集轉換為小結果集驅動查詢薦
- Mybatis 查詢語句結果集總結MyBatis
- 為什麼Oracle的外連線寫法不同導致查詢結果不同?Oracle
- 使用查詢結果更新表的方法
- PostgreSQL函式:返回表查詢結果集SQL函式
- DBeaver 匯出多個查詢結果集
- MySQL 將查詢結果匯出到檔案MySql
- 備忘:laravel 對查詢結果集可以迴圈where查詢Laravel
- 連結伺服器查詢導致的阻塞伺服器
- SQL查詢結果集對注入的影響及利用SQL
- JAVA + Oracle儲存過程返回查詢結果集JavaOracle儲存過程
- PB帶引數帶結果集的動態SQL查詢SQL
- mysql查詢結果多列拼接查詢MySql
- SpringBoot分頁查詢 頁碼問題導致返回結果數量為0Spring Boot
- arcgis api for javascript學習-使用FeatureLayer結合FeatureTable實現查詢並能根據查詢結果將檢視縮放至結果的範圍APIJavaScript
- 查詢與插入資料使用rownum與level
- UTL_FILE包,將查詢結果寫入檔案
- Oracle Database 12c查詢最佳化器的缺陷-外連線導致結果不準確OracleDatabase
- navicat 匯出查詢結果
- mysql匯出查詢結果MySql
- SQL 把查詢結果當作"表"來使用SQL
- oracle 查詢結果的各種格式Oracle
- 【記錄】sqlserver列轉行查詢 並將查詢結果用逗號分隔開SQLServer
- xsl中,對xml文件查詢的結果再次查詢XML
- Mybatis底層原理學習(三):查詢結果集的處理原理MyBatis
- 演示字符集不同導致插入,查詢產生亂碼的過程
- 如何令sql查詢的結果集與客戶提供的excel表格中列的資料保持一致SQLExcel
- Mybatis模糊查詢結果為空MyBatis
- ps aux查詢結果解釋UX
- 查詢結果集很小,但 Created_tmp_disk_tables 不斷增加
- 在sqlplus下格式化輸出查詢結果集SQL
- PLSQL Language Referenc-PL/SQL靜態SQL-帶有子查詢的查詢結果集處理SQL
- 單個分割槽索引失效導致繫結變數查詢無法使用索引索引變數
- MySQL查詢結果匯出方式總結MySql
- oracle中基於ROWNUM的查詢的返回Oracle
- SQL語句將查詢結果插入到另一張表中SQL
- [Mysql 查詢語句]——對查詢結果進一步的操作MySql