-- drop table
DROP TABLE emp PURGE;
DROP TABLE dept PURGE;

-- create emp table 
CREATE TABLE emp AS
SELECT -- 1. EMPNO(Unique 컬럼)
       LEVEL empno,
       -- 2. EMPNO(VARCHAR로 변형)
       TO_CHAR(LEVEL) empno_varchar,
       -- 3. JOB(천만건 데이터를 20개 그룹으로 분류)
       CHR(65 + CEIL(LEVEL / 500000) - 1) job,
       -- 4. HIREDATE(10개씩 증가하여 날짜 데이터 만듬) MAX(2738-11-26)
       TO_DATE('00010101', 'YYYYMMDD') + CEIL(LEVEL / 10) - 1 hiredate,
       -- 5. DEPTNO
       -- 자리수 길이를 한 그룹으로 하여 데이터 생성, 분포도 차이나도록) MAX(80)
       LENGTH(LEVEL) * 10 deptno
FROM   DUAL
CONNECT BY LEVEL <= 10000000;

-- create dept table
CREATE TABLE dept AS
SELECT LEVEL * 10  deptno,
       'SALES_' || LEVEL dname,
       'ZONE_'  || LEVEL loc
FROM   DUAL
CONNECT BY LEVEL <= 9;

-- create index
CREATE UNIQUE INDEX EMP_U1 ON EMP (EMPNO) COMPUTE STATISTICS PARALLEL 8;

ALTER INDEX EMP_U1 NOPARALLEL;

CREATE INDEX EMP_N1 ON EMP (DEPTNO) COMPUTE STATISTICS PARALLEL 8;

ALTER INDEX EMP_N1 NOPARALLEL;

CREATE INDEX EMP_N2 ON EMP (HIREDATE) COMPUTE STATISTICS PARALLEL 8;

ALTER INDEX EMP_N2 NOPARALLEL;

CREATE INDEX EMP_N3 ON EMP (EMPNO_VARCHAR) COMPUTE STATISTICS PARALLEL 8;

ALTER INDEX EMP_N3 NOPARALLEL;

CREATE UNIQUE INDEX DEPT_U1 ON DEPT (DEPTNO);

-- gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP', CASCADE => TRUE, DEGREE => 8);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT', CASCADE => TRUE);