3.3. 실행계획의 제어

* 옵티마이저에 대한 충분한 이해 → SQL 에 대한 최적의 실행계획 판단 → 최적의 실행계획 유도
* 옵티마이저를 적절한 방법으로 제어 (힌트 사용 / SQL 수정)

-- http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/hintsref.htm#PFGRF94937
-- V$SQL_HINT : 263 개 힌트가 정의되어 있음 (11.2.0.1.0 기준)

* 힌트 목적
  * 과거 : 옵티마이저의 실수를 보완
  * 현재 : 옵티마이저에 조언 (옵티마이저가 모르는 정보의 보완, 사용자의 목적)
* 힌트 특성
  * 힌트는 "훈수" 다 - 옵티마이저 마음대로 에러 없이 무시
  * 자습적(Heuristic) 기법에 의한 초기치 선택(Cutoff)을 하는 전략
  * 버전 증가에 따른 힌트 추가/삭제 숙지 필요 - 힌트 변경 유형에 따른 액세스가 있다는 의미
* 힌트 이슈
  * 10% 이상의 쿼리에 힌트가 적용 되었다면 원인 파악 필요
  * 불필요한 힌트는 인덱스 구성 변경이 어려워지고, 옵티마이저에 의한 더 좋은 실행계획 선택을 막는다

-- T1 / LOCAL DB
DROP TABLE T1;

CREATE TABLE T1 AS
SELECT LEVEL-1 AS N1, TRUNC((LEVEL-1)/10) AS N2, TRUNC((LEVEL-1)/100) AS N3, TRUNC((LEVEL-1)/1000) AS N4 FROM DUAL CONNECT BY LEVEL <= 10000;

ALTER TABLE T1 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T1 MODIFY N4 NUMBER NOT NULL;

ALTER TABLE T1 ADD CONSTRAINT T1PK PRIMARY KEY (N1);
CREATE INDEX T1N2 ON T1 (N2);
CREATE INDEX T1N3 ON T1 (N3);
CREATE INDEX T1N4 ON T1 (N4);
CREATE INDEX T1N4N3 ON T1 (N4, N3);

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T1' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T1N4N3', DEGREE => 2);


-- T2 / LOCAL DB
DROP TABLE T2;

CREATE TABLE T2 AS SELECT * FROM T1;

ALTER TABLE T2 MODIFY N1 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N2 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N3 NUMBER NOT NULL;
ALTER TABLE T2 MODIFY N4 NUMBER NOT NULL;

ALTER TABLE T2 ADD CONSTRAINT T2PK PRIMARY KEY (N1);
CREATE INDEX T2N2 ON T2 (N2);
CREATE INDEX T2N3 ON T2 (N3);
CREATE INDEX T2N4 ON T2 (N4);
CREATE INDEX T2N4N3 ON T2 (N4, N3);

EXEC DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'UADMIN', TABNAME => 'T2' , DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2PK', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N2', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N3', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4', DEGREE => 2);
EXEC DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => 'UADMIN', INDNAME => 'T2N4N3', DEGREE => 2);


-- T3 / REMOTE DB
DROP TABLE T3;

CREATE TABLE T3 AS SELECT * FROM T1@TESTDB;


-- T4 / LOCAL DB
DROP CLUSTER H1 INCLUDING TABLES;

CREATE CLUSTER H1 (N4 NUMBER) HASHKEYS 10;

CREATE TABLE T4 CLUSTER H1 (N4) AS SELECT * FROM T1;


-- T5 / LOCAL DB
CREATE CLUSTER H2 (N4 NUMBER);

CREATE INDEX H2N4 ON CLUSTER H2;

CREATE TABLE T5 CLUSTER H2 (N4) AS SELECT * FROM T1;
CREATE TABLE T6 CLUSTER H2 (N4) AS SELECT * FROM T1;

-- M1 / LOCAL DB
CREATE MATERIALIZED VIEW M1 ENABLE QUERY REWRITE AS 
SELECT N4, COUNT(*) AS CNT FROM T1 GROUP BY N4;


-- PT1, PT2 / LOCAL DB
CREATE TABLE PT1 PARTITION BY LIST (N4)
(
  PARTITION PT1_0 VALUES (0),
  PARTITION PT1_1 VALUES (1),
  PARTITION PT1_2 VALUES (2),
  PARTITION PT1_3 VALUES (3),
  PARTITION PT1_4 VALUES (4),
  PARTITION PT1_5 VALUES (5),
  PARTITION PT1_6 VALUES (6),
  PARTITION PT1_7 VALUES (7),
  PARTITION PT1_8 VALUES (8),
  PARTITION PT1_9 VALUES (9)
)
AS SELECT * FROM T1;

CREATE TABLE PT2 PARTITION BY LIST (N4)
(
  PARTITION PT2_0 VALUES (0),
  PARTITION PT2_1 VALUES (1),
  PARTITION PT2_2 VALUES (2),
  PARTITION PT2_3 VALUES (3),
  PARTITION PT2_4 VALUES (4),
  PARTITION PT2_5 VALUES (5),
  PARTITION PT2_6 VALUES (6),
  PARTITION PT2_7 VALUES (7),
  PARTITION PT2_8 VALUES (8),
  PARTITION PT2_9 VALUES (9)
)
AS SELECT * FROM T1;