Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL: View performance problem
In article <5bdpe8$35e_at_prometheus.acsu.buffalo.edu>, sstock_at_cs.buffalo.edu (Sandra M Stock) wrote:
>We are running into a performance problem with a view, and I wonder if
>anyone has any ideas to improve response time. The problem is:
>
>We have an EMPLOYEE view that needs to be joined to an APPOINTMENT
>view. For each record in EMPLOYEE, there may be many records in
>APPOINTMENT. However, we only want one record output for each distinct
>employee (regardless of how many records exist in APPOINTMENT).
>Without removing duplicates, the queries against the view (select *
>from EMPLOYEE) return results in approximately 40 seconds. As soon as
>we add any logic to remove duplicates (i.e.: select distinct, a
>subquery in the WHERE clause to match a field to the MAX value of that
>field from a second instance of the APPOINTMENT table) response time
>jumps to 3 minutes, which times out our users before they get any
>results.
>
>Is there any way to do a join in such a way as to only get the first
>match back, without using distinct?
>Is there some SQL logic that can efficiently remove duplicate rows?
>
I had a similar issue last year, but unfortunately I don't have the code in front of me right now. I think what I did went as follows, but I'm composing this response as I go, so check my work...
Since you said you tried select distinct, I assume that you only need columns from your employee table in the select list. Try something like this:
create or replace view busy_employee as
select
e.* -- or whatever
from
employee e
where
exists
( select * from appointment a where a.employee_id = e.employee_id -- or whatever your key is and rownum <= 1 )
The "rownum <= 1" was important in my version of ORACLE, at least (7.1.3 on VMS).
Let me know if this helps, please.
-- Larry Reid lcreid_at_web.netReceived on Mon Jan 13 1997 - 00:00:00 CST