CREATE TABLE dim_comm COMPRESS NOLOGGING AS
SELECT empno, comm
FROM scott.emp
WHERE comm IS NOT NULL
ORDER BY 1
Table created.
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
Table created.
ALTER TABLE dim_emp
ADD CONSTRAINT pk_emp PRIMARY KEY (empno)
Table altered.
ALTER TABLE dim_comm
ADD CONSTRAINT pk_comm PRIMARY KEY (empno)
Table altered.
ALTER TABLE dim_comm
ADD CONSTRAINT fk_comm_emp FOREIGN KEY
(empno) REFERENCES dim_emp(empno)
Table altered.