How can I optimize this SQL

I'm looking for advice, errr, again...

I have an SQL that gets executed repeatedly in an application that is quite slow. (It works, but slows down the app).

There are two tables involved:
Events and Certs
In Events, EventID is the PK, the table contains primary events as well as Cert events.
Certs contains just two columns - Certification events and MainEvent, which is a link back to Events

This SQL gets the needed count:

SELECT Count(C.CertEvent)
FROM Events E, Certs C, Eventsl TE
WHERE E.EventID=C.MainEvent(+)
  AND C.CertEvent=TE.EventID(+)
  AND TE.Task_ <> 'Audit'
  AND E.EventID=3158

As you can see, it gets a count of all Cert events, linked to a specific primary event which are not of type Audit. There are indexes on all involved fields (I'm using 8.1.7.4). The Events table is medium size, Certs is quite small.

Is there a way to get that count more efficient by restructuring the SQL? Or.. Perhaps I simply have an inefficient design and the CertEvent ID should simply be kept in the Events table? I think if I added the Task type in the Certs table, it will violate normalization (same data in two places), but would probably make the query faster. I do have the option of changing the table structure if I can improve performance significantly.

I'd be grateful for any suggestions. Received on Sun Oct 05 2003 - 01:41:41 CDT