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 -> Can this be optimized further?

Can this be optimized further?

From: Gaylen Fraley <gfraley5_at_earthlink.net>
Date: Thu, 06 Sep 2001 00:30:21 GMT
Message-ID: <xszl7.10932$IP6.906809@newsread2.prod.itd.earthlink.net>


I have an Oracle query that I need some assistance on. It is simple, conceptually, but not necessarily technically. I will show it here and then explain
it.

create table SP_spr_sp0140767 as
select i.id
,spr.espdate
,spr.promotability
,spr.potential
,spr.evaluator
,spr.evalcomments
,spr.sme
,spr.smeexpertise

from esp_individ_d i
, esp_sprating_d spr

where i.id = spr.id
and spr.espdate = (select max(espdate)
from esp_sprating_d r where i.id=r.id )

union

select i.id
,spr.espdate
,spr.promotability
,spr.potential
,spr.evaluator
,spr.evalcomments
,spr.sme
,spr.smeexpertise

from esp_individ_d i
, esp_sprating_d spr

where i.id = spr.id (+)
and not exists ( select 1 from esp_sprating_d spr where i.id = spr.id );

The first where clause, before the union statement, selects the most recent table entry. The last where clause, basically, puts a placeholder record in the output file (in ESP_INDIVID_D and NOT in ESP_SPRATING_D). ESP_INDIVID_D contains all eligible employees. ESP_SPRATING_D only contains records for employees that have ratings and it can span several years. Thus, the reason for the first where clause to get the latest record. I have to end up with a file equal to the total in ESP_INDIVID_D.

This works and works reasonably quick. The problem is that I end up doing this for 4 tables. Every query that is run takes 2 minutes of clock time and there are hundreds! I am pretty well versed at SQL (DB2 and Oracle) and have not been able to optimize this beyond what you see here. Would you have any suggestions to speed this up? Thanks, Received on Wed Sep 05 2001 - 19:30:21 CDT

Original text of this message

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