CREATE TABLE dim_comm COMPRESS NOLOGGING AS 
SELECT empno, comm 
  FROM scott.emp 
 WHERE comm IS NOT NULL 
 ORDER BY 1

CREATE TABLE dim_emp COMPRESS NOLOGGING AS 
SELECT e.empno, e.ename, e.job 
     , e.mgr, m.ename as mgr_name 
     , e.hiredate, e.sal, e.deptno 
  FROM scott.emp e 
  LEFT OUTER 
  JOIN scott.emp m 
    ON e.empno = m.empno 
 ORDER BY 1

ALTER TABLE dim_emp 
  ADD CONSTRAINT pk_emp PRIMARY KEY (empno)

ALTER TABLE dim_comm 
  ADD CONSTRAINT pk_comm PRIMARY KEY (empno)

ALTER TABLE dim_comm 
  ADD CONSTRAINT fk_comm_emp FOREIGN KEY 
   (empno) REFERENCES dim_emp(empno)