Re: How can I optimize this SQL

Randy Harris wrote:
[snip]
>
> 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

Looks like you aren't using bind variables. Bind vars will speed bu your query quite a bit.

>
> 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.

What are "medium size" and "quite small"? Have you done any kind of tracing on a session to determine what wait events are being generated?

>
> 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.

Trace will help clue you in on altering you tables.

>
> I'd be grateful for any suggestions.
>
>

-- 
Ron Reidy
Oracle DBA