Hint C Documents and SettingsAdministratorsqlplus scotttiger SQLPlus Release

Hint의 사용 방법 예제 C: ₩Documents and Settings₩Administrator>sqlplus scott/tiger SQL*Plus: Release 10. 2. 0. 1. 0 - Production on Copyright (c) 1982, 2005, Oracle. All rights reserved. 다음에 접속됨: Oracle Database 10 g Enterprise Edition Release 10. 2. 0. 1. 0 - Production With the Partitioning, OLAP and Data Mining options SQL> CREATE INDEX SCOTT. I_BIG_EMP_DEPTNO ON SCOTT. BIG_EMP(DEPTNO) 2 TABLESPACE USERS; 인덱스가 생성되었습니다. SQL> CREATE UNIQUE INDEX I_BIG_EMPNO ON BIG_EMP(EMPNO); 인덱스가 생성되었습니다. SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS; 테이블이 분석되었습니다. SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'ORIG' FOR SELECT ENAME, DEPTNO FROM BIG_EMP WHERE DEPTNO = 10; 해석되었습니다. 4

Hint의 사용 방법 예제 SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'ORIG')); PLAN_TABLE_OUTPUT ---------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------| 0 | SELECT STATEMENT | | 2940 | 42 (3)| |* 1 | TABLE ACCESS FULL| BIG_EMP | 2940 | 42 (3)| ---------------------------------Predicate Information (identified by operation id): -------------------------1 - filter("DEPTNO"=10) Note ----- 'PLAN_TABLE' is old version 16 개의 행이 선택되었습니다. SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'HINT 1' FOR SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_DEPTNO) */ ENAME, DEPTNO FROM BIG_EMP WHERE DEPTNO = 10; 해석되었습니다. 5

Hint의 사용 방법 예제 SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'HINT 1')); PLAN_TABLE_OUTPUT ------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------| 0 | SELECT STATEMENT | | 2940 | 53 (0)| | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 2940 | 53 (0)| |* 2 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO | 294 | | 1 (0)| ------------------------------------------Predicate Information (identified by operation id): -------------------------2 - access("DEPTNO"=10) Note ----- 'PLAN_TABLE' is old version 17 개의 행이 선택되었습니다. SQL> 6

Hint의 종류 ALL_ROWS 실습 SQL> 2 3 4 5 6 7 8 EXPLAIN PLAN SET STATEMENT_ID = 'ALL_ROWS' FOR SELECT /*+ INDEX(BIG_EMP I_BIG_EMP_DEPTNO) */ ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'ALL_ROWS')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 65844923 -----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -----------------------------------------------| 0 | SELECT STATEMENT | | 12 | 54 (2)| 00: 01 | SORT ORDER BY | | 12 | 54 (2)| 00: 01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 53 (0)| 00: 01 |* 3 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO | 294 | | 1 (0)| 00: 01 -----------------------------------------------Predicate Information (identified by operation id): -------------------------2 - filter("EMPNO"<=200 AND "EMPNO">=100) 3 - access("DEPTNO"=20) 16 개의 행이 선택되었습니다. SQL> 8

Hint의 종류 FIRST_ROWS(N) 실습 SQL> 2 3 4 5 6 7 8 EXPLAIN PLAN SET STATEMENT_ID = 'FIRST_ROWS' FOR SELECT /*+ FIRST_ROWS(1) */ ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'FIRST_ROWS')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 3671819838 -----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -----------------------------------------------| 0 | SELECT STATEMENT | | 12 | 5 (20)| 00: 01 | SORT ORDER BY | | 12 | 5 (20)| 00: 01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 4 (0)| 00: 01 |* 3 | INDEX RANGE SCAN | I_BIG_EMPNO | 99 | | 2 (0)| 00: 01 -----------------------------------------------Predicate Information (identified by operation id): -------------------------2 - filter("DEPTNO"=20) 3 - access("EMPNO">=100 AND "EMPNO"<=200) 16 개의 행이 선택되었습니다. SQL> 9

Hint의 종류 CHOOSE 실습 SQL> 2 3 4 5 6 7 8 EXPLAIN PLAN SET STATEMENT_ID = 'CHOOSE' FOR SELECT /*+ CHOOSE */ ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'CHOOSE')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 3671819838 -----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time -----------------------------------------------| 0 | SELECT STATEMENT | | 12 | 5 (20)| 00: 01 | SORT ORDER BY | | 12 | 5 (20)| 00: 01 |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 4 (0)| 00: 01 |* 3 | INDEX RANGE SCAN | I_BIG_EMPNO | 99 | | 2 (0)| 00: 01 -----------------------------------------------Predicate Information (identified by operation id): -------------------------2 - filter("DEPTNO"=20) 3 - access("EMPNO">=100 AND "EMPNO"<=200) 16 개의 행이 선택되었습니다. SQL> 10

