Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL: View performance problem

SQL: View performance problem

From: Sandra M Stock <sstock_at_cs.buffalo.edu>
Date: 1997/01/13
Message-ID: <5bdpe8$35e@prometheus.acsu.buffalo.edu>#1/1

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?

Thanks.

Sandy Stock
sstock_at_cs.buffalo.edu
Programmer/Analyst
Administrative Computing Services
State University of New York at Buffalo Received on Mon Jan 13 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US