select l.borrid, l.mdlid, l.year, l.user_id, l.prmname, l.value, to_char(m.score) as marks, l.roword
from
(
  select a.borrid, b.mdlid, to_number(to_char(d.stmtdt, 'yyyy'), '9999') as year, a.user_id, lpad(c.item_name, length(decode(c.item_name, 'DSCR (Mandatory)', 'DSCR', c.item_name))+((c.tr_lvl-1)*5), ' ') as prmname, to_char(a.value) as value, a.item_id as roword
  from coaratios a, model b, coaitems c, statement d, coatree e, audit_method f
  where
  a.coaid = b.coaid and
  a.coaid = c.coaid and
  a.item_id = c.item_id and
  a.tr_id = c.tr_id and
  a.coaid = d.coaid and
  a.stmtid = d.stmtid and
  a.borrid = d.borrid and
  a.user_id = d.user_id and
  a.coaid = e.coaid and
  a.tr_id = e.tr_id and
  d.audit_method_id = f.id and
  trim(lower(e.tr_name)) = 'key financial ratios'
) l,
(
  select  distinct x.borrid, x.mdlid, x.year, x.user_id, x.ratioid, x.score from rat_calc_finratio x
) m
where l.borrid = m.borrid (+) and
l.year = m.year (+) and
l.mdlid = m.mdlid (+) and
l.user_id = m.user_id (+) and
l.roword = m.ratioid (+)
union all
select a.borrid, a.mdlid, a.year, a.user_id, replace(b.prmname, 'FR - ','') as prmname, d.attribute as value, TO_CHAR(a.value) as Marks, a.prmid as roword
from subjective_prm_trans a, mdl_parameter_tree b, mdl_parameter_tree c, mdl_grading_details d
where a.mdlid = b.mdlid and
a.prmid = b.prmid and
b.mdlid = c.mdlid and
b.parentid = c.prmid and
a.mdlid = d.mdlid and
a.gddid = d.gddid and
trim(lower(c.prmname)) = 'frs'

Elapsed: 00:02:03.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=42283827442 Card=152
          6094534760 Bytes=3307046853865440)

   1    0   UNION-ALL
   2    1     MERGE JOIN (OUTER) (Cost=42283825854 Card=1526094533281
          Bytes=3307046853619930)

   3    2       SORT (JOIN) (Cost=42283792237 Card=1526094533281 Bytes
          =3188011480024010)

   4    3         VIEW (Cost=9381 Card=1526094533281 Bytes=31880114800
          24010)

   5    4           HASH JOIN (Cost=9381 Card=1526094533281 Bytes=2746
          97015990580)

   6    5             INDEX (FULL SCAN) OF 'IDX_COATREE_2' (NON-UNIQUE
          ) (Cost=1 Card=2 Bytes=46)

   7    5             HASH JOIN (Cost=1998 Card=106964620 Bytes=167934
          45340)

   8    7               NESTED LOOPS (Cost=1966 Card=614 Bytes=34998)
   9    8                 HASH JOIN (Cost=1966 Card=1023 Bytes=55242)
  10    9                   TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=1
          82 Card=256850 Bytes=6164400)

  11    9                   HASH JOIN (Cost=836 Card=700849 Bytes=2102
          5470)

  12   11                     TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 C
          ard=28 Bytes=168)

  13   11                     TABLE ACCESS (FULL) OF 'COARATIOS' (Cost
          =813 Card=1401698 Bytes=33640752)

  14    8                 INDEX (RANGE SCAN) OF 'IDX_AUDIT_METHOD_ID'
          (NON-UNIQUE)

  15    7               TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=29 Car
          d=5774 Bytes=577400)

  16    2       SORT (JOIN) (Cost=33617 Card=452673 Bytes=35308494)
  17   16         VIEW (Cost=7011 Card=452673 Bytes=35308494)
  18   17           SORT (UNIQUE) (Cost=7011 Card=452673 Bytes=9506133
          )

  19   18             TABLE ACCESS (FULL) OF 'RAT_CALC_FINRATIO' (Cost
          =412 Card=452673 Bytes=9506133)

  20    1     HASH JOIN (Cost=1588 Card=1479 Bytes=245514)
  21   20       TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Ca
          rd=27 Bytes=945)

  22   20       HASH JOIN (Cost=1581 Card=59234 Bytes=7759654)
  23   22         TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3
          Card=876 Bytes=42048)

  24   22         HASH JOIN (Cost=1557 Card=71216 Bytes=5910928)
  25   24           TABLE ACCESS (FULL) OF 'MDL_GRADING_DETAILS' (Cost
          =4 Card=2204 Bytes=130036)

  26   24           TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cos
          t=1151 Card=1357360 Bytes=32576640)





Statistics
----------------------------------------------------------
          0  recursive calls
        176  db block gets
      42674  consistent gets
      85740  physical reads
          0  redo size
   92877819  bytes sent via SQL*Net to client
    1196639  bytes received via SQL*Net from client
     108742  SQL*Net roundtrips to/from client
          0  sorts (memory)
          3  sorts (disk)
    1631109  rows processed