Transcript 1_sql튜닝교육

SQL Statement Tuning
e-Architecture 팀 임성욱
1/36
목
차
1. Index의 구성 및 Access 방식
2. Join 의 종류
3. Optimizer
4. 힌트의 종류 및 내용
5. SQL Processing Architecture
6. Explain Plan
7. Trace & Tkprof 사용법
8. 인덱스를 이용하지 않는 경우
9. 뷰의 사용
10. SQL 의 공유
참고자료 : 대용량 데이터베이스 솔루션 – 이화식 저
2/36
1.Index 구성 및 Access방식
SELECT empno, ename, job
FROM
emp
WHERE job = '부장'
ORDER BY empno;
INDEX-KEY
EMPNO ENAME JOB
ROWID
과장
0000A95B.0002.0001
7654
어진수
부장
과장
0000A95B.0005.0001
7900
최종욱
과장
과장
0000E62E.0009.0001
7689
홍길동
과장
과장
0000E9BE.0002.0001
7934
박문수
부장
부장
000062BE.0001.0001
7499
장민식
차장
부장
000062BE.0003.0001
7369
김태우
이사
부장
000093A6.0005.0001
7844
심정보
차장
부장
000093B2.000B.0001
7839
최연준
과장
이사
000069C5.0001.0001
7531
정연수
차장
차장
0000E9BE.0002.0001
7432
김인호
부장
차장
0000E9BE.0005.0001
7856
정명호
과장
차장
0000E9BE.000B.0001
7827
최민준
부장
TABLE (EMP)
INDEX (JOB)
3/36
SORT된 결과
2. Join 의 종류
SELECT a.FLD1, ..., b.FLD1,...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = 'AB'
AND b.FLD2 = '10'
2.1 Nested Loop Join
FLD1
='AB'
TABLE
ACCESS
BY
ROWID
KEY2=
KEY1
TABLE
ACCESS
BY
ROWID
FLD2
='10'
check
o
o
o
운반
단위
순차적 (부분범위처리 가능)
종속적 (먼저 처리되는 테이블의
x
처리범위에 따라 처리량 결정)
랜덤(Random) 액세스 위주
연결고리 상태에 따라 영향이 큼
주로 좁은 범위 처리에 유리
INDEX
(FLD1 TAB1
)
4/36
INDEX
(KEY2)
TAB2
2. Join 의 종류
TABLE1
1 A
2 C
3 D
4 K
5 M
6 F
7 E
8 M
....
.... ...
2.1 Nested Loop Join (계속)
TABLE2
A가
P나
C라
H사
...
E바
TABLE3
TABLE3
라 10
마 20
라 10
마 20
(2 row)
(2 row)
TABLE2
A가
P나
C라
S마
...
E마
(1000 row)
(1000 row)
TABLE1
1 A
2 C
3 D
4 K
5 M
6 F
7 E
8 M
....
....
(10000 row)
(10000 row)
최소 10,000회 이상 ACCESS
최대 6회 이하 ACCESS
5/36
2. Join 의 종류
2.2 Sort Join
SELECT /*+ use_merge(a b) */
a.FLD1, ..., b.FLD2,...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = 'AB'
AND b.FLD2 = '10'
TABLE
ACCESS
BY
ROWID
FLD1
='AB'
a.KEY1=
b.KEY2
를
조건으로
Merge
S
O
. R
.
. T
<처리순서>
1. a.FLD1 = ‘AB’ 조건으로 인덱스를 경유하여
TAB1 Access 후 결과 집합 Sort
TABLE
ACCES
S BY
ROWID
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
S
O
R
T
.
.
.
FLD2
='10'
.
.
.
2. b.FLD2 = ’10’ 조건으로 인덱스를 경유하여
TAB2 Access 후 결과 집합 Sort
운반단위
3. 1,2 에서 Sort된 2개의 집합을 Merge
INDEX
(FLD1)
TAB1
TAB2
6/36
INDEX
(FLD2)
2. Join 의 종류
2.3 Hash Join
SELECT /*+ use_hash(a b) */
a.FLD1, ..., b.FLD2,...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2
AND a.FLD1 = 'AB'
AND b.FLD2 = '10'
크기가 작은
파티션을
메모리에 로딩
TABLE
ACCESS
BY
ROWID
FLD1
='AB'
TABLE
ACCES
S BY
ROWID
메모리
FLD2
='10'
Building
파
티
. 션
. 1
.
<처리순서>
1. a.FLD1 = ‘AB’ 조건으로 인덱스를 경유하여
TAB1 Access 후 결과 집합 생성
2. b.FLD2 = ’10’ 조건으로 인덱스를 경유하여
.
.
.
TAB2 Access 후 결과 집합 생성
.
.
.
.
.
.
.
.
.
.
.
.
파
티
션
2
.
.
.
.
.
.
3. 1,2 에서 생성된 2개의 집합으로 파티션 짝을
운반단위
생성
4. 크기가 작은 파티션을 메모리에 로딩(Building)
하여 Hash Table 생성
5. 나머지 파티션의 로우을 읽어 Hash Table에
대응되는 로우가 있는지를 체크
INDEX
(FLD1)
TAB1
TAB2
7/36
INDEX
(FLD2)
3. Optimizer
3.1 Optimizer 의 종류
CHOOSE
ALL_ROWS
통계정보의 생성여부에 따라 Cost-Based Approach
(ALL_ROWS) 와 Rule-Based Approach 중 선택
Cost-Based Approach 사용 (Best Throughput 이 목적)
FIRST_ROWS
Cost-Based Approach 사용 (Best Response Time 이 목적)
RULE
미리 정해져 있는 Approach Rule에 의해 Access Path 결정
8/36
3. Optimizer
3.2 Optimizer Mode의 설정
Optimizer Mode Setting
Instance Level
 Initial Paramemer ($ORACLE_HOME/dbs/initSID.ora)에 기술
 OPTIMIZER_MODE = CHOOSE
