AND TL_TRC.EFFDT = ( SELECT MAX(TL_TRC_ED.EFFDT) 
                           FROM PS_TL_TRC_TBL TL_TRC_ED 
                           WHERE TL_TRC.TRC = TL_TRC_ED.TRC 
                                 AND TL_TRC_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) 

CREATE TABLE TMP_XXX_1 as
SELECT TL_TRC_ED.TRC TRC1, MAX(TL_TRC_ED.EFFDT) MAX1
FROM PS_TL_TRC_TBL TL_TRC_ED 
INNER JOIN PS_TL_PAYABLE_TIME TL_PAYABLE_TIME ON TL_TRC_ED.TRC = TL_PAYABLE_TIME.TRC
                                                 AND TL_TRC_ED.EFFDT <= TL_PAYABLE_TIME.DUR
GROUP BY TL_TRC_ED.TRC;

TRUNCATE TABLE TMP_XXX_1;
DROP TABLE TMP_XXX_1; 

drop table test_0105;

drop table test_0106;

create table test_0105  (col_1  number, col_2 number);

create table test_0106 (col_1 number,col_3 number);

insert into test_0105 select level,mod(level,1000) from dual connect by level <=1000000;

insert into test_0106 select level, mod(level,100) from dual connect by level <= 1000000;

create index test_0106_idx on test_0106(col_3,col_1);

select count(*)
 from   test_0105 t5
 where  t5.col_1 <= (select max(col_1)
                     from   test_0106 t6
                     where  t6.col_3 = t5.col_2);

