Sql优化的大提升是什么

Sql优化的大提升是什么，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

创新互联服务项目包括通海网站建设、通海网站制作、通海网页制作以及通海网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，通海网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到通海省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！

最近在做集团任务中心视图的优化发现几个系统的sql视图慢的过分直接po代码核心的业务sql 已蒙蔽

create or replace view km_todotasklist as

select "ID","DEVICE_TYPE","SYSTEM_TYPE","TASK_CODE","TASK_TYPE1","TASK_TYPE2","TITLE","STATUS","OWNER_ID","OWNER_NAME","PRIORITY","CREATE_ID","CREATE_TIME","UPDATE_ID","UPDATE_TIME" from （

select t.id_ as id,

CASE

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is null THEN

'PC,APP'

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is not null THEN

'PC'

ELSE

(select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 )

END as device_type,

'KM' as system_type,

'核心业务URL’||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((select to_char(t.next_user_id_)

from OA_OT_PROCESS_RECORD t

where t.next_task_id_ = t.id_ and rownum=1)))) as task_code,

'代办' as task_type1,

to_char(mo.name) as task_type2,

to_char(a.title) as title,

'0' as status,

(select to_char(t.next_user_id_)

from OA_OT_PROCESS_RECORD t

where t.next_task_id_ = t.id_ and rownum=1) as owner_id,

(select em.real_name

from IMS_OT_USER t

inner join IMS_OT_EMPLOYEE em

on t.employee_id = em.id

where t.user_login_name =

(select t.next_user_id_

from OA_OT_PROCESS_RECORD t

where t.next_task_id_ = t.id_ and rownum=1) and rownum=1) as owner_name,

'' as priority,

a.creator as create_id,

a.create_time as create_time,

'' as update_id,

'' as update_time

from oa_ot_application a

inner join act_ru_task t

on t.proc_inst_id_ = a.pro_inst_id and t.task_def_key_ <> 'usertask2'

inner join oa_ot_application_ext et

on et.app_id = a.id and et.business_type ='1'

inner join OA_OT_APP_MODULE mo on a.module_id =mo.id

UNION ALL

select t.id as id,

'PC' as device_type,

'KM' as system_type,

'http://10.XX.XXX.XXX'||(select t.oa_req_url from OA_OT_APP_MODULE t where t.id =a.module_id)||'/input.htm?taskId='||t.id||'&appId='||a.id||'&sid='|| utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(to_char(t.user_id)))) as task_code,

'待阅' as task_type1,

to_char(mo.name) as task_type2,

to_char(a.title) as title,

'0' as status,

to_char(t.user_id) as owner_id,

(select em.real_name

from IMS_OT_USER t

inner join IMS_OT_EMPLOYEE em

on t.employee_id = em.id

where t.user_login_name = t.user_id and rownum=1) as owner_name,

'' as priority,

a.creator as create_id,

a.create_time as create_time,

'' as update_id,

'' as update_time

from oa_ot_application a

inner join oa_ru_circulation t

on t.proc_inst_id = a.pro_inst_id

inner join oa_ot_application_ext et

on et.app_id = a.id

inner join OA_OT_APP_MODULE mo on a.module_id =mo.id

） t order by t.create_time desc;

全局查询大概15秒左右。

1 了解核心业务需求

2 了解目前sql痛点

3 改进sql

create or replace view km_todotasklist as

select t.id_ as id,

CASE

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is null THEN

'PC,APP'

WHEN (select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 ) is not null THEN

'PC'

ELSE

(select t.cfg_value from SYS_OT_CONFIG t where t.code ='SERVER_H5_MODELID' and INSTR( t.cfg_value ,a.module_id)>0 )

END as device_type,

'KM' as system_type,

'核心业务URL'||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(re.next_user_id_))) as task_code,

'代办' as task_type1,

to_char(mo.name) as task_type2,

to_char(a.title) as title,

'0' as status,

re.next_user_id_ as owner_id,

emp.real_name as owner_name,

'' as priority,

a.creator as create_id,

a.create_time as create_time,

'' as update_id,

'' as update_time

from oa_ot_application a

inner join act_ru_task t

on t.proc_inst_id_ = a.pro_inst_id

inner join oa_ot_application_ext et

on et.app_id = a.id and et.business_type ='1'

inner join OA_OT_APP_MODULE mo on a.module_id =mo.id

left join OA_OT_PROCESS_RECORD re on re.next_task_id_ = t.id_

left join IMS_OT_USER uss on re.next_user_id_ =uss.user_login_name

left join IMS_OT_EMPLOYEE emp on uss.employee_id= emp.id;

目前sql已优化到0.3秒

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。

名称栏目：Sql优化的大提升是什么
分享链接：http://dzwzjz.com/article/jijope.html

大橙子网站建设，新征程启航

Sql优化的大提升是什么

其他资讯