Session Level
 ALTER SESSION SET OPTIMIZER_MODE = RULE;
 ALTER SESSION SET OPTIMIZER_GOAL = RULE;
Statement Level
 ORACLE Hint 사용 (/*+ hint */)
 RULE, CHOOSE, FIRST_ROWS, ALL_ROWS
Optimizer Approach 에 영항을 주는 요인
 Optimizer Mode
 Analyze (통계정보)
 Oracle Hints
9/36
3. Optimizer
3.3 Optimizer의 원리
Rule-Based Optimizer 의 Ranking
1.
ROWID에 의한 1 Row Access
2.
Cluster join 에 의한 1 Row Access
3.
Unique or Primary Key에 의한 1 Row Access
4.
Composite key
5.
Single-Column Indexes
6.
Indexed column 의 범위 검색
7.
Indexed column full scan
8.
Full Table Scan
10/36
3. Optimizer
3.3 Optimizer의 원리(계속)
Optimizer의 취사 선택
EMPNO
Index만 사용
RANKING 의 차이
Low COST 의 선택
SELECT
FROM
WHERE
AND
*
EMP
ENAME LIKE 'AB%'
EMPNO = '7890'
SELECT *
FROM EMP
FULL Table
Scan
WHERE EMPNO > '10'
HINT 에 의한 선택
SELECT
FROM
WHERE
AND
/*+ INDEX(EMP JOB_IDX) */ *
EMP
ENAME LIKE 'AB%'
JOB
LIKE 'SA%'
11/36
JOB Index만
사용
4. Hint 의 종류 및 내용
4.1 Optimization Approaches and Goals
Hint
Description
사용예
ALL_ROWS
Best Troughput
/*+ ALL_ROWS */
FIRST_ROWS
Best Response-Time
/*+ FIRST_ROWS */
※ FIRST_ROWS 힌트가 무시되는 경우
 DELETE, UPDATE Statements
 Set Operators (UNION, INTERSECT, MINUS, UNION ALL)
 GROUP BY clause
 FOR UPDATE clause
 Aggregate functions
 DISTINCT operator
