Help with improving SQL response time
438181Sep 20 2006 — edited Sep 27 2006Hi: the following statement runs very slow. Any suggestions on how I can improve the response time.
select EVENT_DATA.TYPE, COUNT(*)
from EVENT_DATA
where
EVENT_DATA.class_id = 910713 AND
EVENT_DATA.agent_id = 160 AND
(TO_CHAR((TO_DATE('01/01/1970','MM/DD/YYYY') + (EVENT_DATA.utimestamp/86400)), 'MM/YYYY') = TO_CHAR(SYSDATE-2, 'MM/YYYY'))
GROUP by EVENT_DATA.TYPE;
The table details is below
===================
SQL> describe EVENT_DATA;
Name Null? Type
----------------------------------------- -------- ----------------------------
UTIME NOT NULL NUMBER(10)
UTIMESTAMP NOT NULL NUMBER(10)
AGENT_ID NOT NULL NUMBER(38)
PRIORITY NOT NULL NUMBER(3)
CLASS_ID NOT NULL NUMBER(38)
TYPE NOT NULL VARCHAR2(80)
EVENT_ID NOT NULL NUMBER(20)
SRCID NOT NULL NUMBER(20)
DSTID NOT NULL NUMBER(20)
INFO VARCHAR2(4000)
USERNAME VARCHAR2(255)
USERCONTEXT VARCHAR2(255)
===================
Currently the table is indexed on the following fields
=====================
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- ------------------------------
EVENT_DATA_AGENT_IDX NONUNIQUE AGENT_ID
EVENT_DATA_DSTID_IDX NONUNIQUE DSTID
EVENT_DATA_PART_IDX NONUNIQUE UTIME
EVENT_DATA_SRCID_IDX NONUNIQUE SRCID
PK_EVENT_DATA_EVENT_ID UNIQUE EVENT_ID
=====================
Thanks
Ravi