SELECT field1,
      field2,
      field3,
      t2_1.col1,
      t2_2.col2
 from  table1
      ,table2 t2_1
      ,table2 t2_2
 where field4='A'
 and   t2_1.col_2 = field1
 and   t2_2.col_2 = field2;
 

create table test_119 (col_1 number, col_2 number);

create table test_120 (col_3 number, col_4 number);

insert into test_119 select level, mod(level,10)+1 from dual connect by level <= 10000;

insert into test_120 select level,level*(-1) from dual connect by level <= 10;

create or replace function test_120_fn (p_in  number) return number as
  v_return number;
begin
  select col_4
  into   v_return
  from   test_120
  where  col_3 = p_in;
  
  return v_return;
end;;
/

declare
  v_tim   pls_integer;
  v_iter  pls_integer := 100;
  
begin

  v_tim := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    for rec in (select col_1
                      ,col_2
                      ,col_4
                from   test_119
                      ,test_120
                where  col_3 = col_2) loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Test 1 '||to_char(dbms_utility.get_time - v_tim));

  v_tim := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    for rec in (select col_1
                      ,col_2
                      ,test_120_fn(col_2)
                from   test_119) loop
      null;
    end loop;
  end loop;
  
  dbms_output.put_line('Test 2 '||to_char(dbms_utility.get_time - v_tim));
end;
/

Test 1 239
Test 2 10158

Test 1 321
Test 2 19386