CHOOSE
통계정보가 있는 경우 ALL_ROWS, 통계정보가 없는
경우 RULE
RULE
/*+ CHOOSE */
/*+ RULE */
12/36
4. Hint 의 종류 및 내용
4.2 Hint for Access Methods
Hint
Description
사용예
FULL
Full Table Scan
/*+ FULL(emp) */
ROWID
Table scan by rowid
/*+ ROWID(emp) */
SELECT /*+ ROWID(emp) */ *
FROM emp
WHERE ROWID > ‘AAAtkBBBDDDFEEFFFE’ AND empno = 123;
CLUSTER
Cluster scan
/*+ CLUSTER(emp) */
INDEX
Index scan
/*+ INDEX(emp emp_idx) */
INDEX 힌트 다음에 인덱스명을 여러 개 나열할 경우 해당 인덱스중 Cost가 가장
낮은 인덱스를 선택하고, 인덱스명을 생략할 경우 사용가능한 인덱스중 Cost가 가
장 낮은 인덱스를 선택
INDEX_ASC
INDEX 힌트와 동일
/*+ INDEX_ASC(emp emp_idx) */
INDEX_COMBINE
BITMAP 인덱스의 Boolean
Combination 사용
(BITMAP AND, OR Operation등)
/*+ INDEX_COMBINE(emp sal_bmi
hiredate_bmi) */
INDEX_DESC
Index Descending Range Scan
/*+ INDEX_DESC(emp emp_idx) */
13/36
4. Hint 의 종류 및 내용
4.2 Hint for Access Methods(계속)
Hint
Description
사용예
INDEX_FFS
Fast Full Index Scan (rather than a full
table scan)
/*+ INDEX_FFS(emp emp_idx) */
NO_INDEX
지정된 인덱스를 사용하지 않음
/*+ NO_INDEX(emp emp_idx) */
AND_EQUAL
Single Column 인덱스 Merge
/*+ AND_EQUAL(table index1 index2 ..
Index5) */
(2개부터 Max 5개 까지 지정가능)
USE_CONCAT
/*+ USE_CONCAT */
Union all 집합 연산자를 사용하여 질의의
Where 절에 있는 결합된 OR 조건을 혼합질
의로 변환
NO_EXPAND
Where절에 있는 OR 조건 또는 In-list를
Union all을 이용한 혼합질의로 변환하지
않음
/*+ NO_EXPAND */
Hints for Join Orders
Hint
Description
사용예
ORDERED
FROM절에 나타난 순서대로 Join 수행
/*+ ORDERED */
STAR
Star Query Plan 사용
/*+ STAR */
14/36
4. Hint 의 종류 및 내용
4.3 Hint for Join Operations and Parallel Exec.
Hint
Description
사용예
USE_NL
Nested Loop Join 사용
/*+ USE_NL(emp dept) */
USE_MERGE
Sort Merge Join 사용
/*+ USE_MERGE(emp dept) */
USE_HASH
Hash Join 사용
/*+ USE_HASH(emp dept) */
DRIVING_SITE
ORACLE이 선택한 Site외에 다른 Site를 /*+ DRIVING_SITE(emp) */
Driving Site로 지정해서 Query를 실행
LEADING
Join 순서에 있어서 지정된 테이블을 먼
저 Access
/*+ LEADING(emp) */
HASH_AJ &
MERGE_AJ
Not-In Subquery 사용시 Hash AntiJoin,
Sort Merge Anti-Join 사용
/*+ HASH_AJ */
/*+ MERGE_AJ */
Hints for Parallel Executions
Hint
Description
사용예
PARALLEL
Parallel Operation 사용
/*+ PARALLEL(emp, 5) */
NOPARALLEL
Parallel Operation을 사용하지 않음
/*+ NOPARALLEL(emp) */
15/36
4. Hint 의 종류 및 내용
Hint
4.4 Additional Hints
Description
사용예
CACHE
Full Table Scan시 추출된 Block을
Buffer cache의 Most recently
used LRU End 쪽에 위치시킴
(Small Lookup 테이블에 유리)
/*+ CACHE(emp) */
NOCACHE
Full Table Scan시 추출된 Block을
Buffer cache의 Least recently
used LRU End 쪽에 위치시킴(메
모리에서 빨리 Flush됨)
/*+ NOCACHE(emp) */
MERGE
View 또는 Sub-Query의 내용을
Merge
/*+ MERGE(v_emp) */
NO_MERGE
View 또는 Sub-Query의 내용을
Merge하지 않음
/*+ NO_MERGE(v_emp) */
PUSH_PRED
조건을 View내부에서 사용
/*+ PUSH_PRED(v_emp) */
PUSH_SUBQ
Sub-Query를 먼저 수행
/*+ PUSH_SUBQ */
STAR_TRANSFORMATION
STAR Query 수행시 Cartesian
Product를
피하고 Sub-Query형태로 변환
/*+ STAR_TRANSFORMATION */
16/36
5. SQL Processing Architectures
User
Result
SQL Query
Parser
SQL
Execution
Dictionary
 Parser
