Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Help with improving SQL response time

438181Sep 20 2006 — edited Sep 27 2006
Hi: 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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2006
Added on Sep 20 2006
17 comments
830 views