sql语句优化问题

Question

大家看看这样的sql语句有哪些地方可以优化： 
select count(*) from (select CUID,ALARM_SEVERITY,ALARM_TYPE_NAME,ALARM_NAME,EMS_NAME,VENDOR,NET_NAME, 
TRANSSYS_NAME,PROVINCE_NAME, 
CITY_NAME,COUNTY_NAME,SITE_NAME,ALARMOBJECT_TYPE,ALM_DEVINFO, 
ALARM_PHYDEVINFO,NEALARM_TIME,EMSALARM_TIME,ALARM_REASON,ALARM_DESC, 
LAYER_RATE,TRAPH_NAME,SERVICE_LEVEL,ALARM_IMPORTANCE,ACK_USER,ACK_TIME, 
NEEND_TIME,EMSEND_TIME,REPORT_COUNT,IS_CANCLEAR,SIGNAL_RATE,FILTER_TYPE, 
VALID_FLAG,SHEET_LIMIT_TIME,RELATED_TASK_CUID,SENDTOEOMS_STATE, 
SENDTOEOMS_MODE,EOMS_SHEET_NUMBER,NE_MODEL,NE_CAPACITY, 
NE_SIGNAL_TYPE,DURING_TIME,ALARM_ID,ALARM_EXPLAIN,LOGICAL_CLASS, 
LOGICAL_SUBCLASS,EFFECT_DEVICE,EFFECT_SERVICE,RELATED_FLAG,SERVICE_TYPE, 
ALARM_STANDARD_NAME,VENDOR_ALARM_SEVERITY,IS_STANDARD, 
ALARM_STANDARD_TYPE,LAYER_RATE_DESC,EMOS_SHEET_STATE, 
RELATED_CURRENT_CUID,LOCATION_EXPLAIN,SLA_LEVEL,SLA_SERVICE, 
IS_CONCERNED,DERIVATIVE_INFO from HISTORY_ALARM where 
DERIVATIVE_INFO in (select CUID from HISTORY_ALARM 
where 1=1 and 
(NEALARM_TIME>=to_date('2011-07-18 03:00:30','%Y-%m-%d %H:%M:%S') 
and NEALARM_TIME<=to_date('2011-07-18 03:00:45','%Y-%m-%d %H:%M:%S')) and (derivative_info like 'DERI' or derivative_info like 'MAIN')))

Answer 1

这语法，
比较字符后面加 % 号
LIKE后面不加 % 号，
无语

Answer 2

优化后select count(*) 
from HISTORY_ALARM where exists 
 (select 1 from HISTORY_ALARM where 1=1 
and DERIVATIVE_INFO = CUID 
and (NEALARM_TIME>=to_date('2011-07-18 03:00:30','YYYY-MM-DD HH:MI:SS') 
and NEALARM_TIME<=to_date('2011-07-18 03:00:45','YYYY-MM-DD HH:MI:SS')) 
and (derivative_info like '%DERI%' or derivative_info like '%MAIN%'))

Answer 3

这样的sql主要是慢在了计数上，不知道该怎么优化了，3楼的办法时间那样写不能执行了select count(*) from (select CUID,ALARM_SEVERITY,ALARM_TYPE_NAME,ALARM_NAME,EMS_NAME,VENDOR,NET_NAME, 
TRANSSYS_NAME,PROVINCE_NAME,  
CITY_NAME,COUNTY_NAME,SITE_NAME,ALARMOBJECT_TYPE,ALM_DEVINFO,  
ALARM_PHYDEVINFO,NEALARM_TIME,EMSALARM_TIME,ALARM_REASON,ALARM_DESC,  
LAYER_RATE,TRAPH_NAME,SERVICE_LEVEL,ALARM_IMPORTANCE,ACK_USER,ACK_TIME,  
NEEND_TIME,EMSEND_TIME,REPORT_COUNT,IS_CANCLEAR,SIGNAL_RATE,FILTER_TYPE,  
VALID_FLAG,SHEET_LIMIT_TIME,RELATED_TASK_CUID,SENDTOEOMS_STATE,  
SENDTOEOMS_MODE,EOMS_SHEET_NUMBER,NE_MODEL,NE_CAPACITY,  
NE_SIGNAL_TYPE,DURING_TIME,ALARM_ID,ALARM_EXPLAIN,LOGICAL_CLASS,  
LOGICAL_SUBCLASS,EFFECT_DEVICE,EFFECT_SERVICE,RELATED_FLAG,SERVICE_TYPE,  
ALARM_STANDARD_NAME,VENDOR_ALARM_SEVERITY,IS_STANDARD,  
ALARM_STANDARD_TYPE,LAYER_RATE_DESC,EMOS_SHEET_STATE,  
RELATED_CURRENT_CUID,LOCATION_EXPLAIN,SLA_LEVEL,SLA_SERVICE,  
IS_CONCERNED,DERIVATIVE_INFO  from HISTORY_ALARM where  
DERIVATIVE_INFO in (select CUID from HISTORY_ALARM  
where 1=1 and  
(NEALARM_TIME>=to_date('2011-07-18 03:00:30','%Y-%m-%d %H:%M:%S')  
and NEALARM_TIME<=to_date('2011-07-18 03:00:45','%Y-%m-%d %H:%M:%S'))