- Syntax Analysis
- Semantic Analysis
 Optimizer
- CBO / RBO
Rule-Based
Optimizer
RBO
CBO
Optimizer Mode?
Statistics
Cost-Based
Optimizer
Bind
Variable
Query plan
Row Source
Generator
17/36
 Row Source Generator
- receives the optimal plan
from the optimizer
- outputs the execution plan
for the SQL statement
6. Execution Plan
EXECUTION PLAN
• EXPLAIN PLAN Statement 사용 → PLAN_TABLE에 실행계획 Insert
• PLAN_TABLE에서 Query
• PLAN_TABLE 생성 : $ORACLE_HOME/rdbms/admin/utlxplan.sql 실행
SQL Statement
SELECT ename, job, sal, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND NOT EXISTS
(SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal
AND
hisal);
Explain Plan 예
ID OPERATION
OPTIONS
OBJECT_NAME
-------------------------------------------------------------------------------------0
SELECT STATEMENT
1
FILTER
2
NESTED LOOPS
3
TABLE ACCESS FULL
EMP
4
TABLE ACCESS BY ROWID
DEPT
5
INDEX
UNIQUE SCAN PK_DEPTNO
6
TABLE ACCESS FULL
SALGRADE
18/36
6. Execution Plan (계속)
♣ OPERATION
OPTION
설
명
AGGREGATE
그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추
출되도록 하는처리
AND-EQUAL
인덱스 머지를 이용하는 경우 중복 제거, 단일 인덱스 컬럼
을 사용하는 경우
CONNECT BY
CONNECT BY를 사용하여 트리구조로 전개
CONCATENATION
단위 액세스에서 추출한 로우들의 합집합을 생성(UNIONALL)
COUNTING
테이블의 로우 수를 센다.
FILTER
선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면
제거하는 작업
FIRST ROW
조회 로우 중에 첫 번째 로우만 추출한다.
FOR UPDATE
선택된 로우에 LOCK을 지정한다.
INDEX
UNIQUE
RANGE SCAN
UNIQUE 인덱스를 사용(단 한 개의 로우를 추출)
NON-UNIQUE한 인덱스를 사용(한개 이상의 로우)
INTERSECTION
교집합의 로우를 추출한다.(같은 값이 없다)
MERGE JOIN
먼저 자신의 조건만으로 액세스한 후 각각을 소트하여 머지
해 가는 조인
MINUS
MINUS 함수를 사용한다.
19/36
6. Execution Plan (계속)
♣ OPERATION
OPTION
설
명
NESTED LOOPS
먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를
이용해 다른 테이블을 연결하는 조인
REMOTE
다른 분산 데이타베이스에 있는 객체를 추출하기 위해 데이
타베이스 링크를 사용하는 경우
SORT
TABLE ACCESS
AGGREGATE
UNIQUE
GROUP BY
JOIN
ORDER BY
그룹함수(SUM, COUNT 등)를 사용하여 하나의 로우가 추출
되도록 하는 처리
같은 로우를 제거하기 위한 소트
액세스 결과를 GROUP BY하기 위한 소트
머지 조인을 하기 위한 소트
ORDER BY를 위한 소트
FULL
CLUSTER
HASH
BY ROWID
전체 테이블 스캔
클러스터 액세스
키값에 대한 해쉬 알고리즘을 사용
ROWID를 이용하여 테이블을 추출
UNION
두 집합의 합집합을 구한다.(중복없음) 항상 전체 범위를 구
한다.
UNION ALL
두 집합의 합집합을 구한다.(중복가능) UNOIN과 다르게 부
분범위 처리를 한다.
VIEW
어떤 처리에 의해 생성되는 가상의 집합(뷰)에서 추출한
다.(주로 서브쿼리에 의해서 수행된 결과)
20/36
7. ORACLE Trace 의 활용
7.1 Trace Enabling
 Initial Parameter의 USER_DUMP_DEST에 지정된 위치에 생성
