不知道对哪些表建索引 select type,subtype, contact_name,istatus, assignment,ci_name, hwdns_name, remind from (
select type, subtype, contact_name, istatus, assignment,ci_name, hwdns_name, islegal,
case
when num = 0 and nullnum >= 1 or num = 1 and inum >= 1
then 'ADD' end remind
from (select max(a.id) id, max(a.type) type, max(a.subtype) subtype, max(a.contact_name) contact_name,max(a.logical_name) ci_name,
max(a.islegal) islegal, max(a.assignment) assignment, max(a.hwdns_name) hwdns_name,
count(distinct e.enabled) num,
count(case when e.enabled = 'ACTIVE' then 1
else null end) Anum,
count(case
when e.enabled = 'INACTIVE' then 1
else null end) Inum,
case when max(nvl(e.enabled, 2)) = '2' then 1 else null end nullNum,
max(a.istatus) istatus, max(e.enabled) enabled, max(f.options) options from itsm.device2m1 a
inner join mbis_config_type_result_t b on a.type = b.ci_type
full outer join itsm.hwstoragem1 c on a.logical_name = c.logical_name
full outer join itsm.hwsanswitchm1 hws on a.logical_name = hws.logical_name
full outer join eflow_mon_middleware e on a.id = e.ci_id
inner join (select * from mbis_config_cistatus_t t where t.isactive = 1 and t.options = 1) f on f.ci_status = a.istatus
where b.ci_type_id in (6, 20) and (a.type || a.subtype in (select h.category_segment2 || h.category_segment3
from eflow_mon_standard h where enabled = 'ACTIVE'))
and a.assignment not in (select g.ci_group from mbis_config_group_t g where g.isactive = '1')
and a.subtype || a.contact_name || b.ci_type_id not in
(select ci.ci_subtype || ci.ci_owner || ci.ci_type_id
from mbis_config_ciowner_t ci)
and a.logical_name not in
(select cf.ci_name
from mbis_config_ci_t cf
where to_char(sysdate, 'yyyy-mm-dd') >=
to_char(start_date, 'yyyy-mm-dd')
and to_char(sysdate, 'yyyy-mm-dd') <=
to_char(end_date, 'yyyy-mm-dd'))
group by (a.logical_name)) isg
where isg.islegal = 'registered'
and isg.hwdns_name is not null) views
where views.remind is not null
select type, subtype, contact_name, istatus, assignment,ci_name, hwdns_name, islegal,
case
when num = 0 and nullnum >= 1 or num = 1 and inum >= 1
then 'ADD' end remind
from (select max(a.id) id, max(a.type) type, max(a.subtype) subtype, max(a.contact_name) contact_name,max(a.logical_name) ci_name,
max(a.islegal) islegal, max(a.assignment) assignment, max(a.hwdns_name) hwdns_name,
count(distinct e.enabled) num,
count(case when e.enabled = 'ACTIVE' then 1
else null end) Anum,
count(case
when e.enabled = 'INACTIVE' then 1
else null end) Inum,
case when max(nvl(e.enabled, 2)) = '2' then 1 else null end nullNum,
max(a.istatus) istatus, max(e.enabled) enabled, max(f.options) options from itsm.device2m1 a
inner join mbis_config_type_result_t b on a.type = b.ci_type
full outer join itsm.hwstoragem1 c on a.logical_name = c.logical_name
full outer join itsm.hwsanswitchm1 hws on a.logical_name = hws.logical_name
full outer join eflow_mon_middleware e on a.id = e.ci_id
inner join (select * from mbis_config_cistatus_t t where t.isactive = 1 and t.options = 1) f on f.ci_status = a.istatus
where b.ci_type_id in (6, 20) and (a.type || a.subtype in (select h.category_segment2 || h.category_segment3
from eflow_mon_standard h where enabled = 'ACTIVE'))
and a.assignment not in (select g.ci_group from mbis_config_group_t g where g.isactive = '1')
and a.subtype || a.contact_name || b.ci_type_id not in
(select ci.ci_subtype || ci.ci_owner || ci.ci_type_id
from mbis_config_ciowner_t ci)
and a.logical_name not in
(select cf.ci_name
from mbis_config_ci_t cf
where to_char(sysdate, 'yyyy-mm-dd') >=
to_char(start_date, 'yyyy-mm-dd')
and to_char(sysdate, 'yyyy-mm-dd') <=
to_char(end_date, 'yyyy-mm-dd'))
group by (a.logical_name)) isg
where isg.islegal = 'registered'
and isg.hwdns_name is not null) views
where views.remind is not null
开个传送门送你
oracle
go
想知道索引需要建立哪些列的索引