sql优化

wkc1203 2017-11-30 03:34:25
通过程序去访问,需要1.5分的样子才能返回结果,请问怎么优化sql语句

以下是语句

select u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.c_meter_number,
u.n_area_id,
u.c_user_address,
u.c_user_phone,
p.c_properties_name,
u.n_amount as n_now_balance,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
s.d_meter_reading_date,
u.n_enter_type,
decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') as type,
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999'))
from yx_detail d
where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
min(s.n_degrees_start) as minStart,
max(s.n_degrees_end) as maxEnd,
sum(s.n_real_dosage) as totalDosage,
sum(s.n_amount) as totalAmount,

s.c_situation_use_month,
decode(s.n_charge_state, 0, '欠费', '已缴费') as chargeState
from yx_situation s , yx_user u
,yx_model m
,yx_properties p
, yx_mobile_reading_record re
, yx_community co
, yx_meter_reader t
where
1=1
and s.c_user_id = u.c_user_id
and s.n_model_id = m.n_model_id
and u.n_properties_id = p.n_properties_id
<!-- and re.c_user_id=u.c_user_id -->
and co.n_company_id=u.n_company_id
and t.n_company_id=u.n_company_id
<if test="userid!=null">
and u.c_user_id=#{userid}
</if>
<if test="meterNumber!=null">
and u.c_meter_number=#{meterNumber}
</if>
group by u.c_old_user_id,
u.c_user_id,
u.c_user_name,
u.n_enter_type,
u.n_company_id,
t.c_meter_reader_name,
co.c_community_name,
re.n_reading_status,
p.c_properties_name,
u.c_user_address,
u.n_area_id,
u.c_user_phone,
u.n_meter_type,
u.n_amount,
m.c_model_name,
u.c_meter_number,
p.c_properties_name,
s.c_situation_use_month,
s.n_charge_state,
s.d_meter_reading_date,
s.n_situation_id

order by s.c_situation_use_month desc
...全文
418 15 点赞 打赏 收藏 举报
写回复
15 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wkc1203 2017-12-12
没有优化,把这个sql 语句拆分为两个分开查询了,问题解决了,结贴,感谢楼上的大佬们
  • 打赏
  • 举报