Hint의 종류 RULE 실습 SQL> 2 3 4 5 6 7 8 EXPLAIN PLAN SET STATEMENT_ID = 'RULE' FOR SELECT /*+ RULE */ ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'RULE')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 65844923 ----------------------------| Id | Operation | Name | ----------------------------| 0 | SELECT STATEMENT | | | 1 | SORT ORDER BY | | |* 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | |* 3 | INDEX RANGE SCAN | I_BIG_EMP_DEPTNO | ----------------------------Predicate Information (identified by operation id): -------------------------2 - filter("EMPNO"<=200 AND "EMPNO">=100) 3 - access("DEPTNO"=20) Note ----- rule based optimizer used (consider using cbo) 20 개의 행이 선택되었습니다. 11

Hint의 종류 FULL 실습 SQL> 2 3 4 5 6 7 8 EXPLAIN PLAN SET STATEMENT_ID = 'FULL' FOR SELECT /*+ FULL(BIG_EMP) */ ENAME FROM BIG_EMP WHERE DEPTNO = 20 AND EMPNO BETWEEN 100 AND 200 ORDER BY ENAME; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'FULL')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 4283132093 ---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------| 0 | SELECT STATEMENT | | 12 | 44 (5)| 00: 01 | | 1 | SORT ORDER BY | | 12 | 44 (5)| 00: 01 | |* 2 | TABLE ACCESS FULL| BIG_EMP | 12 | 43 (3)| 00: 01 | ---------------------------------------Predicate Information (identified by operation id): -------------------------2 - filter("EMPNO"<=200 AND "DEPTNO"=20 AND "EMPNO">=100) 14 개의 행이 선택되었습니다. SQL> 13

Hint의 종류 INDEX 실습 SQL> CREATE INDEX TEST_INDEX ON BIG_EMP(EMPNO, ENAME); 인덱스가 생성되었습니다. SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'INDEX' FOR SELECT ENAME, DEPTNO FROM BIG_EMP WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'INDEX')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 3379895701 -----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------------| 0 | SELECT STATEMENT | | 12 | 2 (0)| 00: 01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 2 (0)| 00: 01 | |* 2 | INDEX UNIQUE SCAN | I_BIG_EMPNO | 1 | | 1 (0)| 00: 01 | -----------------------------------------------Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL> 14

Hint의 종류 INDEX 실습 SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'INDEX 1' FOR SELECT /*+ INDEX(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO FROM BIG_EMP WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'INDEX 1')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 1704472701 ---------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------| 0 | SELECT STATEMENT | | 12 | 3 (0)| 00: 01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 3 (0)| 00: 01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | 1 | | 2 (0)| 00: 01 | ---------------------------------------------Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL> 15

Hint의 종류 INDEX_ASC 실습 SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'INDEX_ASC' FOR SELECT /*+ INDEX_ASC(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO FROM BIG_EMP WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'INDEX_ASC')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 1704472701 ---------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------| 0 | SELECT STATEMENT | | 12 | 3 (0)| 00: 01 | | 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 12 | 3 (0)| 00: 01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | 1 | | 2 (0)| 00: 01 | ---------------------------------------------Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL> 16

Hint의 종류 INDEX_DESC 실습 SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'INDEX_DESC' FOR SELECT /*+ INDEX_DESC(BIG_EMP TEST_INDEX) */ ENAME, DEPTNO FROM BIG_EMP WHERE EMPNO = 10; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'INDEX_DESC')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 2076808727 ---------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------| 0 | SELECT STATEMENT | | 12 | 3 (0)| 00: 01 | | 1 | TABLE ACCESS BY INDEX ROWID | BIG_EMP | 12 | 3 (0)| 00: 01 | |* 2 | INDEX RANGE SCAN DESCENDING| TEST_INDEX | 1 | | 2 (0)| 00: 01 | ---------------------------------------------Predicate Information (identified by operation id): 2 - access("EMPNO"=10) 14 개의 행이 선택되었습니다. SQL> 17

Hint의 종류 INDEX_FFS 실습 SQL> 2 3 4 5 6 EXPLAIN PLAN SET STATEMENT_ID = 'INDEX_FF' FOR SELECT /*+ INDEX_FFS(BIG_EMP TEST_INDEX) */ EMPNO FROM BIG_EMP WHERE EMPNO < 200; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'INDEX_FF')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 9946052 -----------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -----------------------------------------| 0 | SELECT STATEMENT | | 192 | 768 | 21 (0)| 00: 01 | |* 1 | INDEX FAST FULL SCAN| TEST_INDEX | 192 | 768 | 21 (0)| 00: 01 | -----------------------------------------Predicate Information (identified by operation id): -------------------------1 - filter("EMPNO"<200) 13 개의 행이 선택되었습니다. SQL> 18

