select distinct dic.TYPE_ID,
(select p.TYPE_NAME
from COM_FEEDBACK_TYPE_DIC p
where p.TYPE_ID = substr(dic.TYPE_ID, 1, 3)) as FIRST_NAME,
dic.TYPE_NAME,
(select max(ver.version_code)
from com_feedback_app_version ver
where ver.system_id = apptype.system_id) as VERSION,
case
when dic.type_id like '202%' then
(select count(*)
from com_feedback t
where subject_type = dic.TYPE_ID)
else
(select count(*)
from com_feedback t
where subject_type = dic.TYPE_ID
and version_id =
(select max(ver.version_code)
from com_feedback_app_version ver
where ver.system_id = apptype.system_id
and t.parent_id = '-1'
and creation_date > add_months(sysdate, -1)))
end as SUBECT_COUNT,
app.system_short_name,
order_by
from COM_FEEDBACK_TYPE_DIC dic
left join com_feedback_app_type apptype on dic.type_id = apptype.type_id
left join com_feedback_app app on apptype.system_id = app.system_id
where dic.ENABLED_FLAG = 1
and length(dic.type_id) > 3
and dic.type_id != '201006'
and dic.type_id != '200011'
and dic.type_id != '200020'
order by order_by
目前搜索要5秒多 但是去掉 红色部分 只要 1秒不到
红色部分里面的表com_feedback 数据量上万 其它表都很少数据 不超过200该怎么优化?
(select p.TYPE_NAME
from COM_FEEDBACK_TYPE_DIC p
where p.TYPE_ID = substr(dic.TYPE_ID, 1, 3)) as FIRST_NAME,
dic.TYPE_NAME,
(select max(ver.version_code)
from com_feedback_app_version ver
where ver.system_id = apptype.system_id) as VERSION,
case
when dic.type_id like '202%' then
(select count(*)
from com_feedback t
where subject_type = dic.TYPE_ID)
else
(select count(*)
from com_feedback t
where subject_type = dic.TYPE_ID
and version_id =
(select max(ver.version_code)
from com_feedback_app_version ver
where ver.system_id = apptype.system_id
and t.parent_id = '-1'
and creation_date > add_months(sysdate, -1)))
end as SUBECT_COUNT,
app.system_short_name,
order_by
from COM_FEEDBACK_TYPE_DIC dic
left join com_feedback_app_type apptype on dic.type_id = apptype.type_id
left join com_feedback_app app on apptype.system_id = app.system_id
where dic.ENABLED_FLAG = 1
and length(dic.type_id) > 3
and dic.type_id != '201006'
and dic.type_id != '200011'
and dic.type_id != '200020'
order by order_by
目前搜索要5秒多 但是去掉 红色部分 只要 1秒不到
红色部分里面的表com_feedback 数据量上万 其它表都很少数据 不超过200该怎么优化?
给你们分 谢谢帮助