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_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

( -- 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

(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,

The first step would be to upgrade to a version of the product supported during the current millennium. That would be 10.2.0.4 or above.

Your version of Oracle is so old I, to be honest, I haven't seen a copy in 10 years. But of equal importance is that no one is going to help you
tune this unformatted verbose disaster when you didn't include a version number (no 8i is not a version number), any DDL for tables or indexes,
or what I would call the bare minimum ... an explain plan.

Upgrade to something real, run an explain plan, then come back and perhaps we can help you.

In general, when posting code to the forum, please use a { code } tag (without spaces) before and after the code.

That is a long SQL statement. Splitting the SQL statement up into multiple views likely will not help performance, but could hurt performance.

A couple comments about the SQL statement:
* "SELECT DISTINCT" repeated in many places, including in an inline view using a GROUP BY clause. Are all of the DISTINCT clauses necessary?

* ORDER BY is frequently used in the inline views: "order by group_name" in the T11 inline view, "order by groupname" in the T6 inline view, "order by groupname" in the T3 inline view, "order by linecode" in the T5 inline view. Remove those ORDER BY clauses.

* "UNION" is used in T3 inline view - is it possible to replace that with a "UNION ALL"?

* IN clauses may be inefficient in Oracle 8i, consider switching to an EXISTS clause or transforming the IN clause to an inline view.

* Odd LIKE syntax in several places: where "groupname like '%' and hub like '%'", "lr.groupname like '%'" in T1 inline view, "lr.groupname like '%'" in T2 inline view.

* The table autoloadflow_lineresults is accessed many times - is that a large table? Is it possible to consolidate the queries accessing that table?

* The WHERE clauses seem to limit the usefulness of any indexes which may exist.

* It appears that you want the results from the T6 inline view (which join the T1, T2, T3, T4, and T5 inline views) to drive into the T7, T8, T9, T10, and T11 inline views - does the execution plan show that is happening?

Simply removing the order by and like clauses not required and removing the distinct selection from the select statment where tables 6, 7, 8, 9, 10 & 11 are joined turned this from a 6min query to a 1min query.

Simply removing the order by and like clauses not required and removing the distinct selection from the select statment where tables 6, 7, 8, 9, 10 & 11 are joined turned this from a 6min query to a 1min query.

Thanks for the update regarding the performance improvement.

Please also take a look at the other suggestions I provided to you. I would start with converting the IN clause(s) to either an EXISTS clause or to a join.