一條sql語句的優化
今天跟蹤程式，無意中發現一條不良的SQL語句
今天跟蹤程式，無意中發現一條不良的SQL語句
分析如下：
SELECT fee_stat_name, NVL (t1.b, 0)
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0', balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') <> '2'
UNION ALL
SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST", '1', waste_dtime,
"OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
ORDER BY t2.print_order
執行計劃如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=54 Bytes=2268)
1 0 SORT (ORDER BY) (Cost=44 Card=54 Bytes=2268)
2 1 HASH JOIN (OUTER) (Cost=40 Card=54 Bytes=2268)
3 2 VIEW (Cost=5 Card=54 Bytes=1404)
4 3 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
6 5 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card=54)
7 2 VIEW (Cost=35 Card=4 Bytes=64)
8 7 SORT (GROUP BY) (Cost=35 Card=4 Bytes=216)
9 8 NESTED LOOPS (Cost=31 Card=4 Bytes=216)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
11 10 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
12 9 VIEW
13 12 UNION-ALL
14 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=3047 Bytes=63987)
15 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=20313 Bytes=304695)
Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
1059 consistent gets
0 physical reads
0 redo size
1713 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
23 rows processed
主要的問題在於IF_BALANCELIST要執行兩次全表掃描。而判斷條件中使用balance_dtime BETWEEN
his_report_arg.start_dt AND his_report_arg.end_dt，沒有很好地利用IF_BALANCELIST建立的
balance_dtime與waste_dtime索引，應該修改如下：
SELECT fee_stat_name, NVL (SUM (c), 0)
FROM (SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0',
balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') <> '2') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
UNION ALL
SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST" tot_cost, '1',
waste_dtime, "OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE waste_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate)
GROUP BY fee_stat_name, print_order
ORDER BY print_order
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=108 Bytes=3888)
1 0 SORT (GROUP BY) (Cost=26 Card=108 Bytes=3888)
2 1 VIEW (Cost=22 Card=108 Bytes=3888)
3 2 UNION-ALL
4 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
5 4 VIEW (Cost=5 Card=54 Bytes=1404)
6 5 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
8 7 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
9 4 VIEW (Cost=6 Card=1 Bytes=16)
10 9 SORT (GROUP BY) (Cost=6 Card=1 Bytes=45)
11 10 NESTED LOOPS (Cost=2 Card=1 Bytes=45)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
13 12 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=3047 Bytes=63987)
15 14 INDEX (RANGE SCAN) OF 'I_IF_BLANCELIST_BALANCE_DTIME' (NON-UNIQUE)
16 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
17 16 VIEW (Cost=5 Card=54 Bytes=1404)
18 17 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
19 18 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
20 19 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
21 16 VIEW (Cost=6 Card=3 Bytes=48)
22 21 SORT (GROUP BY) (Cost=6 Card=3 Bytes=117)
23 22 NESTED LOOPS (Cost=2 Card=3 Bytes=117)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
25 24 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
26 23 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=20313 Bytes=304695)
27 26 INDEX (RANGE SCAN) OF 'I_IF_BALANCELIST_WASTE_DTIME' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1715 bytes sent via SQL*Net to client
1131 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
23 rows processed
這樣就能夠充分利用IF_BALANCELIST建立的balance_dtime與waste_dtime索引。
今天跟蹤程式，無意中發現一條不良的SQL語句
分析如下：
SELECT fee_stat_name, NVL (t1.b, 0)
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0', balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') <> '2'
UNION ALL
SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST", '1', waste_dtime,
"OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
ORDER BY t2.print_order
執行計劃如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=54 Bytes=2268)
1 0 SORT (ORDER BY) (Cost=44 Card=54 Bytes=2268)
2 1 HASH JOIN (OUTER) (Cost=40 Card=54 Bytes=2268)
3 2 VIEW (Cost=5 Card=54 Bytes=1404)
4 3 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
6 5 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card=54)
7 2 VIEW (Cost=35 Card=4 Bytes=64)
8 7 SORT (GROUP BY) (Cost=35 Card=4 Bytes=216)
9 8 NESTED LOOPS (Cost=31 Card=4 Bytes=216)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
11 10 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
12 9 VIEW
13 12 UNION-ALL
14 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=3047 Bytes=63987)
15 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=20313 Bytes=304695)
Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
1059 consistent gets
0 physical reads
0 redo size
1713 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
23 rows processed
主要的問題在於IF_BALANCELIST要執行兩次全表掃描。而判斷條件中使用balance_dtime BETWEEN
his_report_arg.start_dt AND his_report_arg.end_dt，沒有很好地利用IF_BALANCELIST建立的
balance_dtime與waste_dtime索引，應該修改如下：
SELECT fee_stat_name, NVL (SUM (c), 0)
FROM (SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0',
balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') <> '2') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
UNION ALL
SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST" tot_cost, '1',
waste_dtime, "OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE waste_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate)
GROUP BY fee_stat_name, print_order
ORDER BY print_order
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=108 Bytes=3888)
1 0 SORT (GROUP BY) (Cost=26 Card=108 Bytes=3888)
2 1 VIEW (Cost=22 Card=108 Bytes=3888)
3 2 UNION-ALL
4 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
5 4 VIEW (Cost=5 Card=54 Bytes=1404)
6 5 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
8 7 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
9 4 VIEW (Cost=6 Card=1 Bytes=16)
10 9 SORT (GROUP BY) (Cost=6 Card=1 Bytes=45)
11 10 NESTED LOOPS (Cost=2 Card=1 Bytes=45)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
13 12 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=3047 Bytes=63987)
15 14 INDEX (RANGE SCAN) OF 'I_IF_BLANCELIST_BALANCE_DTIME' (NON-UNIQUE)
16 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
17 16 VIEW (Cost=5 Card=54 Bytes=1404)
18 17 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
19 18 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
20 19 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
21 16 VIEW (Cost=6 Card=3 Bytes=48)
22 21 SORT (GROUP BY) (Cost=6 Card=3 Bytes=117)
23 22 NESTED LOOPS (Cost=2 Card=3 Bytes=117)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
25 24 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
26 23 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=20313 Bytes=304695)
27 26 INDEX (RANGE SCAN) OF 'I_IF_BALANCELIST_WASTE_DTIME' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1715 bytes sent via SQL*Net to client
1131 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
23 rows processed
這樣就能夠充分利用IF_BALANCELIST建立的balance_dtime與waste_dtime索引。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-82779/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條SQL語句的優化過程SQL優化
- SQL語句優化－－十條經驗SQL優化
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 一個SQL語句的優化SQL優化
- 一條update語句的優化探索優化
- SQL語句的優化SQL優化
- 一條sql語句的建議調優分析SQL
- 一條簡單的SQL語句優化-新年新氣象SQL優化
- SQL語句優化SQL優化
- 總結出10條SQL語句優化精髓SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- SQL 語句的優化方法SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- 一次sql語句優化的反思SQL優化
- Mysql 52條SQL語句效能優化策略彙總MySql優化
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- 求助：SQL語句優化SQL優化
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條SQL語句的旅行之路SQL
- SQL語句優化的34條建議(轉-浪花七八朵 )SQL優化
- 優化 SQL 語句的步驟優化SQL
- 關於sql語句的優化SQL優化
- sql語句的優化案例分析SQL優化
- [20151209]一條sql語句的優化(續).txtSQL優化
- MySQL之SQL語句優化MySql優化
- SQL語句優化（轉載）SQL優化
- 常用SQL語句優化技巧SQL優化
- Oracle之sql語句優化OracleSQL優化
- 一條SQL語句的執行計劃變化探究SQL
- 一條sql語句的改進探索SQL