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

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

Re: SQL: View performance problem

From: Jill Stephenson <jstephen_at_qld.design.telecom.com.au>
Date: 1997/01/16
Message-ID: <32DD9E6B.53BB@qld.design.telecom.com.au>#1/1

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

Original text of this message

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