ORACLE使用WITH AS和HINT MATERIALIZE優化SQL解決FILTER效率低下

原文：http://blog.csdn.net/liangweiwei130/article/details/37882503

-------------------------------------------------

在做項目的過程中，一個頁面使用類似如下的SQL查詢數據，為了保密和使用方便，我把項目中有關的表名和字段替換使用ORACLE數據庫中的系統表和字段。

在我所做的項目中，類似ALL_TABLES的表中大概有8W多條數據，下面這個查詢SQL很慢。


[sql]?view plain?copy
?print?
  1. WITH?PARAMS?AS??
  2. ?(SELECT?''?USER_ID,?''?SDATE,?'%'?||?''?||?'%'?SNAME?FROM?DUAL)??
  3. SELECT?AU.USERNAME,?AU.USER_ID??
  4. ??FROM?ALL_USERS?AU??
  5. ?INNER?JOIN?PARAMS?PA??
  6. ????ON?1?=?1??
  7. ?INNER?JOIN?DBA_USERS?DU??
  8. ????ON?AU.USERNAME?=?DU.USERNAME??
  9. ?WHERE?((PA.SDATE?IS?NULL?AND?PA.USER_ID?IS?NOT?NULL?AND??
  10. ???????AU.USER_ID?=?PA.USER_ID)?OR??
  11. ?????????
  12. ???????(PA.SDATE?IS?NULL?AND?PA.USER_ID?IS?NULL?AND??
  13. ???????AU.USERNAME?NOT?IN??
  14. ???????(SELECT?AU.USERNAME??
  15. ????????????FROM?ALL_USERS?AU??
  16. ???????????INNER?JOIN?DBA_USERS?DEV??
  17. ??????????????ON?AU.USERNAME?=?DEV.USERNAME??
  18. ???????????INNER?JOIN?(SELECT?OWNER?AS?USERNAME??
  19. ????????????????????????FROM?ALL_TABLES?T??
  20. ???????????????????????WHERE?T.LAST_ANALYZED?=?TRUNC(SYSDATE))?ATA??
  21. ??????????????ON?AU.USERNAME?=?ATA.USERNAME))?OR??
  22. ???????(PA.SDATE?IS?NOT?NULL?AND??
  23. ???????AU.USERNAME?IN??
  24. ???????(SELECT?AU.USERNAME??
  25. ????????????FROM?ALL_USERS?AU??
  26. ???????????INNER?JOIN?DBA_USERS?PA??
  27. ??????????????ON?AU.USERNAME?=?PA.USERNAME??
  28. ???????????INNER?JOIN?ALL_TABLES?ATA??
  29. ??????????????ON?PA.USERNAME?=?ATA.OWNER??
  30. ???????????WHERE?TO_CHAR(ATA.LAST_ANALYZED,?'YYYY-MM-DD')?=?PA.SDATE)?AND??
  31. ???????AU.USER_ID?=?PA.USER_ID))??
  32. ???AND?DU.PROFILE?LIKE?'D%'??
  33. ???AND?AU.USERNAME?LIKE?PA.SNAME??

針對上面的SQL語句執行慢的問題，我做了如下的分析：


????????????????第一步，把語句的WHERE條件后的三個OR都分別和主查詢一塊執行，執行速度都很快，放到一塊就很慢。


????????????????第二步，對比上面SQL和三個OR拆分出來的三個SQL的執行計劃，如下圖所示。發現上面SQL的執行中有一個FILTER，過濾器謂詞中用到了NOT EXISTS，是導致這條SQL跑的慢的原因。



原因找到了，就得想辦法把執行計劃的FILTER去掉。開始想加HINT，但是實驗了很多HINT，都不起作用。最后的結果還一樣，后來想到WITH AS?能提高SQL的查詢速度，就把影響SQL執行的那段SQL放到WITH AS里面，結果還是一樣。后來嘗試把HINT?MATERIALIZE和WITH AS?結合使用，修改成如下的SQL，查詢速度立即提升了很多。如下圖所示，執行計劃中FILTER的NOT EXISTS不存在了。

