我的一个表中有三列的复合索引.它可以找到我的查询在哪里有三列.当我的搜索查询只有三分之二时,事情似乎不再那么快了!
你知道如何解决这个问题吗?
谢谢,
谭
附:
桌子
APPL_PERF_STATS
具有复合索引(current_appl_id,event_name& generic_method_name)
这个花了2分钟(使用复合索引中的所有列):
SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != ‘NULL’ AND
CURRENT_APPL_ID != ‘NULL’ AND EVENT_NAME != ‘NULL’) AND ROWNUM < 502
AND current_appl_id = ‘OMS’ AND event_name = ‘OMS-CeaseProduct’ AND
generic_method_name = ‘CE CallForwardFixedCOProduct’ AND
appl_perf_interval_typ_id = 1440 AND cover_period_start_ts >=
to_date(’20-07-2008 14:36′,’dd-mm-yyyy HH24:mi’) AND
cover_period_start_ts <= to_date(’19-08-2009 14:36′,’dd-mm-yyyy
HH24:mi’) ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME,
CREATE_TS
这个需要12分钟才能运行(在复合索引中只使用三分之二):
SELECT * FROM APPL_PERF_STATS WHERE (GENERIC_METHOD_NAME != ‘NULL’ AND
CURRENT_APPL_ID != ‘NULL’ AND EVENT_NAME != ‘NULL’) AND ROWNUM < 502
AND current_appl_id = ‘OMS’ AND event_name = ‘OMS-CeaseProduct’ AND
appl_perf_interval_typ_id = 1440 AND cover_period_start_ts >=
to_date(’20-07-2008 14:36′,’dd-mm-yyyy HH24:mi’) AND
cover_period_start_ts <= to_date(’19-08-2009 14:36′,’dd-mm-yyyy
HH24:mi’) ORDER BY CURRENT_APPL_ID, EVENT_NAME, GENERIC_METHOD_NAME,
CREATE_TS