set autot off

create table master
(id number primary key)
/
insert into master
select rownum
  from all_objects
;
create table results
(masterid    number
,code  varchar2(5)
,Value varchar2(25)
)
/
alter table results add constraint results_fk foreign key (masterid) references master
/

insert into results
select id
     , column_value
     , column_value||' result'
  from master
     , table (sys.dbms_debug_vc2coll ('A', 'B', 'C', 'D', 'E'))
/

create or replace
function f (p_code in results.code%type
           ,p_id   in results.masterid%type
           )
   return results.value%type
is
   retval results.Value%type;
begin
   select Value
     into retval
     from results
    where masterid = p_id
      and code = p_code
   ;
   return retval;
end;
/
create table final_results
(a varchar2(10)
,b varchar2(10)
,c varchar2(10)
,d varchar2(10)
,e varchar2(10)
,final varchar2 (10)
);
insert into final_results values ('A result', 'B result', 'C result', 'D result', 'E result', 'Great');
insert into final_results values ('A result', 'B result', 'C result', 'D result', null, 'Good');
insert into final_results values ('A result', 'B result', 'C result', null, null, 'Fair');
insert into final_results values ('A result', 'B result', null, null, null, 'Neutral');
insert into final_results values ('A result', null, null, null, null, 'Poor');

begin
   dbms_stats.gather_table_stats (user, 'results');
   dbms_stats.gather_table_stats (user, 'master');
   dbms_stats.gather_table_stats (user, 'final_results');
end;
/
set echo on
select id
     , m.a
     , m.b
     , m.c
     , m.d
     , m.e
     , fres.final
  from (select id
             , f ('A', 1) a   --<---- Note these function calls
             , f ('B', 1) b   --<---- in this in-line view.
             , f ('C', 1) c   --<---- These are used to Pivot
             , f ('D', 1) d   --<---- the rows into columns
             , f ('E', 1) e   --<---- .
          from master
        ) m
  join final_results fres
    on (Nvl (m.a, 'x') = Nvl (fres.a, 'x')
        and nvl (m.b, 'x') = nvl (fres.b, 'x')
        and nvl (m.c, 'x') = nvl (fres.c, 'x')
        and nvl (m.d, 'x') = nvl (fres.d, 'x')
        and nvl (m.e, 'x') = nvl (fres.e, 'x')
       )
 where id = 1   --<------ Note this predicate

set echo off autot off timing off
Pause This is the Original Query
/
set autot on timing on
/
set autot off timing off
pause Above is the Autotrace of the Original Query. Next is the Modified Version
set echo on
select id
     , m.a
     , m.b
     , m.c
     , m.d
     , m.e
     , fres.final
  from (select id
             , rownum --<--- Added to materialize the in-line view
             , f ('A', 1) a   --<---- These function calls
             , f ('B', 1) b   --<---- stayed in place.
             , f ('C', 1) c   --<---- Note the difference in
             , f ('D', 1) d   --<---- the IO that's taking
             , f ('E', 1) e   --<---- place.
          from master
         where id = 1  --<----- This Predicated was added
        ) m
  join final_results fres
    on (Nvl (m.a, 'x') = Nvl (fres.a, 'x')
        and nvl (m.b, 'x') = nvl (fres.b, 'x')
        and nvl (m.c, 'x') = nvl (fres.c, 'x')
        and nvl (m.d, 'x') = nvl (fres.d, 'x')
        and nvl (m.e, 'x') = nvl (fres.e, 'x')
       )
 where id = 1   --<------ It's still here.

set echo off autot off timing off
Pause
/
set autot on timing on
/
set autot off timing off
pause Above is the Autotrace of the Rownum and the Predicate Move. Next the Pivot Query

set echo on

select m.mid
     , m.a
     , m.b
     , m.c
     , m.d
     , m.e
     , fres.final
  from (select masterid mid
             , max (decode (code, 'A', value)) a
             , max (decode (code, 'B', value)) b
             , max (decode (code, 'C', value)) c
             , max (decode (code, 'D', value)) d
             , max (decode (code, 'E', value)) e
          from (select masterid
                     , code
                     , value
                  from results
                 where masterid = 1
                ) m
         group by masterid
       ) m
  join final_results fres
    on (Nvl (m.a, 'x') = Nvl (fres.a, 'x')
        and nvl (m.b, 'x') = nvl (fres.b, 'x')
        and nvl (m.c, 'x') = nvl (fres.c, 'x')
        and nvl (m.d, 'x') = nvl (fres.d, 'x')
        and nvl (m.e, 'x') = nvl (fres.e, 'x')
       )

set echo off autot off timing off
Pause
/
set autot on timing on
/
set autot off timing off
pause Above is the Autotrace of the Pivot Query

drop table results
/
drop table master
/
drop table final_results
/
drop function f
/