[sql]?view plain?copy
?print?
  1. WITH?PARAMS?AS??
  2. ?(SELECT?''?USER_ID,?''?SDATE,?'%'?||?''?||?'%'?SNAME?FROM?DUAL),??
  3. USERNAMEDATA?AS??
  4. ?(SELECT?/*+?materialize?*/??
  5. ???AU.USERNAME??
  6. ????FROM?ALL_USERS?AU??
  7. ???INNER?JOIN?DBA_USERS?DEV??
  8. ??????ON?AU.USERNAME?=?DEV.USERNAME??
  9. ???INNER?JOIN?(SELECT?OWNER?AS?USERNAME??
  10. ????????????????FROM?ALL_TABLES?T??
  11. ???????????????WHERE?T.LAST_ANALYZED?=?TRUNC(SYSDATE))?ATA??
  12. ??????ON?AU.USERNAME?=?ATA.USERNAME)??
  13. SELECT?AU.USERNAME,?AU.USER_ID??
  14. ??FROM?ALL_USERS?AU??
  15. ?INNER?JOIN?PARAMS?PA??
  16. ????ON?1?=?1??
  17. ?INNER?JOIN?DBA_USERS?DU??
  18. ????ON?AU.USERNAME?=?DU.USERNAME??
  19. ?WHERE?((PA.SDATE?IS?NULL?AND?PA.USER_ID?IS?NOT?NULL?AND??
  20. ???????AU.USER_ID?=?PA.USER_ID)?OR??
  21. ?????????
  22. ???????(PA.SDATE?IS?NULL?AND?PA.USER_ID?IS?NULL?AND??
  23. ???????AU.USERNAME?NOT?IN?(SELECT?USERNAME?FROM?USERNAMEDATA))?OR??
  24. ???????(PA.SDATE?IS?NOT?NULL?AND??
  25. ???????AU.USERNAME?IN??
  26. ???????(SELECT?AU.USERNAME??
  27. ????????????FROM?ALL_USERS?AU??
  28. ???????????INNER?JOIN?DBA_USERS?PA??
  29. ??????????????ON?AU.USERNAME?=?PA.USERNAME??
  30. ???????????INNER?JOIN?ALL_TABLES?ATA??
  31. ??????????????ON?PA.USERNAME?=?ATA.OWNER??
  32. ???????????WHERE?TO_CHAR(ATA.LAST_ANALYZED,?'YYYY-MM-DD')?=?PA.SDATE)?AND??
  33. ???????AU.USER_ID?=?PA.USER_ID))??
  34. ???AND?DU.PROFILE?LIKE?'D%'??
  35. ???AND?AU.USERNAME?LIKE?PA.SNAME??


總結：

在FILTER中，NOT EXISTS后的SQL語句多次執行，本來數據量就很大，每次都要執行一遍，結果可想而知。但是使用HINT?MATERIALIZE和WITH AS?結合使用，把內聯視圖實體化，執行過程中會創建基于視圖的臨時表。這樣就不會每次NOT EXISTS都去執行一遍大數據表的掃描，只需要掃描一次即可。

但是是不是可以在WITHAS中的每個語句都實體化那？如果WITH AS中的語句只被調用一次的話，最好還是不要使用HINT?MATERIALIZE，因為使用HINT?MATERIALIZE第一次查詢會創建基于視圖結果的臨時表，這也耗費一些時間。多次使用的話可以使用HINT?MATERIALIZE。


本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/539731.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/539731.shtml
英文地址，請注明出處：http://en.pswp.cn/news/539731.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

面試題333

面試題333

2019獨角獸企業重金招聘Python工程師標準>>> 面試題333 博客分類： java 1、spring的緩存,mybatis緩存2、介紹下dubbo。A服務調用B服務，B服務又調用C服務,這種情況怎么辦3、JVM監控工具有哪些，區別又是什么（如能追上各個…
閱讀更多...
mysql vfp_用 VFP 連接 MYSQL 數據庫

mysql vfp_用 VFP 連接 MYSQL 數據庫

今天試了一下用 Visual FoxPro 連接 MySQL 數據庫。首先在自己機子上架設 MySQL 數據庫，就不多說了，我是直接用 XAMPP 架設的服務器。然后在 VFP 里輸入命令：sqlhandle SQLSTRINGCONNECT("driver{MySQL ODBC 5.1 Driver};server127.0.0…
閱讀更多...
oracle中with的用法及用處

oracle中with的用法及用處

原文出處：http://blog.csdn.net/chenjinlin1/article/details/6572401 ---------------------------------------------------------------- WITH 用于一個語句中某些中間結果放在臨時表空間的SQL語句 如 WITH channel_summary AS ( SELECT channels.channel_de…
閱讀更多...
xpath選擇當前結點的子節點

xpath選擇當前結點的子節點

2019獨角獸企業重金招聘Python工程師標準>>> xpath選擇當前結點的子節點 博客分類： 搜索引擎，爬蟲 在通過selenium使用xpath選擇節點的時候，可能會遇到這么一種情況：在指定的當前節點下搜索滿足要求的節點。 node dri…
閱讀更多...
mysql中主從復制配置文件_MySQL主從復制 配置文件實例

mysql中主從復制配置文件_MySQL主從復制 配置文件實例

1、主服務器配置文件# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[mysqld]# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70%…
閱讀更多...
SQL中，where 與 having 的性能比較

SQL中，where 與 having 的性能比較

原文：http://blog.csdn.net/showshore/article/details/7263115 --------------------------------------------------------- 在做項目的過程中，使用sql語句時，很多時候會用到where或having。 看到國外一個論壇上有人提到兩者性能比較的這個…
閱讀更多...
Spark 獨立部署模式

Spark 獨立部署模式

2019獨角獸企業重金招聘Python工程師標準>>> Spark 獨立部署模式 博客分類： spark 除了在 Mesos 或 YARN 集群上運行之外, Spark 還提供一個簡單的獨立部署的模塊。你通過手動開始master和workers 來啟動一個獨立的集群。你也可以利用我們提供的腳本 .…
閱讀更多...
mysql數據庫的鏈接地址_常用數據庫連接URL地址大全

mysql數據庫的鏈接地址_常用數據庫連接URL地址大全

1、Oracle8/8i/9i數據庫(thin模式) Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); String url="jdbc:oracle:thin:@localhost:1521:orcl"; //orcl為數據庫的SID String user="test"; String password="test"; Con…
閱讀更多...
數據庫中where與having區別~~~

數據庫中where與having區別~~~

1、where和having的執行級別不同 在查詢過程中聚合語句(sum,min,max,avg,count)要比having子句優先執行.而where子句在查詢過程中執行優先級別優先于聚合語句(sum,min,max,avg,count)。 having就是來彌補where在分組數據判斷時的不足。因為where執行優先級別要快于聚合語句。…
閱讀更多...
spring boot 1.5.4 定時任務和異步調用（十）

spring boot 1.5.4 定時任務和異步調用（十）

1 Spring Boot定時任務和異步調用 我們在編寫Spring Boot應用中經常會遇到這樣的場景，比如：我需要定時地發送一些短信、郵件之類的操作，也可能會定時地檢查和監控一些標志、參數等。 spring boot定時任務spring-boot-jsp項目源碼&#…
閱讀更多...
ORA-04063: view DAILY.TMP_TBX_100_0_S4 有錯誤

ORA-04063: view DAILY.TMP_TBX_100_0_S4 有錯誤

