Currently Being Moderated
Hi,
I am hoping somebody here can help.
I currently have an Oracle 8i Database View/Query that is quite large and ugly and very time consuming to run and pulls information from multiple tables. I am after some tips to help me streamline it.
Would I be better to split this query up into multiple views? Is there any code I can change to help it run better.
Any help is appreciated :)
select "LINE_GOR","GROUPNAME","SUB","HUB","LINECODE","FROM_","TO_","LENGTH (m)","DINIS_GRP_CURRENT","% DINIS_GRP_CURRENT",
"DINIS_SEGMENT_CURRENT","GRP_SD50","GRP_SN50","GRP_WD50","GRP_WN50","SEG_SD","SEG_SN","SEG_WD","SEG_WN","SD_CONT_RAT",
"SN_CONT_RAT","WD_CONT_RAT","WN_CONT_RAT","SDLF","WDLF","SCRF","WCRF","SD_RATING","SN_RATING","WD_RATING","WN_RATING","SD_UTILISATION",
"SN_UTILISATION","WD_UTILISATION","WN_UTILISATION","WORST_UTILISATION","TMU","SD_GRP_RATING","SN_GRP_RATING","WD_GRP_RATING","WN_GRP_RATING",
"SD_GRP_VOLT_REG","SN_GRP_VOLT_REG","WD_GRP_VOLT_REG","WN_GRP_VOLT_REG","WORST_VOLT_REG","DINIS Power Loss (kW)","LOADFLOW_DATE",
"LOADFLOW_NOTES","PROJECT_NO","PROJECT_TYPE","POW","COMMENTS","COMMENTS_DATE","GRP_SD10","GRP_SN10","GRP_WD10","GRP_WN10"
from
(--------------------------------------------------------------------------------
-- CALCULATIONS
select groupname, sub, hub, line_gor, linecode, from_, to_, "LENGTH (m)", dinis_grp_current,
round("% DINIS_GRP_CURRENT",2) "% DINIS_GRP_CURRENT", dinis_segment_current,
grp_sd grp_sd50, grp_sn grp_sn50, grp_wd grp_wd50, grp_wn grp_wn50, seg_sd, seg_sn, seg_wd, seg_wn,
sd_cont_rat, sn_cont_rat, wd_cont_rat, wn_cont_rat,
sdlf, wdlf, scrf, wcrf,
round(sd_cont_rat*scrf,0) sd_rating, -- Segment Cyclic Ratings ie. Continuous Rating * Group Cyclic Rating Factor
round(sn_cont_rat*scrf,0) sn_rating,
round(wd_cont_rat*wcrf,0) wd_rating,
round(wn_cont_rat*wcrf,0) wn_rating,
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Utilisation
else seg_sd/(sd_cont_rat*scrf)*100 end) ,0) sd_utilisation, -- If SD Forecast for a feeder/group is 0, a DIINIS Loadflow is not run.
-- The % DINIS Group Current is Null and the Group Current is 0, and
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0 -- only Cnductor and Node data has been imported.
else seg_sn/(sn_cont_rat*scrf)*100 end) ,0) sn_utilisation, -- In this case the Utilisations are set to 0.
round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0 -- Utilisations are Segment Current / Segment Cyclic Rating
else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0) wd_utilisation,
round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0
else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) wn_utilisation,
greatest(
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Worst Utilisation
else seg_sd/(sd_cont_rat*scrf)*100 end) ,0),
round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0
else seg_sn/(sn_cont_rat*scrf)*100 end) ,0),
round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0
else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0),
round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0
else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) ) worst_utilisation,
tmu,
round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sd_cont_rat*scrf -- Group Rating relative to Segemnt Utilisation
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999 -- When the % DINIS Group Current is null and the Group Summer Day Load = 0
else (case when (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999 -- ie. No Loadflow has been run, the Segment Cyclic Rating becomes the Group Rating
else (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sd_grp_rating, -- When the % DINIS Group Current = 0, the Group Rating = 9999
-- The Group Rating is capped at 9999
-- Group Ratings are Cyclic Rating * 100 / % DINIS Group Current
round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sn_cont_rat*scrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sn_grp_rating,
round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wd_cont_rat*wcrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wd_grp_rating,
round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wn_cont_rat*wcrf
else (case when "% DINIS_GRP_CURRENT" = 0 then 9999
else (case when (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999
else (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wn_grp_rating,
round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1) sd_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1) sn_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1) wd_grp_volt_reg,
round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) wn_grp_volt_reg,
greatest ( round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1),
round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) ) worst_volt_reg,
dinis_real_power_loss "DINIS Power Loss (kW)", daterun loadflow_date, errornotes loadflow_notes,
project_no, project_type, pow,comments, last_update_time comments_date,
grp_sd10, grp_sn10, grp_wd10, grp_wn10
from
(--------------------------------------------------------------------------------
-- DATA
-- Tables Structure
-- t1 -
-- t2 -
-- t3 - = t6 -
-- t4 -
-- t5 -
-- t7 - = DATA
-- t8 -
-- t9 -
-- t10 -
-- t11 -
select distinct t6.groupname, t7.sub, t7.hub, t6.line_gor, t6.linecode, t6.from_, t6.to_, t6."LENGTH (m)",
dinis_grp_current, t6."% DINIS_GRP_CURRENT", t6.dinis_segment_current,
t6.grp_sd, t6.grp_sn, t6.grp_wd, t6.grp_wn,t6.grp_sd10, t6.grp_sn10, t6.grp_wd10, t6.grp_wn10,
t6.seg_sd, t6.seg_sn, t6.seg_wd, t6.seg_wn,
t6.sd_cont_rat, t6.sn_cont_rat, t6.wd_cont_rat, t6.wn_cont_rat,
t6.sdlf, t6.wdlf, t6.scrf, t6.wcrf, t7.tmu, t8.errornotes, t8.daterun, t9.dinis_grp_volt_reg,
t10.dinis_real_power_loss, t11.project_no, t11.project_type, t11.pow, t11.comments, t11.last_update_time
from -- Tables 6, 7, 8, 9 , 10 & 11
( -- Table 6, Join of Tables 1, 2, 3, 4 & 5
select distinct t1.groupname, t1.line_gor, t1.linecode, t1.userreference1 from_, t2.userreference1 to_, t1.nodetext3 project_s, t2.nodetext3 project_r, t1.length "LENGTH (m)",
t3.group_current dinis_grp_current,
(case when (t3.group_current is null or t3.group_current = 0) then null -- See notes in Table 3
else t1.current_in/t3.group_current*100 end)"% DINIS_GRP_CURRENT",
t1.current_in dinis_segment_current,
t4.grp_sd, t4.grp_sn, t4.grp_wd, t4.grp_wn, grp_sd10, grp_sn10, grp_wd10,grp_wn10,
(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_sd,0) end) seg_sd,
(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_sn,0) end) seg_sn,
(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_wd,0) end) seg_wd,
(case when (t3.group_current is null or t3.group_current = 0) then null
else round(t1.current_in/t3.group_current*t4.grp_wn,0) end) seg_wn,
t5.sd_cont_rat, t5.sn_cont_rat, t5.wd_cont_rat, t5.wn_cont_rat,
t4.sdlf sdlf, t4.wdlf wdlf,
(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- OH cable Cyclis Rating Factor = 1
(select crf
from dlf_to_crf
where min_dlf <= t4.sdlf and max_dlf > t4.sdlf)
else 1 end) scrf,
(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- UG cable Cyclic Rating Factor Look Up
(select crf -- from DLF_TO_CRT table
from dlf_to_crf
where min_dlf <= t4.wdlf and max_dlf > t4.wdlf) -- DLF determined in Table 4
else 1 end) wcrf
from
( -- Table 1
-- Line Results converted to kVar, kW and Amps
-- Line Results joined to Node Results on "Sendingnodegor"
select distinct
lr.groupname, lr.gor line_gor, round(lr.userspecifiedlinelength,0) length, lr.linecode,
round((lr.reactivepowerin*power(10,6))/1000,2) Reactive_Power_in_kVar,
round((lr.reactivepowerout*power(10,6))/1000,2) Reactive_Power_out_kVar,
round((lr.reactivepowerloss*power(10,6))/1000,2) Reactive_Power_Loss_kVar,
round((lr.realpowerin*power(10,6))/1000,2)Real_Power_in_kW,
round((lr.realpowerout*power(10,6))/1000,2) Real_Power_Out_kW,
round((lr.realpowerloss*power(10,6))/1000,2) Real_Power_Loss_kW,
round (greatest (lr.currentin, lr.currentout) * 5248.64, 2) current_in, -- Selects highest current as "Current In"
round (least (lr.currentin, lr.currentout) * 5248.64, 2) current_out, -- Selects lowest current as "Current Out"
lr.sendingnodegor,nr.userreference1, nr.nodetext3,
lr.receivingnodegor
from autoloadflow_lineresults lr,
autoloadflow_noderesults nr
where lr.sendingnodegor = nr.gor (+) and
not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites
lr.groupname like '%' ) t1, -- Table 1
( -- Table 2
-- Line Results GOR and Receiving Node Gor only
-- Line Results joined to Node Results on "Receivingnodegor"
select distinct
lr.groupname, lr.gor line_gor,
lr.receivingnodegor, nr.userreference1, nr.nodetext3
from autoloadflow_lineresults lr,
autoloadflow_noderesults nr
where lr.receivingnodegor = nr.gor (+) and
not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites
lr.groupname like '%' ) t2, -- Table 2
( -- Table 3
-- Group Current in DINIS Loadflow
-- For radial feeders, selects the highest current found in the loadflow Line Results
-- For group/mesh feeders, selects the sum of Group Load values from the AUTOLOADFLOW_GROUPING table
-- Note, the results rely the AUTOLOADFLOW_GROUPING table being up-to-date
select groupname, max (round (greatest (lr.currentin, lr.currentout) * 5248.64, 2)) group_current
from distplan.autoloadflow_lineresults lr
where not groupname like '___G__' and not groupname like '______G__%' -- radial feeders -- Excluding Groups
and not groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Excluding parallel cables
'BDBBRW8', 'VSP1')
group by groupname -- If a feeder has "0" load, the Line Results currents will be Null ie. Tie feeders
union
select groupname, sum(load) load -- Sums multiple loads for groups/mesh feeders
from autoloadflow_grouping
where groupname like '___G__' or groupname like '______G__%' -- Groups/Mesh feeders
or groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Parallel cables
'BDBBRW8', 'VSP1')
group by groupname
order by groupname ) t3, -- Table 3
( --Table 4
-- Groupname GRP and SD, SN, WD, WN Group Forecast Loads, Group Summer DLF, Group Wiinter DLF
-- from the Transaction Viewer for the most recent transactions before taday excluding GROWTH RATE transactions
select t4b.grp,
sum(t4a.grp_sd) grp_sd,
sum(t4a.grp_sn) grp_sn,
sum(t4a.grp_wd) grp_wd,
sum(t4a.grp_wn) grp_wn,
sum(t4a.grp_sd10) grp_sd10,
sum(t4a.grp_sn10) grp_sn10,
sum(t4a.grp_wd10) grp_wd10,
sum(t4a.grp_wn10) grp_wn10,
round( avg(t4b.SDLF) ,4) SDLF,
round( avg(t4b.WDLF) ,4) WDLF
from
( -- Table 4a, All Forecast Events from the Forecast, Partitioned and Sorted with most recent RN = 1
select lmp,
event,
dt,
round(load_50poe_sd,0) grp_sd, -- Rounds loads
round(load_50poe_sn,0) grp_sn,
round(load_50poe_wd,0) grp_wd,
round(load_50poe_wn,0) grp_wn,
round(load_10poe_sd,0) grp_sd10, -- Rounds loads
round(load_10poe_sn,0) grp_sn10,
round(load_10poe_wd,0) grp_wd10,
round(load_10poe_wn,0) grp_wn10,
row_number() over (partition by lmp order by dt desc, load_50poe_sd) rn
-- Partition data by LMP, Order by DT descending and 50POE
-- Insert row numbers for each partition
from forecast
where dt < sysdate and -- Where the transaction date is before today's date
not event = 'GROWTH RATE' and -- Ignore Growth Rate Transactions in the Forecast
not event = 'THERMAL RATING' and -- Ignore Thermal rating Transactions in the Forecast
committed = 'YES' ) t4a, -- Table 4a -- Only committed transactions
( -- Tabale 4b, LMP, GRP, Summer DLF and Winter DLF from Feeder table
select lmp,
grp,
daily_load_factor_summer sdlf,
daily_load_factor_winter wdlf
from feeders_new ) t4b -- Table 4b
where t4a.lmp = t4b.lmp and
t4a.rn = 1
group by t4b.grp ) t4, -- Table 4
( -- Table 5
-- Continuous Cable Ratings
select linecode, round(sd,0) sd_cont_rat, round(sn,0) sn_cont_rat, round(wd,0) wd_cont_rat, round(wn,0) wn_cont_rat
from ratings_oh_cyclic
union
select linecode, summer sd_cont_rat, summer sn_cont_rat, winter wd_cont_rat, winter wn_cont_rat
from ratings_ug_continuous
order by linecode ) t5 -- Table 5
where t1.line_gor = t2.line_gor and -- Join Tables 1, 2, 3, 4 & 5
t1.groupname = t3.groupname (+) and
t1.groupname = t4.grp (+) and
t1.linecode = t5.linecode
order by groupname
) t6, -- Table 6, (Tables 1, 2, 3, 4 & 5 )
( -- Table 7
-- Grp, Sub, Hub and TMU Data
select t7a.grp, t7b.sub_id, t7c.alpha sub, t7b.hub, t7a.tmu
from
(select distinct grp, avg(target_maximum_utilisation)*100 tmu
from feeders_new
where grp in (select groupname from autoloadflow_lineresults) group by grp) t7a, -- Table 7a
(select distinct grp, hub, sub_id
from feeders_new where grp in (select groupname from autoloadflow_lineresults) ) t7b, -- Table 7b
(select id, alpha from substations ) t7c -- Table 7c
where t7a.grp = t7b.grp and
t7b.sub_id = t7c.id ) t7, -- Table 7
( -- Table 8
-- DINIS Loadflow Summary Results
select groupname, errornotes, daterun
from autoloadflow_summaryresults ) t8, -- Table 8
( -- Table 9
-- DINIS Loadflow Voltage Regulation
select groupname, ( max(voltagemagnitude)-min(voltagemagnitude) ) *100 dinis_grp_volt_reg
from autoloadflow_noderesults
group by groupname ) t9, -- Table 9
( -- Table 10
-- DINIS Group Power Loss
select groupname, round (sum((realpowerloss*100*power(10,6))/1000),2) dinis_real_power_loss
from autoloadflow_lineresults
group by groupname ) t10, -- Table 10
( -- Table 11
-- Autoloadflow_Limits_Projects
select project_no, group_name,
(case when (rating_limit = 'YES' and voltage_limit is null) then 'RATING'
when (rating_limit is null and voltage_limit = 'YES') then 'VOLTAGE'
when (rating_limit = 'YES' and voltage_limit = 'YES') then 'RATING and VOLTAGE'
when (rating_limit is null and voltage_limit is null) then null
else 'XXXXXXXX' end) project_type,
from_, to_, dinis_line_gor, comments, pow, last_update_time, date_completed
from autoloadflow_limits_projects
order by group_name ) t11 -- Table 11
where t6.groupname = t7.grp and -- Join Tables 6, 7, 8, 9, 10 & 11
t6.groupname = t8.groupname and
t6.groupname = t9.groupname and
t6.groupname = t10.groupname and
t6.line_gor = t11.dinis_line_gor (+)
)
)
where groupname like '%' and
hub like '%'
order by groupname, worst_utilisation desc