2025/6/231Oracle數據庫應用

與開發案例教程第十一章SQL語句優化2025/6/23Oracle數據庫應用與開發案例教程2第八章PL/SQL編程11.1SQL語句優化概述11.2SQL優化的一般原則11.3表的連接方法11.4有效使用索引2025/6/23Oracle數據庫應用與開發案例教程311.1SQL語句優化概述進行SQL語句優化的原因編寫規范且高效的SQL語句顯然是數據庫性能優化中非常重要的一項工作，因為不同的SQL實現方式之間的效率差異可能會非常大，尤其是在大數據量的數據庫環境下，在一個千萬級別數據量數據庫的幾個關聯的大表之間執行一條SELECT語句可能會耗費相當長的時間，直接降低了數據庫系統的性能，并導致整個軟件系統運行速度下降，效率低下。為了提高數據庫的整體性能，需要如何分辨那些導致性能低下的SQL語句，并按照相應的原則調整優化。2025/6/23Oracle數據庫應用與開發案例教程411.1SQL語句優化概述SQL語句執行的一般順序1.解析①在共享池中查找相同的SQL語句。②語法分析③語義分析④對象加鎖⑤權限檢查⑥確定執行計劃⑦保存執行計劃2.執行3.提取2025/6/23Oracle數據庫應用與開發案例教程511.2SQL優化的一般原則SELECT語句中避免使用“*”在寫SQL語句時，雖然使用“*”表示表的所有列可以降低編寫SQL語句的難度，但是建議把表的實際列名都寫在SQL語句中來提高其執行效率。【例題11-1】在SELECT語句中分別使用“*”和使用具體的列名來查詢并顯示ZICHANMINGXI所有記錄。2025/6/23Oracle數據庫應用與開發案例教程611.2SQL優化的一般原則編寫SQL時使用相同的編碼風格在編寫SQL語句時，建議使用相同SQL語句的編碼風格，使多條完全相同的SQL語句在系統中只被解析一次。【例題11-2】使用了不同的編碼風格對zichanzhuangtai表執行查詢操作。2025/6/23Oracle數據庫應用與開發案例教程711.2SQL優化的一般原則使用WHERE子句代替HAVING子句在有分組函數的SQL語句中，應該通過WHERE子句限制記錄的數目而避免使用HAVING子句，即通過先用WHERE子句過濾記錄，再使用HAVING子句過濾分組的方式來減少這方面的開銷，提高SQL性能。【例題11-3】對ZICHANMINGXI表進行操作，求出每個部門資產原值的平均值，并將BMID列的值為‘zcc’的記錄信息過濾掉。2025/6/23Oracle數據庫應用與開發案例教程811.2SQL優化的一般原則使用TRUNCATE代替DELETE在執行刪除操作時，如果確定要刪除表中的全部記錄，盡量使用TRUNCATE子句。【例題11-4】使用SELECT語句創建一兩個完全相同的表ZICHANMINGXI1和ZICHANMINGXI2，然后分別使用DELETE語句和TRUNCATE語句執行刪除操作。2025/6/23Oracle數據庫應用與開發案例教程911.2SQL優化的一般原則在確保完整的情況下多COMMIT建議在保證系統正常業務邏輯的情況下，在執行DML操作時，應及時使用COMMIT提交事務，這樣可以及時結束事務，釋放事務所占用的資源。COMMIT所釋放的資源有：1.回滾段上用于恢復數據的信息.2.DML語句造成的鎖3.重做日志緩存區的空間4.ORACLE為維護事務的內部開銷【例題11-5】當向ZICHANLEIXING表中插入一行記錄后，在確保數據完整性的情況下，及時使用COMMIT提交事務。2025/6/23Oracle數據庫應用與開發案例教程1011.2SQL優化的一般原則使用EXISTS替代ININ操作符用于檢查一個值是否包含在列表中，而EXISTS只是檢查行的存在性。在子查詢中，EXISTS提供的性能通常比IN提供的性能要好，因此建議用EXISTS來替代IN。【例題11-6】分別用IN和EXISTS實現下列查詢操作：查詢資產狀態為“bf01”的資產所在的部門信息。2025/6/23Oracle數據庫應用與開發案例教程1111.2SQL優化的一般原則用EXITST替代DISTINCT在連接查詢的SELECT語句中，當需要使用DISTINCT關鍵字來過濾重復的查詢結果行時，DISTINCT通常需要先對查詢結果進行排序，然后才限定在查詢結果中顯示那些不重復的數據列，這一過程也會增加額外的系統開銷。因此，在SQL語句中應該盡量用EXITST替代DISTINCT。【例題11-7】分別使用EXITST和DISTINCT對YONGHU表和BUMEN表進行操作。2025/6/23Oracle數據庫應用與開發案例教程1211.2SQL優化的一般原則使用表連接而不是多個查詢當需要從多個表中查詢數據時，建議使用多表連接的SQL語句執行一次查詢操作得到所需的數據，盡量減少對子查詢或子查詢嵌套的使用，【例題11-8】分別在SELECT語句中使用嵌套子查詢和表連接來查詢部門名稱是組織部的用戶名稱和部門id等信息。2025/6/23Oracle數據庫應用與開發案例教程1311.2SQL優化的一般原則使用“<=”替代“<”當需要在PL/SQL程序塊的循環結構中使用操作符“<=”和“<”，“>=”和“>”時，或要查詢的數據量很大時，這兩類操作符的執行效率差別是比較明顯的。【例題11-9】分別使用操作符“>=”和“>”來查詢ZICHANMINGXI表中資產id大于等于100的記錄。2025/6/23Oracle數據庫應用與開發案例教程1411.2SQL優化的一般原則盡量使用表的別名（ALIAS），并在列前標注來源于哪個表在SQL語句中連接多個表時，Oracle建議最好使用表的別名，并把別名作為每個列的前綴，這樣可以減少Oracle解析表中SQL語句的列名所用的時間。【例題11-10】分別使用表的別名和不使用表的別名來查詢并顯示資產管理數據庫中所有資產的資產id，資產名稱，部門名稱，用戶名稱，狀態名稱和分類名稱等信息。2025/6/23Oracle數據庫應用與開發案例教程1511.3表的連接方法FROM子句中將數據量最小的表作為驅動表在進行多表查詢操作時，建議選擇記錄行數最少的表作為驅動表，來提高SQL的執行效率。【例題11-11】改變FROM子句中表的順序對ZICHANMINGXI表和BUMEN表進行自然連接操作，其中ZICHANMINGXI表中的記錄數遠遠大于YONGHU表中的記錄數。2025/6/23Oracle數據庫應用與開發案例教程1611.3表的連接方法WHERE子句的連接順序當SQL語句中的WHERE子句中有多個查詢條件時，Oracle是按照從右到左（從下到上）來解析的，因此最好將能過濾掉表中記錄數量最多的條件寫在WHERE子句的最后，這樣可以提高SQL性能。【例題11-12】改變WHERE子句連接順序，對ZICHANMINGXI表進行操作，顯示資產狀態為“zc01”，資產id大于等于100的所有資產的資產id，資產名稱，部門名稱和狀態名稱等信息。2025/6/23Oracle數據庫應用與開發案例教程1711.4有效使用索引1.創建索引的基本原則1）對經常以查詢關鍵字為基礎的表建立索引。2）表中的大部分查詢都包含相對簡單的WHERE子句。3）索引應建立在WHERE子句中經常引用的列上。4）為了提高多表連接的性能，應該在連接列上建立索引。5）如果經常需要基于某列或某幾個列進行排序操作，可以考慮在這些列上建立索引。6）不宜將經常修改的列作為索引列。7）不宜在小表上建立索引。2025/6/23Oracle數據庫應用與開發案例教程1811.4有效使用索引2.避免對唯一索引使用NULL值3.使用數據字典視圖監視索引是否正被使用【例題11-13】使用ALTERINDEX語句，指定MONITORINGUSAGE子句，監視已創建的ZICHANZHUANGTAI_PK索引。并通過V$O