通過新增條件優化SQL

space6212發表於2019-03-15
優化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更多的資訊，讓它掃描更少的資料和分割槽，少做無用功。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-1047528/，如需轉載，請註明出處，否則將追究法律責任。

相關文章