select
distinct t.alarmId,t.alarmName,t.alarmLevel,t.alarmTime,t.alarmDataSource,t.alarmDataSourceId,t.alarmSource,t.alarmSourceId,t.eventType,t.alarmType ,t.alarmContent ,t.isConfirm,t.isInCident, t.alarmSourceIpLong, t.alarmProperties
from (
select alarm.id as alarmId , alarm.alarm_name as alarmName , alarm.alarm_level as alarmLevel ,
to_char(alarm.old_alarm_time,'yyyy-mm-dd hh24:mi:ss') as alarmTime ,alarm.alarm_data_source as alarmDataSource ,
alarm.alarm_data_source_id as alarmDataSourceId , alarm.alarm_source as alarmSource , alarm.alarm_source_id as alarmSourceId ,
0 as eventType , alarm.alarm_type as alarmType , alarm.alarm_content as alarmContent ,
alarm.is_confirm as isConfirm ,alarm.is_incident as isInCident ,alarm.alarm_source_ip_long as alarmSourceIpLong ,
alarm.properties as alarmProperties
from tbl_mon_all_alarm_events alarm
union all
select his.id as alarmId , his.alarm_name as alarmName , his.alarm_level as alarmLevel ,
to_char(his.old_alarm_time,'yyyy-mm-dd hh24:mi:ss')as alarmTime ,his.alarm_data_source as alarmDataSource ,
his.alarm_data_source_id as alarmDataSourceId , his.alarm_source as alarmSource , his.alarm_source_id as alarmSourceId ,
1 as eventType , his.alarm_type as alarmType , his.alarm_content as alarmContent ,
his.is_confirm as isConfirm ,his.is_incident as isInCident ,his.alarm_source_ip_long as alarmSourceIpLong ,his.properties as alarmProperties
from tbl_mon_all_alarm_events_his his
)
t where 1 = 1面对这样的查询语句应该怎么优化呢? 2个表加起来的数据量为100w 采用分页 一次50条,查询时间为7~14秒左右,希望能优化到5秒以下oracle优化数据分页
distinct t.alarmId,t.alarmName,t.alarmLevel,t.alarmTime,t.alarmDataSource,t.alarmDataSourceId,t.alarmSource,t.alarmSourceId,t.eventType,t.alarmType ,t.alarmContent ,t.isConfirm,t.isInCident, t.alarmSourceIpLong, t.alarmProperties
from (
select alarm.id as alarmId , alarm.alarm_name as alarmName , alarm.alarm_level as alarmLevel ,
to_char(alarm.old_alarm_time,'yyyy-mm-dd hh24:mi:ss') as alarmTime ,alarm.alarm_data_source as alarmDataSource ,
alarm.alarm_data_source_id as alarmDataSourceId , alarm.alarm_source as alarmSource , alarm.alarm_source_id as alarmSourceId ,
0 as eventType , alarm.alarm_type as alarmType , alarm.alarm_content as alarmContent ,
alarm.is_confirm as isConfirm ,alarm.is_incident as isInCident ,alarm.alarm_source_ip_long as alarmSourceIpLong ,
alarm.properties as alarmProperties
from tbl_mon_all_alarm_events alarm
union all
select his.id as alarmId , his.alarm_name as alarmName , his.alarm_level as alarmLevel ,
to_char(his.old_alarm_time,'yyyy-mm-dd hh24:mi:ss')as alarmTime ,his.alarm_data_source as alarmDataSource ,
his.alarm_data_source_id as alarmDataSourceId , his.alarm_source as alarmSource , his.alarm_source_id as alarmSourceId ,
1 as eventType , his.alarm_type as alarmType , his.alarm_content as alarmContent ,
his.is_confirm as isConfirm ,his.is_incident as isInCident ,his.alarm_source_ip_long as alarmSourceIpLong ,his.properties as alarmProperties
from tbl_mon_all_alarm_events_his his
)
t where 1 = 1面对这样的查询语句应该怎么优化呢? 2个表加起来的数据量为100w 采用分页 一次50条,查询时间为7~14秒左右,希望能优化到5秒以下oracle优化数据分页
distinct 比较耗时的。
去掉了distinct 时间不怎么变化,求解
在就是 distinct 可能会造成查询慢不知道和索引有没有关系,打开执行计划看看