Hint의 종류 USE_CONCAT 실습 SQL> CREATE INDEX TEST_INDEX 1 ON BIG_EMP(SAL); 인덱스가 생성되었습니다. SQL> 2 3 4 5 6 7 EXPLAIN PLAN SET STATEMENT_ID = 'USE_CONCAT' FOR SELECT /*+ RULE USE_CONCAT */ * FROM BIG_EMP WHERE EMPNO > 50 OR SAL < 50000; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'USE_CONCAT')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 137407120 --------------------------| Id | Operation | Name | --------------------------| 0 | SELECT STATEMENT | | | 1 | CONCATENATION | | | 2 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | |* 3 | INDEX RANGE SCAN | TEST_INDEX 1 | |* 4 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | |* 5 | INDEX RANGE SCAN | TEST_INDEX | -------------------------- 20

Hint의 종류 USE_CONCAT 실습 Predicate Information (identified by operation id): -------------------------3 - access("SAL"<50000) 4 - filter(LNNVL("SAL"<50000)) 5 - access("EMPNO">50) Note ----PLAN_TABLE_OUTPUT ------------------------- rule based optimizer used (consider using cbo) 23 개의 행이 선택되었습니다. SQL> 21

Hint의 종류 NO_EXPAND 실습 SQL> 2 3 4 5 6 7 EXPLAIN PLAN SET STATEMENT_ID = 'NO_EXPAND' FOR SELECT /*+ INDEX(BIG_EMP TEST_INDEX) NO_EXPAND */ * FROM BIG_EMP WHERE EMPNO > 50 OR SAL < 50000; 해석되었습니다. SQL> SELECT PLAN_TABLE_OUTPUT 2 FROM TABLE(DBMS_XPLAN. DISPLAY('PLAN_TABLE', 'NO_EXPAND')); PLAN_TABLE_OUTPUT -------------------------Plan hash value: 400756699 ---------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------| 0 | SELECT STATEMENT | | 28955 | 1017 K| 623 (1)| 00: 08 | |* 1 | TABLE ACCESS BY INDEX ROWID| BIG_EMP | 28955 | 1017 K| 623 (1)| 00: 08 | | 2 | INDEX FULL SCAN | TEST_INDEX | 28955 | | 89 (0)| 00: 02 | ---------------------------------------------Predicate Information (identified by operation id): 1 - filter("SAL"<50000 OR "EMPNO">50) 14 개의 행이 선택되었습니다. SQL> 22

Hint의 종류 병렬 옵션 □ Parallel Execution Parallel execution을 사용하면 하나의 일을 여러 개의 일로 쪼개서 병렬로 수행한다. 이는 단일 수행보다 수행시간에 이득을 가져온다. (parallel query, parallel DDL, parallel DML, parallel Data Loading) 26

Hint의 종류 병렬 옵션 □ 동작원리 다음은 parallel execution에 대항하는 그림이다. User process SELECT /*+ parallel (orders, 4) */ COUNT (*) FROM ORDERS; Server process (Query coordinator) parallel execution에서는 서버 프로세스는 parallel execution server들의 상위의 역할을 수행하는 Parallel execution server processes parallel execution coordinator 역할을 수행한다. 그리고 더 이상의 일은 수행하지 않는다. 위의 쿼리는 4개의 sub task로 나뉘어서 수행된다. Database 28

Hint의 종류 병렬 옵션 □ Parallel Query full table scan, full index scan, partition range scan, index range scan을 수행하는 SELECT문에서 사용된다. parallel 힌트 사용을 통해 수행할 수 있다. 또는 테이블을 변경하여 쿼리 수행 시 해당 테이블이 위 경우에 해당하면 자동으로 parallel 정도로 수행이된다. SELECT /*+ parallel (orders, 4) */ COUNT (*) FROM ORDERS; ALTER TABLE ORDERS PARALLEL (DEGREE 4); 세션 레벨에서 parallel execution에 대해서 사용을 금할 수 있다 ALTER SESSION DISABLE PARALLEL QUERY; 또는 문장 단위로 사용을 금할 수 있다. SELECT /*+ NO_PARALLEL (ORDERS) */ COUNT (*) FROM ORDERS; 29

Hint의 종류 병렬 옵션 □ Parallel DML INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */ INTO TEMP_ORDERS SELECT * FROM ORDERS; 위 문장은 SELECT 문은 Single execution으로 수행이 되고 INSERT 문은 parallel로 수행된다. 반면 INSERT /*+ PARALLEL (TEMP_ORDERS, 4) */ INTO TEMP_ORDERS SELECT /*+ PARALLEL (ORDERS, 4) */ * FROM ORDERS; 위 문장은 SELECT 문과 INSERT 문 모두가 parallel로 수행된다. 31