select a.borrid, b.mdlid, d.stmtid, f.auditmethod_name||'  '||to_char(d.stmtdt) || ' [ ' || d.stmtperiod || 'M ]' 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 item_name, a.value, a.item_id
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'

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=117 Card=52 Bytes=5928)

   1    0   HASH JOIN (Cost=117 Card=52 Bytes=5928)
   2    1     HASH JOIN (Cost=114 Card=70 Bytes=7210)
   3    2       HASH JOIN (Cost=91 Card=2485 Bytes=193830)
   4    3         TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 Card=12 Bytes =48)

   5    3         HASH JOIN (Cost=88 Card=2485 Bytes=183890)
   6    5           TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=11 Card=2011 Bytes=62341)

   7    5           HASH JOIN (Cost=72 Card=14947 Bytes=642721)
   8    7             TABLE ACCESS (FULL) OF 'COATREE' (Cost=2 Card=1 Bytes=22)

   9    7             TABLE ACCESS (FULL) OF 'COARATIOS'(Cost=68 Card =116094 Bytes=2437974)

  10    2       TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=13 Card=15911 Bytes=397775)

  11    1     TABLE ACCESS (FULL) OF 'AUDIT_METHOD' (Cost=2 Card=3 Bytes=33)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        932  consistent gets
       2869  physical reads
          0  redo size
    4383378  bytes sent via SQL*Net to client
      85573  bytes received via SQL*Net from client
       7736  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     116017  rows processed

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(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, substr(b.prmname, instr(b.prmname, '- ', 0, 1)+2) 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'

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=843 Card=322 Bytes=145624)

   1    0   UNION-ALL
   2    1     HASH JOIN (OUTER) (Cost=472 Card=52 Bytes=112684)
   3    2       VIEW (Cost=116 Card=52 Bytes=108628)
   4    3         HASH JOIN (Cost=116 Card=52 Bytes=5356)
   5    4           HASH JOIN (Cost=113 Card=52 Bytes=5148)
   6    5             HASH JOIN (Cost=110 Card=70 Bytes=6790)
   7    6               HASH JOIN (Cost=88 Card=2485 Bytes=183890)
   8    7                 TABLE ACCESS (FULL) OF 'COAITEMS' (Cost=11 Card=2011 Bytes=62341)

   9    7                 HASH JOIN (Cost=72 Card=14947 Bytes=642721)
  10    9                   TABLE ACCESS (FULL) OF 'COATREE' (Cost=2 Card=1 Bytes=22)

  11    9                   TABLE ACCESS (FULL) OF 'COARATIOS' (Cost=68 Card=116094 Bytes=2437974)

  12    6               TABLE ACCESS (FULL) OF 'STATEMENT' (Cost=13 Card=15911 Bytes=365953)

  13    5             TABLE ACCESS (FULL) OF 'AUDIT_METHOD' (Cost=2 Card=3 Bytes=6)

  14    4           TABLE ACCESS (FULL) OF 'MODEL' (Cost=2 Card=12 Bytes=48)

  15    2       VIEW (Cost=325 Card=55820 Bytes=4353960)
  16   15         SORT (UNIQUE) (Cost=325 Card=55820 Bytes=1116400)
  17   16           TABLE ACCESS (FULL) OF 'RAT_CALC_FINRATIO' (Cost=51 Card=55820 Bytes=1116400)

  18    1     HASH JOIN (Cost=371 Card=270 Bytes=32940)
  19   18       TABLE ACCESS (FULL) OF 'MDL_GRADING_DETAILS' (Cost=7 Card=2985 Bytes=41790)

  20   18       HASH JOIN (Cost=361 Card=3731 Bytes=402948)
  21   20         HASH JOIN (Cost=7 Card=12 Bytes=1020)
  22   21           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=9 Bytes=369)

  23   21           TABLE ACCESS (FULL) OF 'MDL_PARAMETER_TREE' (Cost=3 Card=1099 Bytes=48356)

  24   20         TABLE ACCESS (FULL) OF 'SUBJECTIVE_PRM_TRANS' (Cost=348 Card=378290 Bytes=8700670)





Statistics
----------------------------------------------------------
          0  recursive calls
          6  db block gets
       6477  consistent gets
      11752  physical reads
          0  redo size
    6312723  bytes sent via SQL*Net to client
     105780  bytes received via SQL*Net from client
       9573  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
     143577  rows processed

trim(lower(e.tr_name)) = 'key financial ratios'

trim(lower(c.prmname)) = 'frs'

trim(lower(e.tr_name)) = 'key financial ratios'

trim(lower(c.prmname)) = 'frs'