Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Can this be optimized further?
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