Question

优化sql 速度太慢 主要优化 or 求高手解决 急急急

select t1.ID,
t1.BUG_ID,
t1.BUG_CODE,
t1.BUG_OBJECT_TYPE,
t1.S_K_CODE,
t1.MODEL_ID,
t1.DATA_IDENTIFY,
t1.CORRECT_TYPE,
t1.X_TABLE_ID,
t1.PROPERTY_VALUE,
t1.PROPERTY_VALUE_TYPE,
t1.PROPERTY_CURRENT_VALUE,
t1.BUG_CORRECT_VALUE,
t1.BUG_IDENTITY_ID,
(select g.name
from xq_std_bug_identity g
where g.std_bi_id = BUG_IDENTITY_ID) BUG_IDENTITY_DESCRI,
t1.BUG_STEP,
t1.BUG_LEVEL,
t1.BUG_SQL,
t1.BUG_DEAL_MAN,
t1.DEAL_ADVISE,
t1.bug_dept_id,
t2.bug_id bug_source_incode,
t2.XQ_CODE,
t2.SOURCE_ID,
t2.SOURCE_CODE,
t2.SOURCE_TYPE,
t2.STATUS,
t2.XQ_CREATER,
t2.XQ_CREATE_DATE,
t2.XQ_DEAL_TIME,
t1.BUG_TYPE,
t2.FIND_TIME,
T2.XQ_DEPT_ID,
f_get_account_name(t1.BUG_DEAL_MAN) MAN_NAME,
(select g.model_name from dq_models g where g.id = t1.model_id) modelName,
t1.bug_busi_place_code
from xq_bugs t1, xq_bug_source t2
where t1.bug_id = t2.bug_id
AND (bug_busi_place_code in
(select t.dept_id
from rs_dept t
start with t.dept_id = 1
connect by t.upper_dept_id = prior t.dept_id) or
xq_dept_id in
(select t.dept_id
from rs_dept t
start with t.dept_id = 1
connect by t.upper_dept_id = prior t.dept_id))
order by FIND_TIME DESC

Answer 1

最简单的改法就是把那些子查询提出来。

变成 select 。。。
from x inner join y on ...
inner join z on...
inner join w on ...
where ...
格式。

Answer 2

把in 换成exists 例如AND (exists
(select t.dept_id
from rs_dept t
start with t.dept_id = 1
connect by t.upper_dept_id = prior t.dept_id where bug_busi_place_code=t.相等的列)

该SQL的主要问题不在于or 在于In

追问

速度还是那么慢 能否再优化下

追答

你这几个表里多少数据啊 如果再不行 你就将SQL重写吧 改成内嵌连接方式，最好把order by 去掉 如果不去掉 可以在排序列上加索引