단, ALTER SYSTEM SET USER_DUMP_DEST = <new_dir> 로 변경 가능
 Initial Parameter의 MAX_DUMP_FILE_DIZE (OS Block 단위)를 초과할 수 없다.
(MAX_DUMP_FILE_SIZE 의 Default Value: UNLIMITED)
 TIMED_STATISTICS Initial Parameter가 TRUE로 Set되어 있어야만 정확한 시간 산출가능
Trace Enable
 ALTER SESSION SET SQL_TRACE = TRUE;
 EXEC DBMS_SESSION.SET_SQL_TRACE( TRUE);
 EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);
 EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial#, 10046, trace_level, ‘’);
21/36
7. ORACLE Trace 의 활용
7.1 Trace Enabling (계속)
Trace Level




Trace Level
Trace Level
Trace Level
Trace Level
1 : 기본정보
4 : 기본정보 + Binding 정보 출력
8 : 기본정보 + Waiting 정보 출력
12 : 기본정보 + Binding + Waiting 정보 출력 (Trace File의 Size 증가에 유의)
22/36
7. ORACLE Trace 의 활용
7.2 TKPROF 의 활용
TKPROF ?
Trace File을 쉽게 읽을 수 있는 Format으로 요약
TKPROF 로 얻을 수 있는 내용