回复
minsic78 2017-12-04
没有执行计划的SQL优化都是耍流氓
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 5 楼 hi537638 的回复:
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999')) from yx_detail d where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice, 这句话慢,把这个函数换成listagg,而且这样查询是走嵌套查询的,查全量数据的时候会慢,把子查询改成表关联查询
这个没用过,要学习下
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 9 楼 qq646748739 的回复:
[quote=引用 6 楼 wkc1203的回复:][quote=引用 3 楼 qq646748739 的回复:] SQL语句还没调整?
没有啊,,好难,[/quote] 不难,你把这个SQL语句格式化下。[/quote] SELECT u.c_old_user_id, u.c_user_id, u.c_user_name, u.c_meter_number, u.n_area_id , u.c_user_address, u.c_user_phone, p.c_properties_name, u.n_amount AS n_now_balance, co.c_area_name , re.n_reading_status, s.d_meter_reading_date, u.n_enter_type, decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') AS type, ( SELECT wm_concat(to_char(d.n_detail_price, 'fm9999990.9999')) FROM yx_detail d WHERE d.n_cost_id = 1 AND d.n_situation_id = s.n_situation_id ) AS detailPrice , m.c_model_name, u.c_meter_number, p.c_properties_name, MIN(s.n_degrees_start) AS minStart, MAX(s.n_degrees_end) AS maxEnd , SUM(s.n_real_dosage) AS totalDosage, SUM(s.n_amount) AS totalAmount, s.c_situation_use_month, decode(s.n_charge_state, 0, '欠费', '已缴费') AS chargeState FROM yx_situation s JOIN yx_user u ON s.c_user_id = u.c_user_id JOIN yx_model m ON s.n_model_id = m.n_model_id JOIN yx_properties p ON u.n_properties_id = p.n_properties_id JOIN yx_area co ON co.n_area_id = u.n_area_id LEFT JOIN yx_mobile_reading_record re ON u.c_user_id = re.c_user_id LEFT JOIN yx_meter_reader t ON t.n_company_id = u.n_company_id WHERE 1 = 1 AND u.c_user_id = 19090540200327 GROUP BY u.c_old_user_id, u.c_user_id, u.c_user_name, u.n_enter_type, u.n_company_id, re.n_reading_status, co.c_area_name, p.c_properties_name, u.c_user_address, u.n_area_id, u.c_user_phone, u.n_meter_type, u.n_amount, m.c_model_name, u.c_meter_number, p.c_properties_name, s.c_situation_use_month, s.n_charge_state, s.d_meter_reading_date, s.n_situation_id, s.n_degrees_end ORDER BY s.c_situation_use_month DESC, s.n_degrees_end DESC
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 9 楼 qq646748739 的回复:
[quote=引用 6 楼 wkc1203的回复:][quote=引用 3 楼 qq646748739 的回复:] SQL语句还没调整?
没有啊,,好难,[/quote] 不难,你把这个SQL语句格式化下。[/quote] 格式化什么意思,有什么好处?? select u.c_old_user_id, u.c_user_id, u.c_user_name, u.c_meter_number, u.n_area_id, u.c_user_address, u.c_user_phone, p.c_properties_name, u.n_amount as n_now_balance, co.c_area_name, re.n_reading_status, s.d_meter_reading_date, u.n_enter_type, decode(u.n_meter_type, 0, '普通表', 1, '智能表', '远程表') as type, (select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999')) from yx_detail d where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice, m.c_model_name, u.c_meter_number, p.c_properties_name, min(s.n_degrees_start) as minStart, max(s.n_degrees_end) as maxEnd, sum(s.n_real_dosage) as totalDosage, sum(s.n_amount) as totalAmount, s.c_situation_use_month, decode(s.n_charge_state, 0, '欠费', '已缴费') as chargeState from yx_situation s join yx_user u on s.c_user_id = u.c_user_id join yx_model m on s.n_model_id = m.n_model_id join yx_properties p on u.n_properties_id = p.n_properties_id join yx_area co on co.n_area_id=u.n_area_id left join yx_mobile_reading_record re on u.c_user_id=re.c_user_id left join yx_meter_reader t on t.n_company_id=u.n_company_id where 1=1 group by u.c_old_user_id, u.c_user_id, u.c_user_name, u.n_enter_type, u.n_company_id, re.n_reading_status, co.c_area_name, p.c_properties_name, u.c_user_address, u.n_area_id, u.c_user_phone, u.n_meter_type, u.n_amount, m.c_model_name, u.c_meter_number, p.c_properties_name, s.c_situation_use_month, s.n_charge_state, s.d_meter_reading_date, s.n_situation_id, s.n_degrees_end order by s.c_situation_use_month desc,s.n_degrees_end desc
  • 打赏
  • 举报
回复
碧水幽幽泉 2017-12-01
引用 6 楼 wkc1203的回复:
[quote=引用 3 楼 qq646748739 的回复:] SQL语句还没调整?
没有啊,,好难,[/quote] 不难,你把这个SQL语句格式化下。
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 2 楼 alex259 的回复:
子查询中又行专列的函数,直接用with table as写成临时表再做关联吧,应该会快不少
用了下,一直报未明确定义列 00918
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 4 楼 jdsnhan 的回复:
贴出执行计划,大家一起帮你分析
就是希望select里的字段都显示出来,但是select里有的字段,group by 里也必须要有,很恼火,
  • 打赏
  • 举报
回复
wkc1203 2017-12-01
引用 3 楼 qq646748739 的回复:
SQL语句还没调整?
没有啊,,好难,
  • 打赏
  • 举报
回复
游灬魂 2017-12-01
(select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999')) from yx_detail d where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice, 这句话慢,把这个函数换成listagg,而且这样查询是走嵌套查询的,查全量数据的时候会慢,把子查询改成表关联查询
  • 打赏
  • 举报
回复
游灬魂 2017-12-01
引用 12 楼 wkc1203 的回复:
[quote=引用 5 楼 hi537638 的回复:] (select wm_concat(to_char(d.n_detail_price, 'fm9999990.9999')) from yx_detail d where d.n_cost_id=1 and d.n_situation_id = s.n_situation_id) as detailPrice, 这句话慢,把这个函数换成listagg,而且这样查询是走嵌套查询的,查全量数据的时候会慢,把子查询改成表关联查询
这个没用过,要学习下[/quote] 因为wm_concate返回的是大字段,而listagg是单独的拼接而已,还是varchar2类型,这个只是sql改写而已,做个子查询还不懂吗?
  • 打赏
  • 举报
回复
jdsnhan 2017-12-01
贴出执行计划,大家一起帮你分析
  • 打赏
  • 举报
回复
碧水幽幽泉 2017-11-30
SQL语句还没调整?
  • 打赏
  • 举报
回复
alex259 2017-11-30
子查询中又行专列的函数,直接用with table as写成临时表再做关联吧,应该会快不少
  • 打赏
  • 举报
回复
碧水幽幽泉 2017-11-30
怎么还有 <!-- and re.c_user_id=u.c_user_id --> <if </if>?
请调整好SQL再发一次。
  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle
加入

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2017-11-30 03:34
社区公告
暂无公告