求助大佬们，oracle SQL优化，一条SQL语句要加载一两分钟（真心求助），慢得要死

Question

SELECT t.id,t.code,t.creater,st_createName.lastName createName,t.createDept,st_createDeptName.departmentName createDeptName,t.createDay,t.type,t.content,t.score,t.accepter,st_accepterName.lastName accepterName,t.acceptDept,st_acceptDeptName.departmentName acceptDeptName,st_acceptSubCpyName.subCompanyName acceptSubCpyName,t.acceptStatus,t.underTaker,st_underTakerName.lastName underTakerName,t.underTakeSender,t.planFinishDay,t.reason, '' Auto_Carry_Empty_Column ,(case t.type when 'QI' then '品质改善' when 'PEI' then '工艺、设备改进' when 'EI' then '现场环境改善' when 'PQ' then '产品使用品质' when 'PAS' then '产品应用服务' when 'MA' then '营销的方法' when 'ED' then '设备研发' when 'RM' then '方法研发' when 'PD' then '产品研发' when 'MI' then '管理创新' when 'CI' then '文化创新' else '' end) typeText,(case t.acceptStatus when 'Init' then '初始' when 'Back' then '采纳' when 'Execute' then '采纳' when 'Finish' then '完成' when 'CA' then '确认' when 'Recommend' then '确认' when 'Unable' then '无效' when 'DoNotAccept' then '不采纳' when 'Switch' then '初始' else '' end) statusText,(select count(s.id) from WV_Advice_Log s where s.adv = t.id and s.logType = 'Browse') browserCount,(select count(s.id) from WV_Advice_Log s where s.adv = t.id and s.logType = 'Talk') talkCount,concat(concat(concat(concat(''),t.content),'') showLink from WV_Advice t left join hrmResource st_createName on to_char(st_createName.id) = t.creater left join hrmDepartment st_createDeptName on to_char(st_createDeptName.id) = t.createDept left join hrmResource st_accepterName on to_char(st_accepterName.id) = t.accepter left join hrmDepartment st_acceptDeptName on to_char(st_acceptDeptName.id) = t.acceptDept left join hrmSubCompany st_acceptSubCpyName on to_char(st_acceptSubCpyName.id) = t.acceptDept left join hrmResource st_underTakerName on to_char(st_underTakerName.id) = t.underTaker where 1=1 and (1!=1 or acceptDept = '93' or accepter = '2304') and (1!=1 or acceptStatus = 'Init' or acceptStatus = 'Back' or acceptStatus = 'Switch') ORDER BY t.createDay desc , t.id desc

Answer 1

不懂业务的没法优化。
LEFT JOIN 的时候，数据量小的放前面。
原则就是：
1. 有索引的，用索引
2. 对大表做数据关联时，尽量不要把大表放到子查询中；有时可以考虑直接关联该表，并用 on 的条件做过滤。
3. 尽量减少使用函数