The text of the SQL statement
The SQL trace statistics in tabular form
The number of library cache misses for the parsing and execution of the statement
The user initially parsing the statement
The execution plan generated by EXPLAIN PLAN option
TKPROF 사용 예
$> tkprof ora_1234_master5.trc 1234.prf explain=apps/apps sys=no
$> tkprof ora_1234_master5.trc 1234.prf sort=(exeela, fchela)
$> tkprof ora_1234_master5.trc 1234.prf explain=scott/tiger
insert=store.sql sys=no sort=(execpu, fchcpu)
23/36
7. ORACLE Trace 의 활용
7.2 TKPROF 의 활용(계속)
TKPROF Options
filename1
Trace File = USER_DUMP_DEST 에 ora_spid_sid.trc 형태로 만들어진다
filename2
Tkprof 파일 명….이것은 원하는 파일 명을 줄 수 있다. <예…skk.prf>
EXPLAIN
Explain=user명 / Password
explain 을 사용하면, tkprof 시 다시 plan 정보를 뜬다.
따라서, plan 정보가 해당 trace 시와 다를 수도 있다. 주의 요망.
SYS
Sys=yes / no (Default yes )
Enables and disables the listing of SQL statements issued by the user SYS, or
recursive SQL statements, into the output file. The default value of YES causes
TKPROF to list these statements.
PRINT
print=10 (Default 지정하지 않으면 all )
Lists only the first integer sorted SQL statements into the output file.
AGGREGATE
aggregate=no (Default yes )
If you specify AGGREGATE = NO, then TKPROF does not aggregate multiple
users of the same SQL text.
24/36
7. ORACLE Trace 의 활용
select
from
where
and
7.2 TKPROF 결과항목 분석
count(*), sum(salqty)
trans t, diss d
t.idno=d.idno and t.transdate=d.dissdate
t.transdept = :b3 and d.dissseq=’6’
call
count
cpu elapsed
disk query current
rows
-------------------------------------------------------------------Parse
1 0.04
0.08
0
3
0
0
Execute
100 0.09
0.09
0
0
0
0
Fetch
100 6.54
6.77
12
12510
567
101
-------------------------------------------------------------------Total
201 6.67
6.88
12
12513
567
101
Misses in Library cache during parse : 1
Parsing user id = 12 (scott)
Rows
Execution Plan
------- -----------------------------------------------------0
SELECT STATEMENT
0
SORT (AGGREGATE)
4
NESTED LOOPS
204120
TABLE ACCESS(FULL) OF ‘TRANS’
759
TABLE ACCESS(BY ROWID) OF ‘DISS’
759
INDEX(UNIQUE SCAN) OF ‘DISS_PK’ (UNIQUE)
25/36
7. ORACLE Trace 의 활용
7.2 TKPROF 결과항목 분석 (계속)
PARSE
Systax Check, Semantic Check, Execution Plan 생성
EXECUTE
This step is the actual execution of the statement by Oracle. For INSERT, UPDATE,
and DELETE statements, this step modifies the data. For SELECT statements, the
step identifies the selected rows.
FETCH
SELECT 결과를 fetch 한 회수 (row 와 구분)
COUNT
parse, execute, fetch 된 횟수
CPU
Total CPU time in seconds for all parse, execute, or fetch calls for the statement.
ELAPSED
Total elapsed time in seconds for all parse, execute, or fetch calls for the
statement.
DISK
Total number of data blocks physically read from the datafiles on disk for all parse,
execute, or fetch calls.
QUERY
Total number of buffers retrieved in consistent mode for all parse, execute, or
fetch calls. Buffers are usually retrieved in consistent mode for queries.
CURRENT
Total number of buffers retrieved in current mode. Buffers are retrieved in current
mode for statements such as INSERT, UPDATE, and DELETE.
Misses in
library cache
The number of library cache misses resulting from parse and execute steps for
each SQL statement.
26/36
8. 인덱스를 이용하지 않는 경우
8.1 인덱스를 사용하지 않는 경우
INDEX COLUMN의 변형
SELECT *
FROM DEPT
WHERE SUBSTR(DNAME,1,3) = 'ABC'
NOT Operator
SELECT *
FROM EMP
WHERE JOB <> 'SALES'
NULL, NOT NULL
SELECT *
FROM EMP
WHERE ENAME IS NOT NULL
Optimizer 의 취사선택
SELECT *
FROM EMP
WHERE JOB LIKE 'AB%'
AND EMPNO = '7890'
27/36
8. 인덱스를 이용하지 않는 경우
8.2 Index Column의 변형
INDEX COLUMN의 변형(external)
SELECT *
FROM EMP
WHERE SUBSTR(DNAME,1,3) = 'ABC'
SELECT *
FROM EMP
WHERE DNAME LIKE 'ABC%'
SELECT *
FROM EMP
WHERE SAL * 12 = 12000000
SELECT *
FROM EMP
WHERE SAL = 12000000 / 12
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,'YYMMDD') =
'940101'
SELECT *
FROM EMP
WHERE HIREDATE =
TO_DATE('940101','YYMMDD')
28/36
8. 인덱스를 이용하지 않는 경우
8.2 Index Column의 변형 (계속)
INDEX COLUMN의 변형(external)
SELECT
FROM
WHERE
AND
*
EMP
EMPNO BETWEEN 100 AND 200
NVL(JOB,'X') = 'CLERK'
SELECT *
FROM EMP
WHERE EMPNO BETWEEN 100 AND 200
AND JOB = 'CLERK'
SELECT *
FROM EMP
WHERE DEPTNO = '10'
AND JOB
= 'SALSMAN'
SELECT *
FROM EMP
WHERE DEPTNO || JOB = '10SALESMAN'
의도적인 SUPPRESSING
SELECT *
FROM EMP
WHERE JOB = 'MANAGER'
SELECT *
FROM EMP
WHERE RTRIM(JOB) = 'MANAGER'
SELECT *
FROM EMP
WHERE EMPNO = 8978
SELECT *
FROM EMP
WHERE RTRIM(EMPNO) = 8978
29/36
8. 인덱스를 이용하지 않는 경우
8.2 Index Column의 변형 (계속)
INDEX COLUMN의 변형(internal)
SELECT * FROM SAMPLET
CHA = 10
WHERE
SELECT * FROM SAMPLET
TO_NUMBER(CHA) = 10
WHERE
SELECT * FROM SAMPLET
NUM LIKE '9410%'
WHERE
CREATE TABLE SAMPLET
( CHA CHAR(10),
NUM NUMBER (12,3),
VAR VARCHAR2(20),
DAT DATE)
SELECT * FROM SAMPLET
DAT = '01-JAN-94'
SELECT * FROM SAMPLET
WHERE
TO_CHAR(NUM) LIKE '9410%'
WHERE
SELECT * FROM SAMPLET
WHERE DAT = TO_DATE('01-JAN-94')
30/36
8. 인덱스를 이용하지 않는 경우
SELECT 'Not fornd !' INTO :COL1
FROM EMP
WHERE EMPNO <> '1234'
SELECT *
FROM EMP
WHERE ENAME LIKE '김%'
AND JOB <> 'SALES'
8.3 Not Operator
SELECT 'OK' INTO :COL1
FROM DUAL
WHERE NOT EXISTS ( SELECT '' FROM EMP
WHERE EMPNO = '1234')
SELECT *
FROM EMP a
WHERE a.ENAME LIKE '김%'
AND NOT EXISTS ( SELECT '' FROM EMP b
WHERE a.ENAME = b.ENAME
AND b.JOB = 'SALES')
SELECT *
FROM EMP
WHERE ENAME LIKE '김%'
MINUS
SELECT *
FROM EMP b
WHERE b.JOB = 'SALES'
31/36
8. 인덱스를 이용하지 않는 경우
8.4 IS NULL & IS NOT NULL
SELECT *
FROM EMP
WHERE ENAME IS NOT NULL
SELECT *
FROM EMP
WHERE ENAME > ' '
SELECT *
FROM EMP
WHERE COMM IS NOT NULL
SELECT *
FROM EMP
WHERE COMM > 0
SELECT *
FROM EMP
WHERE COMM IS NULL
32/36
9. View 의 활용
 테이블과 달리 물리적인 저장공간을 가지지 않음
 관련정보가 단지 Data Dictionary에 저장될 뿐임
 테이블과 거의 동등하게 취급될 수 있는 논리적인 집합
 SELECT : 제한없음
 INSERT, UPDATE, DELETE : 경우에 따라 가능
 인덱스, 클러스터링, 해쉬 클러스터 : 지정 불가
 각종 권한(object privilege) : 부여 가능
 하나의 테이블, 혹은 여러개의 테이블로 뷰를 생성할 수 있음
 저장공간을 가지지 않으므로 정규화 규칙을 무시하고 목적에 따라