執行： CREATE TABLE TMP_TBX_100_0_S3 AS SELECT t.* FROM (select t1.*,NULL AS sdate, NULL AS report_id from TMP_TBX_100_0_S4_1 t1 union all select t2.* from TMP_TBX_100_0_S4_2 t2) t 報錯： ORA-00955: name is already used by an exis…
閱讀更多...
MySQL左連接還有過濾條件_MySQL左連接問題，右表做篩選，左表列依然在？

MySQL左連接還有過濾條件_MySQL左連接問題，右表做篩選，左表列依然在？

問 題原料兩張表，一張user表，一張user_log表(這個例子舉的不好)CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,name varchar(20) DEFAULT NULL,PRIMARY KEY (id)) ENGINEInnoDB DEFAULT CHARSETutf8;CREATE TABLE user_log (id int(10) NOT NU…
閱讀更多...
2017工作總結

2017工作總結

靜兒總結自己的職業生涯分為三個階段。第一個階段為期十年，是純技術階段，是人生的積累期。第二個階段是管理階段，是綜合能力整合期。第三個階段是突破階段，打造自己獨特的核心競爭力。 第一階段 剛畢業的同學可能會覺得技術高大上…
閱讀更多...
批量刪除table或view

批量刪除table或view

一個個刪太費時了。一個簡單可行的方法。 思路： 找出要刪除的表名/視圖名，然后拼接刪除sql，執行。 批量刪除表： 1、找出要刪除的表名select table_name from user_tables where table_name like S1MSGLATENCY_2016032%;2、復制這…
閱讀更多...
編譯安裝mysql5.5.39_編譯安裝MySQL5.5

編譯安裝mysql5.5.39_編譯安裝MySQL5.5

防偽碼：沒有相當程度的孤獨是不可能有內心的平和。1、測試環境主機名IP系統MySQL版本MySQL-00192.168.10.23CentOS release 6.9 (Final)5.5.562、部署2.1 卸載之前版本rpm -qa|grep mysqlrpm -e --nodeps mysql-libs-5.1.73-8.el6_8.x86_642.2 安裝依賴包和cmakeyum…
閱讀更多...
你真的了解iOS怎么取屬性的嗎？

你真的了解iOS怎么取屬性的嗎？

你真的了解iOS怎么取屬性的嗎？ 本文來自CocoaChina粉絲FlyOceanFish投稿如果iOS中談到取屬性，相信大家都會夸夸其談，不就是get方法嗎？或者大談kvc取屬性的機制。不得不說這些也是對的。這時大家可能就疑惑了，那你還要說…
閱讀更多...
Oracle表的并行度

Oracle表的并行度

查看dba_tables數據字典時，可以發現有“DEGREE”字段，這個字段表示的就是數據表的并行度。這個參數的設置，關系著數據庫的I/O，以及sql的執行效率。并行度的優點就是能夠最大限度的利用機器的多個cpu資源，是多個cpu同時…
閱讀更多...
mysql 臨時表 限制_Mysql臨時表

mysql 臨時表 限制_Mysql臨時表

當你創建臨時表的時候，你可以使用temporary關鍵字。如：create temporary table tmp_table(name varchar(10) not null,passwd char(6) not null)‘或CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp ENGINE MEMORY SELECT …from … where IDcurre…
閱讀更多...
10年老兵給程序員的10條建議！

10年老兵給程序員的10條建議！

2019獨角獸企業重金招聘Python工程師標準>>> 程序員雖然薪資待遇好，但是也得付出努力，技術好才行。特別是對于剛剛進入編程工作的新手程序員和正在學習編程的同學來說，一寫代碼就報錯，出bug。作為一個工作了10年的老兵…
閱讀更多...
視圖和表的區別和聯系

視圖和表的區別和聯系

區別： 1、視圖是已經編譯好的sql語句。而表不是 2、視圖沒有實際的物理記錄。而表有。 3、表是內容，視圖是窗口 4、表只用物理空間而視圖不占用物理空間，視圖只是邏輯概念的存在，表可以及時四對它進行修改&#xf…
閱讀更多...
最新文章

Copyright @ 2022~2023