Re: How can I optimize this SQL

Randy Harris wrote:

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

A few very quick thought:

1. Where's the EXPLAIN plan?
2. What indexes exist supporting this query?
3. Are you using RBO or CBO?
4. If CBO are statistics current?
5. Rewrite this with EXISTS.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)