자유롭게 사용할 수 있음
 최종적으로는 테이블을 엑세스하는 것
 사용에 따라 수행속도에 문제가 발생할 수 있음
33/36
9. View 의 활용(계속)
 뷰내에 변수를 지정할 수 없음
 테이블에 비해 특별히 수행속도를 저해하는 것은 없으며 옵티마이져에 의해
되는 수행경로에 영향을 받음
생성
 뷰의 엑세스 경로 생성원리를 숙지하여 잘 활용하면 양호한 수행속도를 보장
을 수 있음
받
 뷰는 뷰내에 사용된 테이블의 인덱스를 사용하게 되므로 인덱스 컬럼을 SQL
Function으로 함부로 가공시키지 말 것
 뷰내의 SELECT 문의 조건은 가능한 최적의 엑세스 경로를 사용할 수 있도록 하
거나 그럴 수 없다면 뷰를 사용한 SQL의 WHERE 절에서는 반드시 양호한 액세스
경로가 되도록 할 것
34/36
10. SQL문의 공유
SQL 의 공유
latch 에 대한 waiting 중library cache 와 shared pool 에 대한
waiting 이 가장 많다. 이 두가지 래치는 모두 원인이 literal sql
(상수값이 그대로 사용되는 문장) 때문이거나 shared pool 이 너무
작아서 발생하는 경우이다.
SQL 공유 원칙
•
•
•
•
바뀔 수 있는 값은 variable을 사용함.
여러 사용자가 자주 사용하는 sql 은 variable 을 사용해야 함.
항상 주요 조건이 있어야 함.
사용자의 조건에 따라 driving 테이블이 결정 되야 하는 경우 dynamic
SQL 을 사용하거나 literal 을 사용하여 CBO 가 최적의 경로를 찾도록
해야함.
35/36
10. SQL 문의 공유 (계속)
공유할 수 없는 SQL
SELECT * FROM EMP;
SELECT * FROM EMP;
SELECT * FROM EMP WHERE DEPTNO = :V_DEPTNO;
SELECT * FROM EMP WHERE DEPTNO = :D_NO;
SELECT * FROM EMP;
SELECT * FROM Emp;
SELECT * FROM EMP WHERE DEPTNO = '10';
SELECT * FROM EMP WHERE DEPTNO = '20';
SELECT * FROM EMP;
SELECT * FROM SCOTT.EMP;
36/36