Steve,
Thanks for the info. The view was created using the fault_no column as the common
link between my two original tables.
Using your idea of the GROUP BY clause, I created another view
(report_response_index)
which contains only the fault_no and latest response_no. Created like:
CREATE VIEW report_response_index AS
(
SELECT a.fault_no,max(response_no) latest_response
FROM report a, response b
WHERE a.fault_no = b.fault_no(+)
GROUP BY a.fault_no
)
It would be great if I could create a view where I could add other columns,
but I don't know if you can(??) Now I've got to work out if I can apply your
2nd SQL...
Regards,
Stacy.
Steve Howard wrote:
> Generally speaking, getting a spike above 60% is nothing to worry about. How
> long does the processor stay above 60%? If it is for several seconds, then
> you might have something to think about.
>
> First, how is your table indexed? I would hope as a minimum you have an
> index on the Fault_No and Response_no columns.
>
> Last, yes, generally there is a more efficient way to get these results than
> the correlated sub-query like you are using. This does not always give
> faster results - especially when the table is small, but bench-mark it and
> see.
>
> Select the results of your sub-query into a temp table, and see if that
> works better. I'm not 100% sure how oracle handles temp tables, but here is
> how I would normally do it - the concept should be the same:
>
> SELECT fault_no, max(response_no) as response_no
> INTO #temp
> FROM stacy
> group by fault_no
>
> then modify your query like this:
>
> SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
> FROM stacy s
> join #temp t on s.fault_no = t.fault_no
> and ((s.response_no = t.response_no) or (s.response_no is null and
> t.response_no is null))
>
> When you've got the results in #temp already, that second will limit the
> results as you want them, and should be faster than the correlated subquery.
> You may need to modify that a bit to be oracle specific, but try it and see
> if it is faster.
>
> Steve H.
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, July 29, 2001 8:03 AM
> To: DBI Users
> Subject: SQL efficiency
>
> Hi all,
>
> 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
>
> Regards,
>
> Stacy.