Table Definitions
CREATE TABLE [dbo].[Test_1](
	[col_1] [numeric](18, 0) NULL,
	[col_2] [numeric](18, 0) NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[Test_2](
	[col_1] [numeric](18, 0) NULL,
	[col_3] [numeric](18, 0) NULL
) ON [PRIMARY];

CREATE NONCLUSTERED INDEX [test_2_idx] ON [dbo].[Test_2] 
(
	[col_3] ASC,
	[col_1] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];

create table numbers (num numeric(18,0));

insert into numbers(num)
select rank() over (order by object_id,column_id) 
from sys.all_columns;


select (n1.num-1)*1000+(n2.num-1) totnum
from   numbers n1, numbers n2
where  n1.num<= 1000
and    n2.num<= 1000
order by totnum;

delete test_1;

delete test_2;

insert into test_1 (col_1,col_2)
select (n1.num-1)*1000+(n2.num-1),n1.num-1
from   numbers n1, numbers n2
where  n1.num<= 1000
and    n2.num<= 1000;

insert into test_2 (col_1,col_3)
select (n1.num-1)*1000+(n2.num-1),n2.num-1
from   numbers n1, numbers n2
where  n1.num<= 1000
and    n2.num<= 1000;

select count(*)
from   test_1 t1
where  t1.col_1 <= (select max(col_1)
                    from   test_2 t2
                    where  t2.col_3 = t1.col_2);

create table tmp_01 as
select accnt_code, crnc_rate
from accounts
inner join crnc_rates on accnt_crnc=crnc_crnc
							  and crnc_date=(select max(crnc_date) from crnc_rates
									    where crnc_rates=accnt_crnc and crnc_date<=accnt_open_dt)

create table tmp_01 as
select accnt_code, crnc_rate
from accounts
inner join crnc_rates on accnt_crnc=crnc_crnc
                         and crnc_date=(select max(crnc_date) from crnc_rates
                                        where crnc_rates=accnt_crnc and crnc_date<=accnt_open_dt)

create table test_0109 (col_1 number, col_2 number);

create table test_0110 (col_1 number, col_2 number);

insert into test_0109 select level,null from dual connect by level <=10000;

insert into test_0110 select level, mod(level,100) from dual connect by level <= 10000;

create index test_0110_idx on test_0110(col_1,col_2);

declare
  v_iter   pls_integer := 10;
  v_time   pls_integer; 
begin
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    update test_0109 t9
    set    col_2 = (select col_2 
                    from   test_0110 t0
                    where  t9.col_1 = t0.col_1);
  end loop;
  
  dbms_output.put_line('Update '||to_char(dbms_utility.get_time-v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    merge into test_0109 t9
    using test_0110 t0
    on (t0.col_1 = t9.col_1)
    when matched then update set t9.col_2 = t0.col_2;
  end loop;
  
  dbms_output.put_line('Merge '||to_char(dbms_utility.get_time-v_time));  
end;
/

Results
set serveroutput on
Update 753
Merge 727

Update 525
Merge 551

Update 365
Merge 297

Update 461
Merge 652

Update 3323
Merge 2750

Update 3002
Merge 3365

drop table gt_test_0105;

drop table gt_test_0106;

create table gt_test_0105  (col_1  number, col_2 number, date_1 date);

create table gt_test_0106 (col_1 number,col_3 number, date_2 date);

insert into gt_test_0105 select level,mod(level,10), sysdate date_1 from dual connect by level <=1000000;

insert into gt_test_0106 select level, mod(level,10), sysdate-(1+ABS(MOD(dbms_random.random,1000))) date_2 from dual connect by level <= 100000;

create index gt_test_0106_idx on gt_test_0106(col_3 , date_2 );                     




select count(*) 
from gt_test_0105 t5 
inner join gt_test_0106 t6 on t6.col_3=t5.col_2
                              and date_2=(select max(t7.date_2) 
                                          from gt_test_0106 t7
                                          where t7.col_3= t5.col_2 
                                                and t7.date_2<=t5.date_1)

 

drop table gt_test_0109

drop table gt_test_0110

create table gt_test_0109 (col_1 number, col_2 number);

create table gt_test_0110 (col_1 number, col_2 number);

insert into gt_test_0109 select level,null from dual connect by level <=100000;

insert into gt_test_0110 select level, mod(level,100) from dual connect by level <= 100000;

create index test_0110_idx on gt_test_0110(col_1,col_2);

declare
  v_iter   pls_integer := 1;
  v_time   pls_integer; 
begin
  v_time := dbms_utility.get_time;
  

  for i in 1..v_iter loop
    update gt_test_0109 t9
    set    col_2 = (select col_2 
                    from   gt_test_0110 t0
                    where  t9.col_1 = t0.col_1)
    --where col_2 is null
    ;
  end loop;
  
  dbms_output.put_line('Update '||to_char(dbms_utility.get_time-v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    merge into gt_test_0109 t9
    using gt_test_0110 t0
    on (t0.col_1 = t9.col_1 /*and  t9.col_2 is null*/)
    when matched then update set t9.col_2 = t0.col_2;
  end loop;
  
  dbms_output.put_line('Merge '||to_char(dbms_utility.get_time-v_time));  
end;

Update 1585
Merge 160

      AND TL_TRC.EFFDT = ( SELECT MAX(TL_TRC_ED.EFFDT) 
                           FROM PS_TL_TRC_TBL TL_TRC_ED 
                           WHERE TL_TRC.TRC = TL_TRC_ED.TRC 
                                 AND TL_TRC_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) 

Merge First
Merge 1239
Update 422

Merge 1244
Update 433

Update First
Update 1793
Merge 319

Update 1399
Merge 325

Oracle Code

create table test_0107 (col_1  number, col_2  number,col_3  date);

create table test_0108 (col_2 number, col_3 date, col_4 number);


insert into test_0107 select level, mod(level,10)+1,sysdate from dual connect by level <=4000;

insert into test_0108 select l1.lvl,trunc(sysdate)+10-l2.lvl, l1.lvl + l2.lvl 
                      from (select level lvl from dual connect by level <= 10) l1
                          ,(select level lvl from dual connect by level <= 75000) l2;

create index test_0108_idx2 on test_0108(col_3 desc,col_2 ,col_4);

create index test_0108_idx  on test_0108(col_2,col_3 desc,col_4);

begin
  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'TEST_0107'
                               ,method_opt => 'FOR ALL COLUMNS'
                               ,cascade => true);

  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'TEST_0108'
                               ,method_opt => 'FOR ALL COLUMNS'
                               ,cascade => true);
end;
/
                   
select count(col_4)
from   test_0107 t7
      ,test_0108 t8
where  t7.col_2 = t8.col_2
and    t8.col_3 = (select max(t81.col_3)
                   from   test_0108 t81
                   where  t7.col_2 = t81.col_2
                   and    t8.col_2 = t81.col_2
                   and    t81.col_3 <= t7.col_3
                   );

select count(col_4)
from   test_0107 t7
      ,test_0108 t8
where  t7.col_2 = t8.col_2
and    t8.col_3 = (select max(t81.col_3)
                   from   test_0108 t81
                   where  t7.col_2 = t81.col_2
                   and    t8.col_2 = t81.col_2
                   and    t81.col_3 <= t7.col_3
                   );

where  t7.col_2 = t81.col_2
       and    t8.col_2 = t81.col_2

select count(col_4)
from   test_0107 t7
      ,test_0108 t8
where  t7.col_2 = t8.col_2
and    t8.col_3 = (select max(t81.col_3)
                   from   test_0108 t81
                   where  t7.col_2 = t81.col_2
                          and    t81.col_3 <= t7.col_3
                   );

select count(1) 
from test_0108 t8
where t8.col_3=(select max(t81.col_3)
                   from   test_0108 t81
                   where  t8.col_2 = t81.col_2
                   and    t81.col_3 < t8.col_3
                   );

select count(col_4)
from (
select t7.col_1
      ,t7.col_2
      ,(select distinct first_value(t8.col_4) over (order by t8.col_3 desc)
        from   test_0108 t8
        where  t8.col_2 = t7.col_2
        and    t8.col_3 <= t7.col_3) col_4
from test_0107 t7);   

This query here, which returns the same set of data runs on a 4,000,000 row data set in 8.5 seconds

select count(col_4)
from (
select t7.col_1
      ,t7.col_2
      ,(select distinct first_value(t8.col_4) over (order by t8.col_3 desc)
        from   test_0108 t8
        where  t8.col_2 = t7.col_2
        and    t8.col_3 <= t7.col_3) col_4
from test_0107 t7);   

insert into test_0107 select level, mod(level,10)+1,sysdate from dual connect by level <=4000;

select count(col_4)
from (
select t7.col_1
      ,t7.col_2
      ,(select t8.col_4 from test_0108 t8
        where t8.col_2 = t7.col_2
              and t8.col_3= (select max(t81.col_3) 
                             from test_0108 t81 
                             where  t81.col_2 = t7.col_2
                                    and t81.col_3<=t7.col_3)) col_4
from test_0107 t7); 

insert into gt_test_0111 select to_number(reverse(to_char(level,'000000'))), mod(level,100) from dual connect by level < 100000;

insert into gt_test_0109 select level,case when mod(level,100)=1 then null else 1 end from dual connect by level <100000;

declare
  v_iter   pls_integer := 1;
  v_time   pls_integer; 
begin
  -- Pre cache table
  for i in 1..v_iter loop
    update gt_test_0109 t9
    set    col_2 = (select col_2 
                    from   gt_test_0110 t0
                    where  t9.col_1 = t0.col_1)
    ;
  end loop;
  
  rollback;


  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    merge into gt_test_0109 t9
    using (select * from gt_test_0110 where col_2 is null) t0
    on (t0.col_1 = t9.col_1)
    when matched then update set t9.col_2 = t0.col_2;
  end loop;
  
  dbms_output.put_line('Merge '||to_char(dbms_utility.get_time-v_time));
  
  rollback;
  
  v_time := dbms_utility.get_time;

  for i in 1..v_iter loop
    update gt_test_0109 t9
    set    col_2 = (select col_2 
                    from   gt_test_0110 t0
                    where  t9.col_1 = t0.col_1)
    where col_2 is null;
  end loop;
 
  dbms_output.put_line('Update '||to_char(dbms_utility.get_time-v_time));

end;
/

Merge 77
Update 9

Merge 53
Update 11

Merge 67
Update 15

    merge into gt_test_0109 t9
    using gt_test_0110 t0
    on (t0.col_1 = t9.col_1 AND t0.col_2 is null)
    when matched then update set t9.col_2 = t0.col_2;

merge into gt_test_0109 t9
    using gt_test_0110 t0
    on (t0.col_1 = t9.col_1 AND t9.col_2 is null)
    when matched then update set t9.col_2 = t0.col_2;

SELECT  TL_PAYABLE_TIME.EMPLID 
      , TL_PAYABLE_TIME.EMPL_RCD 
      , TL_PAYABLE_TIME.DUR 
      , TL_PAYABLE_TIME.SEQ_NBR 
      , PERSONAL_DATA.NAME                       
      , JOB.BARG_UNIT                            
      , TL_PAYABLE_TIME.TRC 
-----------------------------------------------------------------------------------------------------------
--    , TL_TRC.DESCR AS OH_TRC_DESCR             
      ,( SELECT TL_TRC.DESCR
         FROM PS_TL_TRC_TBL TL_TRC
         WHERE     TL_TRC.TRC = TL_PAYABLE_TIME.TRC
               AND TL_TRC.EFFDT = ( SELECT MAX(TL_TRC_ED.EFFDT) 
                                    FROM PS_TL_TRC_TBL TL_TRC_ED 
                                    WHERE     TL_TRC_ED.TRC = TL_PAYABLE_TIME.TRC 
                                          AND TL_TRC_ED.EFFDT <= TL_PAYABLE_TIME.DUR )) AS OH_TRC_DESCR 
-----------------------------------------------------------------------------------------------------------
--    , TL_ERNCD.TL_ERNCD 
      ,( SELECT TL_ERNCD.TL_ERNCD
         FROM PS_OH_S_TL_ERNCD TL_ERNCD
         WHERE     TL_ERNCD.TRC = TL_PAYABLE_TIME.TRC
               AND TL_ERNCD.PAY_SYSTEM = TL_PAYABLE_TIME.PAY_SYSTEM 
               AND TL_ERNCD.EFFDT = ( SELECT MAX(TL_ERNCD_ED.EFFDT) 
                                      FROM PS_OH_S_TL_ERNCD TL_ERNCD_ED 
                                      WHERE     TL_ERNCD_ED.TRC = TL_PAYABLE_TIME.TRC
                                           AND  TL_ERNCD_ED.PAY_SYSTEM = TL_PAYABLE_TIME.PAY_SYSTEM
                                           AND TL_ERNCD_ED.EFFDT <= TL_PAYABLE_TIME.DUR )) AS TL_ERNCD
      , TL_PAYABLE_TIME.TL_QUANTITY 
-----------------------------------------------------------------------------------------------------------
--    , SCH_ASSIGN.SCHEDULE_ID                      
      ,( SELECT SCH_ASSIGN.SCHEDULE_ID
         FROM PS_OH_S_SCH_ASSIGN SCH_ASSIGN
         WHERE     SCH_ASSIGN.EMPLID = TL_PAYABLE_TIME.EMPLID
               AND SCH_ASSIGN.EMPL_RCD = TL_PAYABLE_TIME.EMPL_RCD
               AND SCH_ASSIGN.EFFDT = ( SELECT MAX(SCH_ASSIGN_ED.EFFDT) 
                                        FROM PS_OH_S_SCH_ASSIGN SCH_ASSIGN_ED 
                                        WHERE     SCH_ASSIGN_ED.EMPLID = TL_PAYABLE_TIME.EMPLID
                                              AND SCH_ASSIGN_ED.EMPL_RCD = TL_PAYABLE_TIME.EMPL_RCD
                                              AND SCH_ASSIGN_ED.EFFDT <= TL_PAYABLE_TIME.DUR 
                                              AND SCH_ASSIGN_ED.END_EFFDT >= TL_PAYABLE_TIME.DUR )) AS SCHEDULE_ID
-----------------------------------------------------------------------------------------------------------
--!!!!!      , SCH_CLND.SCHED_HRS                        !!!!  --------------------
      , NULL AS SCHED_HRS
      , TL_PAYABLE_TIME.PAYABLE_STATUS 
      , TL_PAYABLE_TIME.PAYROLL_REQ_NUM 
      , TL_PAYABLE_TIME.FROZEN_SW 
      , TL_PAYABLE_TIME.FROZEN_DATE 
      , TL_PAYABLE_TIME.PUBLISH_SW 
      , TL_PAYABLE_TIME.ACTUAL_PUB_DATE 
      , TL_PAYABLE_TIME.APPRV_PRCS_DTTM 
      , TL_PAYABLE_TIME.OPRID 
      , TL_PAYABLE_TIME.ORIG_SEQ_NBR 
      , TL_PAYABLE_TIME.OVERRIDE_RATE 
      , TL_PAYABLE_TIME.RECORD_ONLY_ADJ 
      , TL_PAYABLE_TIME.LBR_DIST_AMT 
      , TL_PAYABLE_TIME.EST_GROSS 
      , TL_PAYABLE_TIME.DILUTED_GROSS 
      , TL_PAYABLE_TIME.SOURCE_IND 
      , TL_PAYABLE_TIME.TASKGROUP 
      , TL_PAYABLE_TIME.BILLABLE_IND 
      , TL_PAYABLE_TIME.ACCT_CD 
      , TL_PAYABLE_TIME.ACCOUNT 
      , TL_PAYABLE_TIME.DEPTID_CF AS OH_CF_DEPTID 
      , TL_PAYABLE_TIME.PROJECT_ID 
      , TL_PAYABLE_TIME.FUND_CODE 
      , TL_PAYABLE_TIME.PROGRAM_CODE 
      , TL_PAYABLE_TIME.CLASS_FLD 
      , TL_PAYABLE_TIME.CHARTFIELD1 
      , TL_PAYABLE_TIME.CHARTFIELD2 
      , TL_PAYABLE_TIME.CHARTFIELD3 
      , TL_PAYABLE_TIME.BUDGET_REF 
      , JOB.COMPANY                            
      , TL_PAYABLE_TIME.BUSINESS_UNIT 
      , TL_PAYABLE_TIME.PRODUCT 
      , TL_PAYABLE_TIME.CUSTOMER 
      , TL_PAYABLE_TIME.BUSINESS_UNIT_PC 
      , TL_PAYABLE_TIME.BUSINESS_UNIT_PF 
      , TL_PAYABLE_TIME.SETID_ACTIVITY 
      , TL_PAYABLE_TIME.ACTIVITY_ID 
      , TL_PAYABLE_TIME.RESOURCE_TYPE 
      , TL_PAYABLE_TIME.SETID_RESOURCE 
      , TL_PAYABLE_TIME.RESOURCE_CATEGORY 
      , TL_PAYABLE_TIME.RESOURCE_SUB_CAT 
      , TL_PAYABLE_TIME.TASK 
      , TL_PAYABLE_TIME.USER_FIELD_1 
      , TL_PAYABLE_TIME.USER_FIELD_2 
      , TL_PAYABLE_TIME.USER_FIELD_3 
      , TL_PAYABLE_TIME.USER_FIELD_4 
      , TL_PAYABLE_TIME.USER_FIELD_5 
      , PERS_NID.NATIONAL_ID                   
      , JOB.SETID_DEPT AS SETID                
      , JOB.DEPTID                             
-----------------------------------------------------------------------------------------------------------
--      , DEPT.DESCR AS OH_DEPT_DESCR      
      ,( SELECT 
         FROM PS_DEPT_TBL DEPT
         WHERE     DEPT.DEPTID = JOB.DEPTID 
               AND DEPT.SETID = JOB.SETID_DEPT
               AND DEPT.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) 
                                  FROM PS_DEPT_TBL DEPT_ED 
                                  WHERE     DEPT_ED.DEPTID = JOB.DEPTID
                                        AND DEPT_ED.SETID = JOB.SETID_DEPT
                                        AND DEPT_ED.EFFDT <= JOB.EFFDT )) AS OH_DEPT_DESCR
      , JOB.POSITION_NBR                        
-----------------------------------------------------------------------------------------------------------
--    , POSITION_DATA.DESCR AS OH_POSITION_DESCR
      ,( SELECT POSITION_DATA.DESCR 
         FROM PS_S_POSITION_DATA POSITION_DATA
         WHERE     POSITION_DATA.POSITION_NBR = JOB.POSITION_NBR
               AND POSITION_DATA.EFFDT = (SELECT MAX(POSITION_DATA_ED.EFFDT) 
                                          FROM PS_S_POSITION_DATA POSITION_DATA_ED 
                                          WHERE     POSITION_DATA_ED.POSITION_NBR = JOB.POSITION_NBR
                                                AND POSITION_DATA_ED.EFFDT <= JOB.EFFDT )) AS OH_POSITION_DESCR
      , JOB.JOBCODE                         
-----------------------------------------------------------------------------------------------------------
--      , JOBCODE.DESCR AS OH_JOBCODE_DESCR 
      , ( SELECT JOBCODE.DESCR 
          FROM PS_JOBCODE_TBL JOBCODE
          WHERE     JOBCODE.JOBCODE = JOB.JOBCODE
                AND JOBCODE.SETID = JOB.SETID_JOBCODE
                AND JOBCODE.EFFDT = ( SELECT MAX(JOBCODE_ED.EFFDT) 
                                      FROM PS_JOBCODE_TBL JOBCODE_ED 
                                      WHERE     JOBCODE_ED.SETID = JOB.SETID_JOBCODE
                                            AND JOBCODE_ED.JOBCODE = JOB.JOBCODE
                                            AND JOBCODE_ED.EFFDT <= JOB.EFFDT )) AS OH_JOBCODE_DESCR
      , JOB.LOCATION                              
-----------------------------------------------------------------------------------------------------------
--    , LOCATION.DESCR AS OH_LOCATION_DESCR
      , ( SELECT LOCATION.DESCR
          FROM PS_LOCATION_TBL LOCATION
          WHERE     LOCATION.SETID = JOB.SETID_LOCATION 
                AND LOCATION.LOCATION = JOB.LOCATION
                AND LOCATION.EFFDT = ( SELECT MAX(LOCATION_ED.EFFDT) 
                                       FROM PS_LOCATION_TBL LOCATION_ED 
                                       WHERE     LOCATION_ED.SETID = JOB.SETID_LOCATION
                                             AND LOCATION_ED.LOCATION = JOB.LOCATION
                                             AND LOCATION_ED.EFFDT <= JOB.EFFDT )) AS OH_LOCATION_DESCR
      , JOB.UNION_CD 
-----------------------------------------------------------------------------------------------------------
--    , UNION_TBL.DESCR AS OH_UNION_DESCR
      ,( SELECT UNION_TBL.DESCR
         FROM PS_S_UNION_TBL UNION_TBL
         WHERE     UNION_TBL.UNION_CD = JOB.UNION_CD
               AND UNION_TBL.EFFDT = ( SELECT MAX(UNION_TBL_ED.EFFDT) 
                                       FROM PS_S_UNION_TBL UNION_TBL_ED 
                                       WHERE     UNION_TBL_ED.UNION_CD = JOB.UNION_CD
                                             AND UNION_TBL_ED.EFFDT <= JOB.EFFDT )) AS OH_UNION_DESCR

FROM  PS_TL_PAYABLE_TIME TL_PAYABLE_TIME 
INNER JOIN PS_PERSONAL_DATA PERSONAL_DATA   ON TL_PAYABLE_TIME.EMPLID = PERSONAL_DATA.EMPLID
INNER JOIN PS_PERS_NID PERS_NID             ON     TL_PAYABLE_TIME.EMPLID = PERS_NID.EMPLID 
                                               AND PERS_NID.COUNTRY = 'USA' 
                                               AND PERS_NID.NATIONAL_ID_TYPE = 'PR' 
INNER JOIN PS_JOB JOB                       ON      TL_PAYABLE_TIME.EMPLID = JOB.EMPLID 
                                                AND TL_PAYABLE_TIME.EMPL_RCD = JOB.EMPL_RCD 
                                                AND JOB.EFFDT = (  SELECT MAX(JOB_ED.EFFDT) 
                                                                   FROM PS_JOB JOB_ED 
                                                                   WHERE     JOB.EMPLID = JOB_ED.EMPLID 
                                                                         AND JOB.EMPL_RCD = JOB_ED.EMPL_RCD 
                                                                         AND JOB_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) 
WHERE TL_PAYABLE_TIME.DUR >= TO_DATE('2008-01-01' , 'YYYY-MM-DD') 

SELECT  TL_PAYABLE_TIME.EMPLID 
      , TL_PAYABLE_TIME.EMPL_RCD 
      , TL_PAYABLE_TIME.DUR 
      , TL_PAYABLE_TIME.SEQ_NBR 
      , PERSONAL_DATA.NAME                       
      , JOB.BARG_UNIT                            
      , TL_PAYABLE_TIME.TRC 
-----------------------------------------------------------------------------------------------------------
--    , TL_TRC.DESCR AS OH_TRC_DESCR             
      ,( SELECT FIRST_VALUE(TL_TRC.DESCR) OVER (ORDER BY TL_TRC.EFFDT DESC)
         FROM   PS_TL_TRC_TBL   TL_TRC
         WHERE  TL_TRC.TRC   =  TL_PAYABLE_TIME.TRC
         AND    TL_TRC.EFFDT <= TL_PAYABLE_TIME.DUR ) AS OH_TRC_DESCR 
-----------------------------------------------------------------------------------------------------------
--    , TL_ERNCD.TL_ERNCD 
      ,( SELECT FIRST_VALUE(TL_ERNCD.TL_ERNCD) OVER (ORDER BY TL_ERNCD.EFFDT DESC)
         FROM   PS_OH_S_TL_ERNCD TL_ERNCD
         WHERE  TL_ERNCD.TRC = TL_PAYABLE_TIME.TRC
         AND    TL_ERNCD.PAY_SYSTEM = TL_PAYABLE_TIME.PAY_SYSTEM 
         AND TL_ERNCD.EFFDT  <= TL_PAYABLE_TIME.DUR ) AS TL_ERNCD
      , TL_PAYABLE_TIME.TL_QUANTITY 

select count(col_4)
from (
select t7.col_1
      ,t7.col_2
      ,(select distinct first_value(t8.col_4) over (order by t8.col_3 desc)
        from   test_0108 t8
        where  t8.col_2 = t7.col_2
        and    t8.col_3 <= t7.col_3) col_4
from test_0107 t7);   


select count(col_4)
from (
select t7.col_1
      ,t7.col_2
      ,(select t8.col_4 from test_0108 t8
        where t8.col_2 = t7.col_2
              and t8.col_3= (select max(t81.col_3) 
                             from test_0108 t81 
                             where  t81.col_2 = t7.col_2
                                    and t81.col_3<=t7.col_3)) col_4
from test_0107 t7); 

INNER JOIN PS_JOB JOB                       ON      TL_PAYABLE_TIME.EMPLID = JOB.EMPLID 
                                                AND TL_PAYABLE_TIME.EMPL_RCD = JOB.EMPL_RCD 
                                                AND JOB.EFFDT = (  SELECT MAX(JOB_ED.EFFDT) 
                                                                   FROM PS_JOB JOB_ED 
                                                                   WHERE     JOB.EMPLID = JOB_ED.EMPLID 
                                                                         AND JOB.EMPL_RCD = JOB_ED.EMPL_RCD 
                                                                         AND JOB_ED.EFFDT <= TL_PAYABLE_TIME.DUR ) 

select *
     from (
     select t7.col_1
           ,t7.col_2
           ,(select first_value(t8.col_4) over (order by t8.col_3 desc)
             from   test_0108 t8
             where  t8.col_2 = t7.col_2
             and    t8.col_3  <= t7.col_3) col_4
     from test_0107 t7)
     where rownum > 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.15       1.12       1167      12148          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.15       1.13       1167      12148          0           0


select *
     from (
     select t7.col_1
           ,t7.col_2
           ,(select t8.col_4
             from   test_0108 t8
             where  t8.col_2 = t7.col_2
             and    t8.col_3 = (select max(t81.col_3)
                                from   test_0108 t81
                                where  t7.col_2 = t81.col_2
                                and    t81.col_3 <= t7.col_3)) col_4
     from test_0107 t7)
     where rownum > 1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.18       1.15          0      12148          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.18       1.16          0      12148          0           0