通過新增條件優化SQL
今天幫助同事優化了一個SQL，效果顯著。優化方式很簡單，沒有修改執行計劃，沒有新增hint，只是根據實際情況增加一些條件。
[@more@]原來的SQL是這樣的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where
4 a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 387255052
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 288 | 23328 | | 49M (2)|193:55:41 | | |
|* 1 | HASH JOIN | | 288 | 23328 | 5408K| 49M (2)|193:55:41 | | |
| 2 | TABLE ACCESS FULL | tb2 | 115K| 4048K| | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ALL| | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
------------------------------------------------------------------------------------------------------------------------
tb1這個表非常大，從執行計劃可以看出來，Oracle要做一個全表掃描，掃描1700個分割槽，700多GB的資料，這個效率是非常低的。
但是，看一下這個SQL，是有時間限定的，真的有必要進行全表掃描嗎？
查一下資料分佈：
sys@dwrac1> select min(call_date),max(call_date) from user1.tb2;
MIN(CALL_DATE) MAX(CALL_DATE)
-------------------- --------------------
2010-02-10 2011-02-12
可以看到，tb2這個表只包含了一年的資料，根據邏輯，這個SQL對tb1最多掃描13個月資料就足夠了，但問題是：
Oralce沒有那麼智慧，因為沒有人告訴它應該掃描那些區間的資料，所以只能全表掃描。即使收集柱狀圖，Oralce也會掃描所有分割槽。
因此，要解決這個問題，方法很簡單，就是根據時間情況，新增一些條件，讓Oracle掃描更少的分割槽，如：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 1 | HASH JOIN | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 5758 | 202K| 120 (8)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
---------------------------------------------------------------------------------------------------------------------
在這個例子中，call_date的最小時間是2010-02-10，因此，這一資訊告訴Oracle，Oracle在掃描的時候就只掃描比原來少得多的資料和分割槽，效能也就上去了。
不過這裡還有一個問題，就是一次性查詢這麼多資料的話，做hash join時可能會消耗大量的臨時表空間，很容易導致臨時表空間不足失敗，甚至影響其他的操作，因此需要把它們分段查詢。
分割槽查詢的話不能隨便分，要按照call_date來劃分，比如一個月作為一個單位，執行12次就可以了。
如2月份的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and b.call_date 5 and a.login_date<='2010-04-01'
6 and a.login_date >= b.call_date
7 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
8 and a.game_name = b.game_id
9 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 1 | HASH JOIN | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 307 | 11052 | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
|* 4 | TABLE ACCESS FULL | tb1 | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
---------------------------------------------------------------------------------------------------------------------
其中：a.login_date<='2010-04-01'根據實際情況改，只要比to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')範圍略大就可以。
這個優化的原理很簡單，就是告訴Oracle更多的資訊，讓它掃描更少的資料和分割槽，少做無用功。
[@more@]原來的SQL是這樣的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where
4 a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 387255052
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 288 | 23328 | | 49M (2)|193:55:41 | | |
|* 1 | HASH JOIN | | 288 | 23328 | 5408K| 49M (2)|193:55:41 | | |
| 2 | TABLE ACCESS FULL | tb2 | 115K| 4048K| | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ALL| | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 17G| 732G| | 9007K (4)| 35:01:49 | 1 | 1669 |
------------------------------------------------------------------------------------------------------------------------
tb1這個表非常大，從執行計劃可以看出來，Oracle要做一個全表掃描，掃描1700個分割槽，700多GB的資料，這個效率是非常低的。
但是，看一下這個SQL，是有時間限定的，真的有必要進行全表掃描嗎？
查一下資料分佈：
sys@dwrac1> select min(call_date),max(call_date) from user1.tb2;
MIN(CALL_DATE) MAX(CALL_DATE)
-------------------- --------------------
2010-02-10 2011-02-12
可以看到，tb2這個表只包含了一年的資料，根據邏輯，這個SQL對tb1最多掃描13個月資料就足夠了，但問題是：
Oralce沒有那麼智慧，因為沒有人告訴它應該掃描那些區間的資料，所以只能全表掃描。即使收集柱狀圖，Oralce也會掃描所有分割槽。
因此，要解決這個問題，方法很簡單，就是根據時間情況，新增一些條件，讓Oracle掃描更少的分割槽，如：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and a.login_date >= b.call_date
5 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
6 and a.game_name = b.game_id
7 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 1 | HASH JOIN | | 1 | 81 | 3908K (5)| 15:11:55 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 5758 | 202K| 120 (8)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
| 4 | TABLE ACCESS FULL | tb1 | 874M| 36G| 3901K (5)| 15:10:18 | 1255 | 1669 |
---------------------------------------------------------------------------------------------------------------------
在這個例子中，call_date的最小時間是2010-02-10，因此，這一資訊告訴Oracle，Oracle在掃描的時候就只掃描比原來少得多的資料和分割槽，效能也就上去了。
不過這裡還有一個問題，就是一次性查詢這麼多資料的話，做hash join時可能會消耗大量的臨時表空間，很容易導致臨時表空間不足失敗，甚至影響其他的操作，因此需要把它們分段查詢。
分割槽查詢的話不能隨便分，要按照call_date來劃分，比如一個月作為一個單位，執行12次就可以了。
如2月份的：
sys@dwrac1> select b.*, a.login_date
2 from user1.tb1 a, user1.tb2 b
3 where b.call_date>='2010-02-10'
4 and b.call_date 5 and a.login_date<='2010-04-01'
6 and a.login_date >= b.call_date
7 and a.login_date < to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')
8 and a.game_name = b.game_id
9 and a.popt_id = b.popt_id;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1784245566
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 1 | HASH JOIN | | 1 | 81 | 320K (5)| 01:14:54 | | |
|* 2 | TABLE ACCESS FULL | tb2 | 307 | 11052 | 114 (3)| 00:00:02 | | |
| 3 | PARTITION RANGE ITERATOR| | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
|* 4 | TABLE ACCESS FULL | tb1 | 43M| 1875M| 320K (5)| 01:14:48 | 1255 | 1305 |
---------------------------------------------------------------------------------------------------------------------
其中：a.login_date<='2010-04-01'根據實際情況改，只要比to_char(to_date(b.call_date,'yyyy-mm-dd')+30,'yyyy-mm-dd')範圍略大就可以。
這個優化的原理很簡單，就是告訴Oracle更多的資訊，讓它掃描更少的資料和分割槽，少做無用功。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1047528/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PL/SQL 條件SQL
- Java 條件表示式的優化Java優化
- 【SQL】SQL中if條件的使用SQL
- MySQL 針對 like 條件的優化MySql優化
- 記錄一篇關於條件查詢語句的用法:1.通過QueryWrapper進行條件構造2.通過Example進行條件構造APP
- [20181114]一條sql語句的優化.txtSQL優化
- 詳解SQL效能優化十條經驗SQL優化
- Oracle SQL效能優化的40條軍規OracleSQL優化
- 程式碼優化-多型代替IF條件判斷優化多型
- 優化 JS 條件語句的 5 個技巧優化JS
- PL/SQL 條件控制語句SQL
- 智雲通CRM：如何通過平等交換條件，實現雙贏？
- Vue根據條件新增click事件Vue事件
- 蝦扯蛋之條件判斷的極致優化優化
- JavaScript（ES6）邏輯判斷條件優化JavaScript優化
- 條件過濾檢索
- 動態SQL-條件分頁SQL
- sql 使用變數帶入in條件SQL變數
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- 外連線有 OR 關聯條件只能走 NL優化優化
- 通過Scope Hoisting優化Webpack輸出優化Web
- SQL-基礎語法 - 條件分支SQL
- 通過程式找sqlSQL
- MDN新增“HTTP有條件請求”標頭HTTP
- 【GreatSQL最佳化器-05】條件過濾condition_fanout_filterSQLFilter
- sql優化之邏輯優化SQL優化
- 資料庫優化 - SQL優化資料庫優化SQL
- 對一條基於分割槽的簡單SQL的優化SQL優化
- 帶你詳細解讀十條關於SQL效能優化！SQL優化
- SQL SERVER優化SQLServer優化
- SQL優化指南SQL優化
- jQuery DataTables新增自定義多個搜尋條件jQuery
- Laravel 8.55 新新增了條件驗證規則Laravel
- 提高mysql千萬級大資料SQL查詢優化30條經驗（Mysql索引優化注意）MySql大資料優化索引
- odoo 為可編輯列表檢視欄位搜尋新增查詢過濾條件Odoo
- 「分散式技術專題」SQL最佳化的前置條件和最佳化技巧分享分散式SQL
- 最佳化數倉業務檢視：過濾條件傳遞
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL