17,377
社区成员
发帖
与我相关
我的任务
分享求优化sql,附带解释计划
因为测试的数据很少，不懂要怎么优化了
select a1.*, b1.*
from (select a.acc re_acc,
round(nvl(sum(case
when fund_type = 0 then
amt1
end),
0),
2) dsfcg,
round(nvl(sum(case
when fund_type = 0 then
amt2
end),
0),
2) bxzj,
round(nvl(sum(case
when fund_type = 0 then
amt3
end),
0),
2) lczj,
round(nvl(sum(case
when fund_type = 1 then
amt1
end),
0),
2) dfjexj,
round(nvl(sum(case
when fund_type = 1 then
amt2
end),
0),
2) dfgz,
round(nvl(sum(case
when fund_type = 1 then
amt3
end),
0),
2) dfylj,
round(nvl(sum(case
when fund_type = 1 then
amt4
end),
0),
2) dfgk,
round(nvl(sum(case
when fund_type = 1 then
amt5
end),
0),
2) dfcz,
round(nvl(sum(case
when fund_type = 1 then
amt6
end),
0),
2) dfbx,
round(nvl(sum(case
when fund_type = 1 then
amt7
end),
0),
2) dfyy,
round(nvl(sum(case
when fund_type = 2 then
amt1
end),
0),
2) dsjexj,
round(nvl(sum(case
when fund_type = 2 then
amt2
end),
0),
2) sjdj,
round(nvl(sum(case
when fund_type = 2 then
amt3
end),
0),
2) df,
round(nvl(sum(case
when fund_type = 2 then
amt4
end),
0),
2) sf,
round(nvl(sum(case
when fund_type = 2 then
amt5
end),
0),
2) skh,
round(nvl(sum(case
when fund_type = 2 then
amt6
end),
0),
2) bmyzt,
round(nvl(sum(case
when fund_type = 2 then
amt7
end),
0),
2) dsjy,
round(nvl(sum(case
when fund_type = 2 then
amt8
end),
0),
2) dsbx,
round(nvl(sum(case
when fund_type = 6 then
amt1
end),
0),
2) dg,
round(nvl(sum(case
when fund_type = 6 then
amt2
end),
0),
2) zhhk,
round(nvl(sum(case
when fund_type = 6 then
amt3
end),
0),
2) xdzj,
round(nvl(sum(case
when fund_type = 3 then
amt1
end),
0),
2) ks_zcxj,
round(nvl(sum(case
when fund_type = 3 then
amt2
end),
0),
2) ks_ydcq,
round(nvl(sum(case
when fund_type = 3 then
amt3
end),
0),
2) ks_zz,
round(nvl(sum(case
when fund_type = 3 then
amt4
end),
0),
2) ks_syt,
round(nvl(sum(case
when fund_type = 4 then
amt1
end),
0),
2) sn_zcxj,
round(nvl(sum(case
when fund_type = 4 then
amt2
end),
0),
2) sn_ydcq,
round(nvl(sum(case
when fund_type = 4 then
amt3
end),
0),
2) sn_zz,
round(nvl(sum(case
when fund_type = 4 then
amt4
end),
0),
2) sn_syt,
round(nvl(sum(case
when fund_type = 6 then
amt4
end),
0),
2) bwd_zc,
round(nvl(sum(case
when fund_type = 5 then
amt1
end),
0),
2) kh_zcxj,
round(nvl(sum(case
when fund_type = 5 then
amt2
end),
0),
2) kh_ydcq,
round(nvl(sum(case
when fund_type = 5 then
amt3
end),
0),
2) kh_zz,
round(nvl(sum(case
when fund_type = 5 then
amt4
end),
0),
2) kh_syt
from t_acc_bal_chg_dtl a
where a.txn_date >= '20110701'
and a.txn_date <= '20110728'
and a.inst_no = '350101003'
group by a.acc) a1,
(select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_id_jz,
b.cust_manager_name_jz,
b.cust_manager_id_xd,
b.cust_manager_name_xd,
round(nvl(sum(b.tran_amt), 0), 2) bq
from t_acc_bal_chg_info b
where b.txn_date >= '20110701'
and b.txn_date <= '20110728'
group by b.acc,
b.cust_name,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_id_jz,
b.cust_manager_name_jz,
b.cust_manager_id_xd,
b.cust_manager_name_xd) b1
where a1.re_acc = b1.acc(+)
order by nvl(bq, 0) DESC
...全文
请发表友善的回复…
发表回复
aoxianglin 2011-08-03
- 打赏
- 举报
哎 真杯具
aoxianglin 2011-07-29
- 打赏
- 举报
[Quote=引用 6 楼 benchim888 的回复:]
SQL code
--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。
--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。
[/Quote]
所以是有
TXN_DATE, ACC, FUND_TYPE, INST_NO这几个是联合索引
SQL code
--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。
--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。
[/Quote]
所以是有
TXN_DATE, ACC, FUND_TYPE, INST_NO这几个是联合索引
BenChiM888 2011-07-29
- 打赏
- 举报
那就试试临时表的方法吧。
[Quote=引用 8 楼 aoxianglin 的回复:]
引用 6 楼 benchim888 的回复:
SQL code
--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。
--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。
所以是有
TXN_DATE, ACC, FUN……
[/Quote]
[Quote=引用 8 楼 aoxianglin 的回复:]
引用 6 楼 benchim888 的回复:
SQL code
--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。
--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。
所以是有
TXN_DATE, ACC, FUN……
[/Quote]
aoxianglin 2011-07-28
- 打赏
- 举报
[Quote=引用 2 楼 caoleione 的回复:]
1、a1子查询语句中把case换成decode
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_i……
[/Quote]
decode与case效率应该差不了多少吧？
第二个那个over()报错了
1、a1子查询语句中把case换成decode
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_i……
[/Quote]
decode与case效率应该差不了多少吧？
第二个那个over()报错了
aoxianglin 2011-07-28
- 打赏
- 举报
[Quote=引用 2 楼 caoleione 的回复:]
1、a1子查询语句中把case换成decode
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_i……
[/Quote]
谢谢，我试验下
1、a1子查询语句中把case换成decode
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_i……
[/Quote]
谢谢，我试验下
caoleione 2011-07-28
- 打赏
- 举报
1、a1子查询语句中把case换成decode
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_id_jz,
b.cust_manager_name_jz,
b.cust_manager_id_xd,
b.cust_manager_name_xd,
round(nvl(sum(b.tran_amt)over(), 0), 2) bq
from t_acc_bal_chg_info b
where b.txn_date >= '20110701'
and b.txn_date <= '20110728'
2、b1子查询可改写
select b.acc,
b.cust_name,
b.cust_id,
b.assets,
b.vip_stat,
b.psbc_vip_cd,
b.cust_manager_id_zz,
b.cust_manager_name_zz,
b.cust_manager_id_jz,
b.cust_manager_name_jz,
b.cust_manager_id_xd,
b.cust_manager_name_xd,
round(nvl(sum(b.tran_amt)over(), 0), 2) bq
from t_acc_bal_chg_info b
where b.txn_date >= '20110701'
and b.txn_date <= '20110728'
aoxianglin 2011-07-28
- 打赏
- 举报
执行计划
表结构
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1167080402
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 7950
| 1 | SORT ORDER BY | | 3 | 7950
|* 2 | HASH JOIN OUTER | | 3 | 7950
| 3 | VIEW | | 3 | 7392
| 4 | HASH GROUP BY | | 3 | 192
| 5 | TABLE ACCESS BY INDEX ROWID| T_ACC_BAL_CHG_DTL | 6 | 384
|* 6 | INDEX RANGE SCAN | IDX_ACC_BAL_CHG_DTL | 6 |
| 7 | VIEW | | 2 | 372
| 8 | HASH GROUP BY | | 2 | 90
| 9 | TABLE ACCESS BY INDEX ROWID| T_ACC_BAL_CHG_INFO | 2 | 90
|* 10 | INDEX RANGE SCAN | IDX_ACC_BAL_CHGL_INFO | 2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("A1"."RE_ACC"="B1"."ACC"(+))
6 - access("A"."TXN_DATE">='20110701' AND "A"."INST_NO"='350101003' AND
"A"."TXN_DATE"<='20110728')
filter("A"."INST_NO"='350101003')
10 - access("B"."TXN_DATE">='20110701' AND "B"."TXN_DATE"<='20110728')
Note
-----
- 'PLAN_TABLE' is old version
表结构
create table T_ACC_BAL_CHG_DTL
(
TXN_DATE CHAR(8),
ACC CHAR(19),
INST_NO CHAR(9),
FUND_NAME1 VARCHAR2(60),
AMT1 NUMBER(16,2),
FUND_NAME2 VARCHAR2(60),
AMT2 NUMBER(16,2),
FUND_NAME3 VARCHAR2(60),
AMT3 NUMBER(16,2),
FUND_NAME4 VARCHAR2(60),
AMT4 NUMBER(16,2),
FUND_NAME5 VARCHAR2(60),
AMT5 NUMBER(16,2),
FUND_NAME6 VARCHAR2(60),
AMT6 NUMBER(16,2),
FUND_NAME7 VARCHAR2(60),
AMT7 NUMBER(16,2),
FUND_NAME8 VARCHAR2(60),
AMT8 NUMBER(16,2),
FUND_NAME9 VARCHAR2(60),
AMT9 NUMBER(16,2),
FUND_NAME10 VARCHAR2(60),
AMT10 NUMBER(16,2),
FUND_TYPE CHAR(1)
);
create table T_ACC_BAL_CHG_INFO
(
TXN_DATE CHAR(8),
ACC CHAR(19),
CUST_NAME VARCHAR2(40),
CUST_ID CHAR(14),
ASSETS VARCHAR2(20),
VIP_STAT VARCHAR2(20),
PSBC_VIP_CD CHAR(20),
CUST_MANAGER_ID_ZZ CHAR(9),
CUST_MANAGER_NAME_ZZ VARCHAR2(20),
CUST_MANAGER_ID_JZ CHAR(9),
CUST_MANAGER_NAME_JZ VARCHAR2(20),
CUST_MANAGER_ID_XD CHAR(9),
CUST_MANAGER_NAME_XD VARCHAR2(20),
INST_NO CHAR(9),
TRAN_AMT NUMBER(16,2)
);
灰哥 2011-07-28
- 打赏
- 举报
阿门。
BenChiM888 2011-07-28
- 打赏
- 举报
--从你的SQL和执行计划看还真想不出优化的地方了。
--建议你如果能够建中间临时表的话，将上下两部分数据建成临时中间表，然后再建立索引，这样效率也许可以快点。
--另外a.inst_no 上是否存在索引，可以考虑在上面根据数据情况建立索引=号可以直接定位数据的。
aoxianglin 2011-07-28
- 打赏
- 举报
各位大神，
刚问了下,t_acc_bal_chg_dtl这张表就6千多万的数据，另外张表数据也很大。
直接执行这样的语句，效率很挺快的
select *
from t_acc_bal_chg_dtl a
where a.txn_date >= '20110701'
and a.txn_date <= '20110728'
and a.inst_no = '350101003'
这样查询出来，就几万的数据，
就是上面执行了分组汇总，还要根据fund_type不同的值行转列，这样效率就很慢了
求助~~~~
刚问了下,t_acc_bal_chg_dtl这张表就6千多万的数据，另外张表数据也很大。
直接执行这样的语句，效率很挺快的
select *
from t_acc_bal_chg_dtl a
where a.txn_date >= '20110701'
and a.txn_date <= '20110728'
and a.inst_no = '350101003'
这样查询出来，就几万的数据，
就是上面执行了分组汇总，还要根据fund_type不同的值行转列，这样效率就很慢了
求助~~~~
