Sql优化的大提升是什么

Sql优化的大提升是什么，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。最近在做集团任务中心视图的优化 发现几个系统的sql视图慢的过分 直接po代码 核心的业务sql 已蒙蔽1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768createorreplaceviewkm_todotasklistasselect"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（selectt.id_asid,CASEWHEN(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)isnullTHEN'PC,APP'WHEN(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)isnotnullTHEN'PC'ELSE(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)ENDasdevice_type,'KM'assystem_type,'核心业务URL’||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((selectto_char(t.next_user_id_)fromOA_OT_PROCESS_RECORDtwheret.next_task_id_=t.id_andrownum=1))))astask_code,'代办'astask_type1,to_char(mo.name)astask_type2,to_char(a.title)astitle,'0'asstatus,(selectto_char(t.next_user_id_)fromOA_OT_PROCESS_RECORDtwheret.next_task_id_=t.id_andrownum=1)asowner_id,(selectem.real_namefromIMS_OT_USERtinnerjoinIMS_OT_EMPLOYEEemont.employee_id=em.idwheret.user_login_name=(selectt.next_user_id_fromOA_OT_PROCESS_RECORDtwheret.next_task_id_=t.id_andrownum=1)andrownum=1)asowner_name,''aspriority,a.creatorascreate_id,a.create_timeascreate_time,''asupdate_id,''asupdate_timefromoa_ot_applicationainnerjoinact_ru_tasktont.proc_inst_id_=a.pro_inst_idandt.task_def_key_'usertask2'innerjoinoa_ot_application_extetonet.app_id=a.idandet.business_type='1'innerjoinOA_OT_APP_MODULEmoona.module_id=mo.idUNIONALLselectt.idasid,'PC'asdevice_type,'KM'assystem_type,'http://10.XX.XXX.XXX'||(selectt.oa_req_urlfromOA_OT_APP_MODULEtwheret.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))))astask_code,'待阅'astask_type1,to_char(mo.name)astask_type2,to_char(a.title)astitle,'0'asstatus,to_char(t.user_id)asowner_id,(selectem.real_namefromIMS_OT_USERtinnerjoinIMS_OT_EMPLOYEEemont.employee_id=em.idwheret.user_login_name=t.user_idandrownum=1)asowner_name,''aspriority,a.creat 香港云主机orascreate_id,a.create_timeascreate_time,''asupdate_id,''asupdate_timefromoa_ot_applicationainnerjoinoa_ru_circulationtont.proc_inst_id=a.pro_inst_idinnerjoinoa_ot_application_extetonet.app_id=a.idinnerjoinOA_OT_APP_MODULEmoona.module_id=mo.id）torderbyt.create_timedesc;全局查询大概15秒左右。1 了解核心业务需求2 了解目前sql痛点3 改进sql1234567891011121314151617181920212223242526272829303132createorreplaceviewkm_todotasklistasselectt.id_asid,CASEWHEN(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)isnullTHEN'PC,APP'WHEN(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)isnotnullTHEN'PC'ELSE(selectt.cfg_valuefromSYS_OT_CONFIGtwheret.code='SERVER_H5_MODELID'andINSTR(t.cfg_value,a.module_id)>0)ENDasdevice_type,'KM'assystem_type,'核心业务URL'||utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(re.next_user_id_)))astask_code,'代办'astask_type1,to_char(mo.name)astask_type2,to_char(a.title)astitle,'0'asstatus,re.next_user_id_asowner_id,emp.real_nameasowner_name,''aspriority,a.creatorascreate_id,a.create_timeascreate_time,''asupdate_id,''asupdate_timefromoa_ot_applicationainnerjoinact_ru_tasktont.proc_inst_id_=a.pro_inst_idinnerjoinoa_ot_application_extetonet.app_id=a.idandet.business_type='1'innerjoinOA_OT_APP_MODULEmoona.module_id=mo.idleftjoinOA_OT_PROCESS_RECORDreonre.next_task_id_=t.id_leftjoinIMS_OT_USERussonre.next_user_id_=uss.user_login_nameleftjoinIMS_OT_EMPLOYEEemponuss.employee_id=emp.id;

目前sql已优化到0.3秒看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注开发云行业资讯频道，感谢您对开发云的支持。

相关推荐: 什么是Mongodb WiredTiger

这期内容当中小编将会给大家带来有关什么是Mongodb WiredTiger，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。Michael Cahill在2011年与另一个合伙人共同开发了wiredtiger。MongoDB …