CREATE INDEX IDEPTNO ON BIG_EMP(DEPTNO);
CREATE INDEX IEMPNO ON BIG_EMP(EMPNO);
ALTERSESSION SET OPTIMIZER_MODE = RULE;
SET AUTOTRACE TRACE;
SELECT ENAME FROM BIG_EMP
WHERE DEPTNO = 20
AND EMPNO BETWEEN 100 AND 200
ORDER BY 1;
SELECT ENAME FROM BIG_EMP
WHERE DEPTNO >= 20 AND DEPTNO <= 30
AND EMPNO = 100
ORDER BY 1;
DROP INDEX IEMPNO;
CREATE INDEX IEMPNO ON BIG_EMP(EMPNO);
SELECT ENAME FROM BIG_EMP
WHERE DEPTNO = 20
AND EMPNO = 100
ORDER BY 1;
DROP INDEX IEMPNO;
DROP INDEX IDEPTNO;
CREATE INDEX IEMPNO ON BIG_EMP(EMPNO);
CREATE INDEX IDEPTNO ON BIG_EMP(DEPTNO);
SELECT ENAME FROM BIG_EMP
WHERE DEPTNO >= 20
AND EMPNO <= 100
ORDER BY 1;
DROP INDEX IEMPNO;
DROP INDEX IDEPTNO;
CREATE INDEX IENAME ON BIG_EMP(ENAME);
SELECT * FROM BIG_EMP
WHERE ENAME = 'MARTIN' OR SUBSTR(ENAME,1,1) = 'F';
DROP INDEX IENAME;
CREATE INDEX IDEPTNO ON EMP(DEPTNO);
CREATE INDEX IJOB ON EMP(JOB);
SELECT * FROM EMP WHERE JOB = 'CLERK' AND DEPTNO = 10;
SELECT * FROM EMP WHERE DEPTNO = 10 AND JOB = 'CLERK';
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = 10 OR DEPTNO = 20 OR DEPTNO = 30;
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE (DEPTNO = 10 OR DEPTNO = 20 OR DEPTNO = 30) AND JOB = 'CLERK';
-- Cost Based Optimizer
CREATE INDEX IDEPTNO ON BIG_EMP(DEPTNO);
CREATE INDEX IEMPNO ON BIG_EMP(EMPNO);
SET AUTOTRACE TRACE;
ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;
ANALYZE TABLE BIG_EMP COMPUTE STATISTICS;
ANALYZE INDEX IEMPNO COMPUTE STATISTICS;
ANALYZE INDEX IDEPTNO COMPUTE STATISTICS;
SELECT ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200
ORDER BY 1;
ANALYZE TABLE BIG_EMP DELETE STATISTICS;
ANALYZE INDEX IEMPNO DELETE STATISTICS;
ANALYZE INDEX IDEPTNO DELETE STATISTICS;
SELECT ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200
ORDER BY 1;
DROP INDEX IEMPNO;
DROP INDEX IDEPTNO;
SELECT TABLE_NAME, LAST_ANALYZED FROM USER_TABLES; -- 마지막으로 테이블 통게정보를 분석한 날짜
-- 통계 정보
ANALYZE TABLE BIG_EMP COMPUTE STATISTICS;
ANALYZE TABLE BIG_DEPT COMPUTE STATISTICS;
SELECT TABLE_NAME, BLOCKS,NUM_ROWS,AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME ='BIG_EMP' OR TABLE_NAME = 'BIG_DEPT';
COL TABLE_NAME FORMAT A15
COL COLUMN_NAME FORMAT A15
COL LOW_VALUE FORMAT A20
COL HIGH_VALUE FORMAT A20
SELECT TABLE_NAME, COLUMN_NAME, LOW_VALUE, HIGH_VALUE, NUM_DISTINCT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME ='BIG_EMP' OR TABLE_NAME = 'BIG_DEPT';
COL INDEX_NAME FORMAT A20
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, NUM_ROWS
FROM USER_INDEXES
WHERE TABLE_NAME ='BIG_EMP' OR TABLE_NAME = 'BIG_DEPT';