收集个SQLHC

在ALARM_TIME列建立索引，如果ALARM_TIME可以为空，可以按如下方式建立索引
create index idx_xxx on AMOD_TARGET_ALARM(ALARM_TIMEdesc,0);
然后按以下hint运行sql即可
SELECT *
FROM (SELECT temptable., ROWNUM ROW_NUM
FROM (select /+ index(t ALARM_TIME的desc索引) use_nl(t,s,tc,r,a,si) leading(t) /a.IS_SENDZDALARM,
a.IS_SENDSMSALARM,
a.IS_SENDINTERFACEALARM,
t.TARGET_ID,
si.,
t.TARGET_TYPE,
t.UNITID,
t.ALARM_TYPE,
t.ALARM_TIME,
t.ALARM_CONTENT,
t.ALARM_LEVEL,
t.SCANCE,
s.UNITNAME,
tc.code_name,
tc.code_id,
r.PROVIDER_NAME,
r.PROVIDER_ID
from AMOD_TARGET_ALARM t
left join amod_target_orginfo s
on t.unitid = s.unitid
left join amod_type_code tc
on tc.code_id = t.alarm_type
left join AMOD_TARGET_PROVIDER r
on t.PROVIDER_ID = r.PROVIDER_ID
left join AMOD_TARGET_ITEM a
on t.TARGET_ID = a.TARGET_ID
and t.unitid = a.unitid
left join (select /*+ no_merge */ sm.targetalarmid,
to_char(wmsys.wm_concat(sm.people)) people,
to_char(wmsys.wm_concat(sm.phone)) phone
from amod_alarm_smsreceiveinfo sm
group by targetalarmid) si
on si.targetalarmid = t.id
where 1 = 1
order by ALARM_TIME desc) temptable
WHERE ROWNUM <= 20)
WHERE ROW_NUM >= 1;
另外如果创建索引之后执行计划全部自动走NL+视图谓词推入，就不用加Hint了

感谢大佬们帮忙看问题~