- Stacy Mader <[EMAIL PROTECTED]> on 07/29/01 23:03:10 +1000:
> With our report/response database, fault_no's can have one, multiple or
> null response_no's. The SQL below returns distinct fault_no's regardless
>
> if it has one, multiple or null response's. The SQL does the job, but
> can you figure out it uses up a large amount of CPU (>60% on an Ultra
> 1)?
> I'm only dealing with ~ 1400 rows.
>
> Is there a better method?
>
> SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
> FROM stacy s
> WHERE (s.response_no =
> (
> SELECT max(response_no)
> FROM stacy
> WHERE fault_no = s.fault_no
> ) OR response_no is null
> )
>
>
> BTW: This is operating on an Oracle VIEW. I'm using Oracle 7.3.3 via
> perl5.6.0/DBI1.14
NULL's are evil in most cases -- C.J. Date explains why better than
I can. You would be better off using a default value for the field
and simply selecting what you want. After that the query breaks
down to a simple join on -- hopefully -- indexed fields.
sl
