Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table job_history( 
  employee_id   number
, start_date    date
, end_date      date
, job_id        varchar2( 100 )
, department_id number
) ;
begin
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (102,to_timestamp('13-JAN-93','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('24-JUL-98','DD-MON-RR HH24.MI.SSXFF'),'IT_PROG',60);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (101,to_timestamp('21-SEP-89','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('27-OCT-93','DD-MON-RR HH24.MI.SSXFF'),'AC_ACCOUNT',110);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (101,to_timestamp('28-OCT-93','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('15-MAR-97','DD-MON-RR HH24.MI.SSXFF'),'AC_MGR',110);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (201,to_timestamp('17-FEB-96','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('19-DEC-99','DD-MON-RR HH24.MI.SSXFF'),'MK_REP',20);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (114,to_timestamp('24-MAR-98','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('31-DEC-99','DD-MON-RR HH24.MI.SSXFF'),'ST_CLERK',50);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (122,to_timestamp('01-JAN-99','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('31-DEC-99','DD-MON-RR HH24.MI.SSXFF'),'ST_CLERK',50);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (200,to_timestamp('17-SEP-87','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('17-JUN-93','DD-MON-RR HH24.MI.SSXFF'),'AD_ASST',90);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (176,to_timestamp('24-MAR-98','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('31-DEC-98','DD-MON-RR HH24.MI.SSXFF'),'SA_REP',80);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (176,to_timestamp('01-JAN-99','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('31-DEC-99','DD-MON-RR HH24.MI.SSXFF'),'SA_MAN',80);
Insert into JOB_HISTORY (EMPLOYEE_ID,START_DATE,END_DATE,JOB_ID,DEPARTMENT_ID) values (200,to_timestamp('01-JUL-94','DD-MON-RR HH24.MI.SSXFF'),to_timestamp('31-DEC-98','DD-MON-RR HH24.MI.SSXFF'),'AC_ACCOUNT',90);
end;
/
1 rows affected

select * from job_history ;


EMPLOYEE_ID
START_DATE
END_DATE
JOB_ID
DEPARTMENT_ID


102
13-JAN-93
24-JUL-98
IT_PROG
60

101
21-SEP-89
27-OCT-93
AC_ACCOUNT
110

101
28-OCT-93
15-MAR-97
AC_MGR
110

201
17-FEB-96
19-DEC-99
MK_REP
20

114
24-MAR-98
31-DEC-99
ST_CLERK
50

122
01-JAN-99
31-DEC-99
ST_CLERK
50

200
17-SEP-87
17-JUN-93
AD_ASST
90

176
24-MAR-98
31-DEC-98
SA_REP
80

176
01-JAN-99
31-DEC-99
SA_MAN
80

200
01-JUL-94
31-DEC-98
AC_ACCOUNT
90


create table heap_empjobs (
  employee_id, job_id, dt
, primary key( employee_id, job_id, dt )
) 
as
select E.employee_id, J.job_id
, sysdate - ( row_number() over ( order by employee_id )  ) dt
from (
  select unique employee_id from job_history
) E cross join (
  select unique job_id from job_history
) J 
;
56 rows affected

create table iot_empjobs (
  employee_id, job_id, dt
, primary key( employee_id, job_id, dt )
) 
organization index
as
select E.employee_id, J.job_id
, sysdate - ( row_number() over ( order by employee_id )  ) dt
from (
  select unique employee_id from job_history
) E cross join (
  select unique job_id from job_history
) J 
;
56 rows affected

EMPLOYEE_ID	START_DATE	END_DATE	JOB_ID	DEPARTMENT_ID
102	13-JAN-93	24-JUL-98	IT_PROG	60
101	21-SEP-89	27-OCT-93	AC_ACCOUNT	110
101	28-OCT-93	15-MAR-97	AC_MGR	110
201	17-FEB-96	19-DEC-99	MK_REP	20
114	24-MAR-98	31-DEC-99	ST_CLERK	50
122	01-JAN-99	31-DEC-99	ST_CLERK	50
200	17-SEP-87	17-JUN-93	AD_ASST	90
176	24-MAR-98	31-DEC-98	SA_REP	80
176	01-JAN-99	31-DEC-99	SA_MAN	80
200	01-JUL-94	31-DEC-98	AC_ACCOUNT	90