Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL: View performance problem
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?
-- If you don't need any details from appointment, which is not clear from your question then you can use, eg., select * from employee e where exists (select 1 from appointment where emp_id = a.emp_id); otherwise you are such with something like, eg., select e.<details>, a.<details> from appointment a, employee e where a.emp_id = e.emp_id and a.appointment_id = (select max(appointment_id) from appointment where emp_id = a.emp_id); Obviously unless you have appropriate indexes on appointment then this won't help. You would need an index on emp_id at least. Hope this helps. Jill +------------------------------------------------+ | Jill Stephenson - Tortuga Technologies Pty Ltd | | | | Work email: jstephen_at_qld.design.telstra.com.au | | Home email: jill_at_tortuga.com.au | +------------------------------------------------+Received on Thu Jan 16 1997 - 00:00:00 CST