聯絡生活來簡化sql語句
目前生產環境中有一條sql語句的CPU消耗很高。執行時間比較長。從awr中抓到的sql語句如下：
SELECT run_request.run_mode, cycle_groups.flow_id,
cycle_groups.request_id, cycle_groups.dynamic_attributes,
cycle_groups.sys_creation_date, cycle_groups.sys_update_date,
cycle_control.cycle_code
FROM (SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --這個地方又關聯了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route) cycle_groups,
cycle_control,
run_request
WHERE cycle_groups.status = 'FIN'
AND cycle_groups.request_id = run_request.request_id
AND cycle_control.cycle_seq_no = cycle_groups.cycle_seq_no
AND cycle_control.cycle_code
|| '_'
|| cycle_groups.flow_id
|| '_'
|| run_request.run_mode IN (
SELECT cycle_control.cycle_code
|| '_'
|| run_request.flow_id
|| '_'
|| run_request.run_mode
FROM run_request, cycle_control
WHERE ( run_request.population_type = 'CYC'
OR run_request.population_type = 'CCD'
)
AND run_request.population_id =cycle_control.cycle_seq_no)
ORDER BY cycle_groups.request_id
從第一印象來看,sql語句有些臃腫，過濾條件也比較奇怪。
首先是標黃的部分，關聯的連線條件都是索引列.
執行計劃如下，可以看到還是消耗很大的。
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 273K(100)| | | |
| 1 | SORT ORDER BY | | 5714K| 1111M| 1174M| 273K (1)| 00:54:46 | | |
|* 2 | HASH JOIN | | 5714K| 1111M| | 19993 (1)| 00:04:00 | | |
| 3 | VIEW | VW_NSO_1 | 423 | 13959 | | 28 (11)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 423 | 12690 | | 28 (11)| 00:00:01 | | |
|* 5 | HASH JOIN | | 6992 | 204K| | 26 (4)| 00:00:01 | | |
| 6 | VIEW | index$_join$_009 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 7 | HASH JOIN | | | | | | | | |
| 8 | INDEX FAST FULL SCAN | CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 9 | INDEX FAST FULL SCAN | CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | RUN_REQUEST | 7641 | 164K| | 23 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 1350K| 220M| | 19944 (1)| 00:04:00 | | |
| 12 | TABLE ACCESS FULL | RUN_REQUEST | 7735 | 92820 | | 23 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 1350K| 204M| | 19916 (1)| 00:03:59 | | |
| 14 | JOIN FILTER CREATE | :BF0000 | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
|* 15 | HASH JOIN | | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
| 16 | VIEW | index$_join$_006 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 17 | HASH JOIN | | | | | | | | |
| 18 | INDEX FAST FULL SCAN| CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 19 | INDEX FAST FULL SCAN| CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS FULL | CYCLE_GROUPS | 13072 | 1570K| | 67 (0)| 00:00:01 | | |
| 21 | VIEW | | 1426K| 38M| | 19840 (1)| 00:03:59 | | |
| 22 | HASH GROUP BY | | 1426K| 69M| 87M| 19840 (1)| 00:03:59 | | |
| 23 | JOIN FILTER USE | :BF0000 | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 24 | NESTED LOOPS | | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 25 | PARTITION RANGE ALL | | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
| 26 | INDEX FULL SCAN | GROUP_STATUS_PK | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
|* 27 | INDEX UNIQUE SCAN | CYCLE_GROUPS_PK | 1 | 23 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
這個調優可以從一個生活的例子來簡單說明。
我平時早餐吃麵包，就舉吃麵包的例子吧。
比如我在所住的校區門口有家麵包店，每次我買麵包都會得到一個收據，上面有所買的麵包明細和卡號，還有一個流水號。
在公司樓下也有一個麵包店，都可以用同一張卡來消費，也會得到一個流水號。
可以關聯起來，表cycle_groups就類似一個客戶清單，清單中的每一項就代表一個客戶卡號。而表group_status就代表消費的明細，比如流水號，所賣的麵包和所消費的金額時間等。
cycle_group裡只有卡號等資訊，在cycle_status中是消費的資料。
這個時候我們想得到某張卡號 消費的情況，比如卡號，年月日，消費的金額，如果流水號相同只輸出一個流水號。
這個操作就類似下面的形式。
SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
而這個時候我們得到了一個卡號，消費年月日，消費流水號的清單，流水號相同的情況下，可以只得到50萬條記錄。
這個時候我們想給予目前的統計結果，得到卡號，消費的門店，消費金額，消費流水號時，這個時候就相當於根據流水號把表cycle_status裡面的資料又重新查取了一遍。
這個時候你想想，還不如直接關聯卡號和消費記錄來查取一次呢。因為，在子查詢裡過濾了流水號，只顯示一條不重複的流水號，但是外部查詢中又需要得到更為詳細的資訊，導致過濾了一次資料，然後又重新還原了一遍。
SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --這個地方又關聯了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route
所以可能自己想了很多的思路，但是最後還是又把問題迴歸到原點。
SELECT run_request.run_mode, cycle_groups.flow_id,
cycle_groups.request_id, cycle_groups.dynamic_attributes,
cycle_groups.sys_creation_date, cycle_groups.sys_update_date,
cycle_control.cycle_code
FROM (SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --這個地方又關聯了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route) cycle_groups,
cycle_control,
run_request
WHERE cycle_groups.status = 'FIN'
AND cycle_groups.request_id = run_request.request_id
AND cycle_control.cycle_seq_no = cycle_groups.cycle_seq_no
AND cycle_control.cycle_code
|| '_'
|| cycle_groups.flow_id
|| '_'
|| run_request.run_mode IN (
SELECT cycle_control.cycle_code
|| '_'
|| run_request.flow_id
|| '_'
|| run_request.run_mode
FROM run_request, cycle_control
WHERE ( run_request.population_type = 'CYC'
OR run_request.population_type = 'CCD'
)
AND run_request.population_id =cycle_control.cycle_seq_no)
ORDER BY cycle_groups.request_id
從第一印象來看,sql語句有些臃腫，過濾條件也比較奇怪。
首先是標黃的部分，關聯的連線條件都是索引列.
執行計劃如下，可以看到還是消耗很大的。
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 273K(100)| | | |
| 1 | SORT ORDER BY | | 5714K| 1111M| 1174M| 273K (1)| 00:54:46 | | |
|* 2 | HASH JOIN | | 5714K| 1111M| | 19993 (1)| 00:04:00 | | |
| 3 | VIEW | VW_NSO_1 | 423 | 13959 | | 28 (11)| 00:00:01 | | |
| 4 | HASH UNIQUE | | 423 | 12690 | | 28 (11)| 00:00:01 | | |
|* 5 | HASH JOIN | | 6992 | 204K| | 26 (4)| 00:00:01 | | |
| 6 | VIEW | index$_join$_009 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 7 | HASH JOIN | | | | | | | | |
| 8 | INDEX FAST FULL SCAN | CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 9 | INDEX FAST FULL SCAN | CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | RUN_REQUEST | 7641 | 164K| | 23 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 1350K| 220M| | 19944 (1)| 00:04:00 | | |
| 12 | TABLE ACCESS FULL | RUN_REQUEST | 7735 | 92820 | | 23 (0)| 00:00:01 | | |
|* 13 | HASH JOIN | | 1350K| 204M| | 19916 (1)| 00:03:59 | | |
| 14 | JOIN FILTER CREATE | :BF0000 | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
|* 15 | HASH JOIN | | 12436 | 1590K| | 70 (2)| 00:00:01 | | |
| 16 | VIEW | index$_join$_006 | 2157 | 17256 | | 3 (34)| 00:00:01 | | |
|* 17 | HASH JOIN | | | | | | | | |
| 18 | INDEX FAST FULL SCAN| CYCLE_CONTROL_PK | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
| 19 | INDEX FAST FULL SCAN| CYCLE_CONTROL_1IX | 2157 | 17256 | | 1 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS FULL | CYCLE_GROUPS | 13072 | 1570K| | 67 (0)| 00:00:01 | | |
| 21 | VIEW | | 1426K| 38M| | 19840 (1)| 00:03:59 | | |
| 22 | HASH GROUP BY | | 1426K| 69M| 87M| 19840 (1)| 00:03:59 | | |
| 23 | JOIN FILTER USE | :BF0000 | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 24 | NESTED LOOPS | | 1426K| 69M| | 1746 (1)| 00:00:21 | | |
| 25 | PARTITION RANGE ALL | | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
| 26 | INDEX FULL SCAN | GROUP_STATUS_PK | 1426K| 38M| | 1740 (1)| 00:00:21 | 1 | 19 |
|* 27 | INDEX UNIQUE SCAN | CYCLE_GROUPS_PK | 1 | 23 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
這個調優可以從一個生活的例子來簡單說明。
我平時早餐吃麵包，就舉吃麵包的例子吧。
比如我在所住的校區門口有家麵包店，每次我買麵包都會得到一個收據，上面有所買的麵包明細和卡號，還有一個流水號。
在公司樓下也有一個麵包店，都可以用同一張卡來消費，也會得到一個流水號。
可以關聯起來，表cycle_groups就類似一個客戶清單，清單中的每一項就代表一個客戶卡號。而表group_status就代表消費的明細，比如流水號，所賣的麵包和所消費的金額時間等。
cycle_group裡只有卡號等資訊，在cycle_status中是消費的資料。
這個時候我們想得到某張卡號 消費的情況，比如卡號，年月日，消費的金額，如果流水號相同只輸出一個流水號。
這個操作就類似下面的形式。
SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
而這個時候我們得到了一個卡號，消費年月日，消費流水號的清單，流水號相同的情況下，可以只得到50萬條記錄。
這個時候我們想給予目前的統計結果，得到卡號，消費的門店，消費金額，消費流水號時，這個時候就相當於根據流水號把表cycle_status裡面的資料又重新查取了一遍。
這個時候你想想，還不如直接關聯卡號和消費記錄來查取一次呢。因為，在子查詢裡過濾了流水號，只顯示一條不重複的流水號，但是外部查詢中又需要得到更為詳細的資訊，導致過濾了一次資料，然後又重新還原了一遍。
SELECT cycle_groups.*, a.request_id
FROM (SELECT cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id
FROM cycle_groups, --資料量在1萬左右
group_status --資料量在百萬
WHERE cycle_groups.GROUP_ID =group_status.GROUP_ID --這些都是索引列
AND cycle_groups.flow_id = group_status.flow_id --這些都是索引列
AND cycle_groups.cycle_seq_no =group_status.cycle_seq_no
AND cycle_groups.route = group_status.route
GROUP BY cycle_groups.GROUP_ID,
cycle_groups.flow_id,
cycle_groups.cycle_seq_no,
cycle_groups.route,
group_status.request_id) a,
cycle_groups
WHERE cycle_groups.GROUP_ID = a.GROUP_ID --這個地方又關聯了一次
AND cycle_groups.flow_id = a.flow_id
AND cycle_groups.cycle_seq_no = a.cycle_seq_no
AND cycle_groups.route = a.route
所以可能自己想了很多的思路，但是最後還是又把問題迴歸到原點。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1346973/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql語句的簡化SQL
- 通過圖表簡化sql語句的表關聯SQL
- 通過java來格式化sql語句JavaSQL
- 透過java來格式化sql語句JavaSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句優化SQL優化
- sql的聯結語句詳解SQL
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 最佳化sql語句SQL
- 求助：SQL語句優化SQL優化
- mysql 常用sql語句 簡介MySql
- SQL Server優化之SQL語句優化SQLServer優化
- 兩表聯查修改的sql語句SQL
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- MySQL之SQL語句優化MySql優化
- SQL語句優化（轉載）SQL優化
- 常用SQL語句優化技巧SQL優化
- Oracle之sql語句優化OracleSQL優化
- SQL 語句的優化方法SQL優化
- sql語句的最佳化SQL
- 使用hint來調優sql語句SQL
- 簡單的SQL語句學習SQL
- SQL語句的分類簡介SQL
- 簡單SQL語句小結(轉)SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- sql最佳化：使用sql profile最佳化sql語句SQL
- 一條簡單SQL語句的構成及語句解析SQL
- SQL聯合語句的視覺解釋SQL視覺
- 優化 SQL 語句的步驟優化SQL
- 一個SQL語句的優化SQL優化
- Oracle SQL語句優化之UNIONOracleSQL優化
- SQL語句操作符優化SQL優化
- 關於sql語句的優化SQL優化
- SQL語句優化技術分析SQL優化
- SQL語句優化方法30例SQL優化
- SQL Profiles與語句最佳化SQL