'Database/Oracle'에 해당되는 글 25건
- 2014.02.20 오라클 힌트 정리표
- 2014.02.20 [오라클] TABLE ANALYZE 하기
- 2012.02.03 오라클 OBJECT 조회(USER_SOURCE 활용)
- 2011.03.02 오라클 테이블 복사 쿼리
- 2011.03.01 crow의 오라클 백업/복구 제2회
- 2011.03.01 crow의 오라클 백업/복구 제1회
- 2011.02.28 SYS_CONNECT_BY_PATH , CONNECT BY
- 2010.10.07 SQL) 세로를 가로의 데이터로 만들기
- 2010.10.07 sum(decode)참조
- 2010.01.21 오라클에서 not exists, not in, minus의 성능차이
http://blog.naver.com/itough2323?Redirect=Log&logNo=80125972268
http://blog.naver.com/explojh?Redirect=Log&logNo=60036603826
◆ 개요
힌트는 SQL 튜닝의 핵심부분으로 일종의 지시구문이다.
SQL에 포함되어 쓰여져 Optimizer의 실행 계획을 원하는 대로 바꿀 수 있게 해준다.
오라클 Optimizer라고 해서 항상 최선의 실행 계획을 수립할 수는 없으므로
테이블이나 인덱스의 잘못된 실행 계획을 개발자가 직접 바꿀 수 있도록 도와주는 것이다.
사용자는 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지에 대해 알고 있는데
이 경우 오라클 서버의 Optimizer에 의존하여 나온 실행 계획보다 훨씬 효율적인 실행 계획을 사용자가 구사할 수 있다.
◆ 사용
힌트를 사용하여 아래와 같은 것들을 할 수 있다.
액세스 경로, 조인 순서, 병렬 및 직렬 처리, Optimizer의 목표(Goal)를 변경 가능하다.
◆ 오라클 힌트 사용예
|
SELECT /*+ INDEX(idx_col1) */ |
|
SELECT /*+ ORDERED INDEX(b idx_col1) */ |
※ 주의! 주석 표시 뒤에 '+' 기호가 있다.
◆ INDEX Access Operation 관련 HINT
|
HINT |
내용 |
사용법 |
|
INDEX |
INDEX를 순차적으로 스캔 |
INDEX(TABLE_name, INDEX_name) |
|
INDEX_ASC |
INDEX를 내림차순으로 스캔. |
|
|
INDEX_DESC |
INDEX를 오름차순으로 스캔. |
INDEX_DESC(TABLE_name, INDEX_name) |
|
INDEX_FFS |
INDEX FAST FULL SCAN |
INDEX_FFS(TABLE_name, INDEX_name) |
|
PARALLEL_INDEX |
INDEX PARALLEL SCAN |
PARALLEL_INDEX(TABLE_name, INDEX_name) |
|
NOPARALLEL_INDEX |
INDEX PARALLEL SCAN 제한 |
NOPARALLEL_INDEX(TABLE_name, INDEX_name) |
|
AND_EQUALS |
여러개의 INDEX MARGE 수행 |
AND_EQUALS(INDEX_name, INDEX_name) |
|
FULL |
FULL SCAN 지정된 테이블에 대한 전체 스캔. |
FULL(TABLE_name) |
◆ JOIN Access Operator 관련 HINT
|
HINT |
내용 |
사용법 |
|
USE_NL |
NESTED LOOP JOIN 옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다. 먼저 특정 TABLE의 ROW에 액세스하고 그 값에 해당하는 다른 TABLE의 ROW를 찾는 작업을 해당범위까지 실행하는 조인. |
USE_NL(TABLE1, TABLE2) |
|
USE_NL_WITH_INDEX |
INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다. |
USE_NL_WITH_INDEX(TABLE INDEX) |
|
USE_MERGE |
SORT MERGE JOIN 옵티마이저가 SORT MERGE JOIN을 사용하도록 한다. 먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식. |
USE_MERGE(TABLE1, TABLE2) |
|
USE_HASH |
HASH JOIN 옵티마이저가 HASH JOIN을 사용하도록 한다. |
USE_HASH(TABLE1, TABLE2) |
|
HASH_AJ |
HASH ANTIJOIN |
HASH_AJ(TABLE1, TABLE2) |
|
HASH_SJ |
HASH SEMIJOIN |
HASH_SJ(TABLE1, TABLE2) |
|
NL_AJ |
NESTED LOOP ANTIJOIN |
NL_AJ(TABLE1, TABLE2) |
|
NL_SJ |
NESTED LOOP SEMIJOIN |
NL_SJ(TABLE1, TABLE2) |
|
MERGE_AJ |
SORT MERGE ANTIJOIN |
MERGE_AJ(TABLE1, TABLE2) |
|
MERGE_SJ |
SORT MERGE SEMIJOIN |
MERGE_SJ(TABLE1, TABLE2) |
◆ JOIN시 DRIVING 순서 결정 HINT
|
HINT |
내용 |
사용법 |
|
ORDERED |
FROM절에 명시된 테이블의 순서대로 DRIVING |
|
|
LEADING |
파라미터에 명시된 테이블의 순서대로 JOIN |
LEAING(TABLE_name1, TABLE_name2, ...) |
|
DRIVING |
해당 테이블을 먼저 DRIVING |
DRIVING(TABLE) |
◆ 기타 HINT
|
HINT |
내용 |
사용법 |
|
APPEND |
INSERT시 DIRECT LOADING |
|
|
PARALLEL |
SELECT, INSERT시 여러개의 프로세스로 수행 |
PARALLEL(TABLE, 개수) |
|
CACHE |
데이터를 메모리에 CACHING |
|
|
NOCACHE |
데이터를 메모리에 CACHING하지 않음 |
|
|
PUSH_SUBQ |
SUBQUERY를 먼저 수행 |
|
|
REWRITE |
QUERY REWRITE 수행 |
|
|
NOREWIRTE |
QUERY REWRITE를 수행 못함 |
|
|
USE_CONCAT |
IN절을 CONCATENATION ACCESS OPERATION으로 수행 |
|
|
USE_EXPAND |
IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함 |
|
|
MERGE |
VIEW MERGING 수행 |
|
|
NO_MERGE |
VIEW MERGING 수행못하게 함 |
|
※ 추가
ALL_ROWS : 가장 좋은 단위 처리량의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (즉, 전체적인 최소의 자원 소비, 모든 레코드의 처리하는 시간의 최소화를 목적으로 최적화)
FIRST_ROWS : 가장 좋은 응답 시간의 목표로 문 블록을 최적화하기 위해 cost-based 접근 방법을 선택합니다. (첫번째 레코드의 추출 시간을 최소화할 목적으로 최적화)
CHOOSE : 최적자(optimizer)가 그 문에 의해 접근된 테이블을 위해 통계의 존재에 근거를 두는 SQL문을 위해 rule-based 접근 방법과 cost-based 접근 방법 사이에 선택하게 됩니다.
CLUSTER : 지정된 테이블에 대한 클러스터 스캔.
HASH : 지정된 테이블에 대한 해쉬 스캔.
ROWID : 지정된 테이블에 대한 ROWID에 의한 테이블 스캔.
RULE : explicitlly chooses rule-based optimization for a statement block. rule-base Optimizer를 사용.
◆ 주의
|
SELECT /*+ ORDERED USE_NL(A B C) INDEX(B ITOREDRETL1) */ FROM TORDERDTL B, TORDER A, TITEM C WHERE ... |
1. ORDERED : FROM 절에 기술한 테이블 순(B → A → C 순)으로 조인함. USE_NL에 사용한 순서가 아님.
※ 참고) LEADING, 예) LEADING(B) : FROM 절의 기술 순서와 상관없이 B테이블이 가장 먼저 선행됨.
2. USE_NL : 조인방법을 Nested Loops방식으로 선택.
예) USE_NL(A B), 테이블명이 아닌 테이블에 대한 Alias명
참고) USE_HASH, USE_MERGE
3. INDEX : 특정 인덱스를 오름차순으로 읽음.
예) INDEX(B ITORDERDTL1) : B는 TORDERDTL 테이블의 Alias명, 사용할 인덱스명 기술
참고) USE_DESC(B ITORDERDTL1) : ITORDERDTL1 인덱스를 내림차순으로 읽음
◆ 실행계획 살펴보기 1
|
SELECT * FROM ( ==> 인라인뷰 3 ( ==> 인라인뷰 2 ( ==> 인라인뷰 1 ) ) ) |
과 같이 되어 있다고 하자.
이럴 경우 실행계획을 보면, 우리가 예상했던대로 인라인뷰1 ==> 인라인뷰2 ==> 인라인뷰3 순서로 드라이빙 되지 않는 경우가 있다.
이런 경우 강제로 위 순서대로 실행계획을 수립하도록 하는 힌트
SELECT /*+ ORDERED */
*
FROM ( .....
위 힌트를 사용하면 맨 깊숙한 인라인뷰부터 실행된다.
◆ 힌트 사용하기
1. /*+ USE_CONCAT */
USE_CONCAT : 조건절에 있는 OR 연산자조건 (또는 IN 연산자 조건)을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결(Concatenation)하는 실행계획을 수립하도록 유도하는 힌트.
반드시 처리주관 조건이 OR로 나누어졌을 때 적용해야 하며, 잘못 사용하면 비효율이 발생할 수 있으므로 주의해야 함.
예>
SELECT /*+ USE_CONCAT */
FROM employees
WHERE job = &job
OR dept_no = &deptno;
풀어쓰자면
SELECT *
FROM employees
WHERE job = &job
UNION ALL
SELECT *
FROM employees
WHERER dept_no = &deptno;
WHERE 절 이후에 나오는 컬럼에 맞게 인덱스를 탄다.
2. /*+ NO_EXPAND */
조건절에 있는 OR 연산자 조건 또는 IN 연산자 조건을 연결 실행계획으로 처리되지 않도록 할 때 사용하는 힌트.
USE_CONCAT의 반대 개념.
예>
SELECT /*+ NO_EXPAND */
FROM customer
WHERE cust_type in ('A','B');
참고 : http://blog.naver.com/hkjhc107?Redirect=Log&logNo=130035827974
◆ 참고
1. Nested Loop
- 테이블의 인덱스끼리 inner-outer 루프를 형성하여 결과를 쿼리하는 방식입니다.
- 제일 많은 유형의 실행계획입니다.
2. Sort Merge
- 쿼리의 결과가 많은 양의 데이터를 읽는 경우, 테이블들을 각각 full-scan하여 같은 키값을 갖는 데이터끼리 조인하여 실행합니다.
- Sort-Merge 방식은 많은 메모리와 디스크 I/O를 필요로 하기 때문에, sqlplus를 실행하는 주체의 메모리/CPU/디스크 스펙에 많은 영향을 받습니다.
3. Hash Join
- 한 테이블은 매우 많은 Row를 갖고, 다른 한 테이블은 매우 적은 Row를 가질 때, 해쉬 알고리즘에 의해 큰 테이블을 여러개의 버켓으로 나누어 쿼리를 수행하는 방식입니다. 작은 테이블은 인덱스를 태우는 것보다 full-scan을 하는 것이 유리할 때 사용됩니다.
[출처] [본문스크랩] 오라클 힌트 정리표|작성자 귀차니즘극복
얼마전에 있었던 경험을 바탕으로 내용을 적어 본다.
갑자기 DBA의 실수로 운영DB의 테이블이 DROP 되었다.
신속한 시스템 복구를 위해 운 좋게 직전에 백업해두었던 테이블이 있어서 임시로 복구를 했다.
그냥 저냥 업무시간대를 잘 넘기고 있어서 별 문제 없이 지나가나 했었다.
갑자기 퇴근시간이 다되어 DB의 CPU 사용률이 높아지더니 떨어질 기미가 보이지 않는다.
여러 쿼리들의 수행시간이 엄청 길어졌고 계속 부하가 걸려온다.
느려진 쿼리의 PLAN을 떠봤다. 갑자기 타지 않던 FULL SCAN 플랜이 자꾸 뜬다.
분명히 DROP된 테이블과 관련이 있는듯 했으나, 새로 생성된 INDEX를 봐도 기존과 동일하게 문제가 없었다.
암만 봐도 인덱스의 문제가 없다.. 이거 왜이러지 하는 순간... 번뜩 생각이 났다.
보통 DB의 옵티마이져는 COST 베이스 방식으로 운영이 되고, 옵티마이저에 따라 DB가 플랜을 만들어서 돌린다.
이때 통계정보를 활용한다.... 바로 이거인듯 했다.
그래서 ANALYZE 된 정보를 확인해 보았다.
SELECT TABLE_NAME,
NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN,
SAMPLE_SIZE,
LAST_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME = 'SAMPLE_TABLE'
;
역시나 TABLE이 DROP되면서 통계정보도 날아갔던 것이다.
즉, 아무리 인덱스가 생성되어 있어도 데이터의 형태에 따라 만들어진 통계정보가 없으면 FULL_SCAN이 발생한다.
DROP된 테이블이 시스템 사용시 무조건 사용되는 테이블이라 그 영향력은 크다.
그럼 통계정보를 생성해 본다.
ANALYZE TABLE SAMPLE_TABLE
COMPUTE STATISTICS
;
[문법 설명]
ANALYZE object_name operation STATISTICS
[VALIDATE STRUCTURE[CASCADE]]
[LIST CHAINED ROWS[INTO tables]]
* object는 TABLE, INDEX, CLUSTER 중에 해당하는 오브젝트 기술
* operation
- COMPUTE : 각각의 값등을 정확하게 계산한다. 가장 정확한 통계를 얻을 수 있지만 처리속도가 가장 느리다.
- ESTIMATE : 자료사전의 값과 데이터 견본을 가지고 통계를 예상한다. 덜정확하지만 처리속도가 훨씬 빠르다.
- DELETE : 테이블의 모든 통계정보를 삭제한다.
수행하고 나서 상단의 조회 쿼리를 다시 조회하면 결과가 나온다.
자 이제 느린 쿼리를 다시 수행해 본다... 음 역시 예전의 상태를 되찾았다!
[출처] [오라클] TABLE ANALYZE 하기|작성자 귀차니즘극복
SELECT * FROM USER_SOURCE
WHERE TEXT LIKE '%테이블명%'
[출처] 오라클 테이블 복사 쿼리 |작성자 수유산장
테스트용 테이블 만들때 유용하게 쓸수 있죠..
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER
검색을 걸어서 필요한 데이타만 복사할수도 있죠.
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER WHERE IDX<10000
테이블 구조만 복사하고 싶다면..
CREATE TABLE TEST_MEMBER AS SELECT * FROM MEMBER WHERE 1=2
검색조건을 이렇게 한다면 테이타는 복사가 안되겠죠..
테이블이 이미 만들어진 경우는
INSERT INTO TEST_MEMBER SELECT * FROM MEMBER
주의할점은 테이블구조만 복사된다는 것이다.
프라이머리키나 인덱스등은 복사가 안돼니 따로 설정해 주어야 한다.
안녕하세요 영원한 초보 crow입니다. 두번째 강좌입니다..^^
김호님 감사합니다 허접하기 짝이 없는 강좌가 올라갈 줄이야 꿈에도 몰랐습니다.
앞으로 더욱 알차게 실무에 바로 쓸 수 있게 올리겠습니다.
그럼 첫번째 강좌에 이어 오늘은 테이블스페이스를 만들어 보고, 크기 조정하고, 지우고...
그리고 테이블 스페이스가 지웠졌을 때 복구하는 이런 것들을 해보죠.
아무래도 데이타가 쌓이는 곳이 이곳이다 보니 쪼까 중요하자나요..
일단 오라클을 실행하구요..
[oracle@crow oracle]$ svrmgrl SVRMGR> connect internal SVRMGR> startup |
전 시간에 배운걸 이용해서 테이블 스페이스가 어디있는지 확인하겠습니다.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files; TABLESPACE_NAME BYTES FILE_NAME ------------------------------------------------------------------------------- SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf |
보시는대로 /home/oracle/oradata/ORCL 에 다 있군요.. 그러면 test라는 테이블 스페이스를 하나 만들어 보겠습니다.
SVRMGR> create tablespace test
2> datafile '/home/oracle/oradata/ORCL/test_01.dbf'
3> size 10K;
Statement processed.
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
------------------------------ ---------- ------------------------------------------
SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf
OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf
RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf
TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf
USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf
INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf
DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf
TEST 10240 /home/oracle/oradata/ORCL/test_01.dbf <==여기보이시죠
8 rows selected. |
보시는대로 test라는 테이블 스페이스를 만들고요, /home/oracle/oradata/ORCL/ 에 test_01.dbf라는 이름의 파일을 만들었습니다.. 아.. 그리고 크기는 10kbyte 로 만들어놨습니다.. 여기서 꼭 /home/oracle/oradata/ORCL/ 에 만들 필요는 없습니다. 전 그저 쉽게 하기 위해 이렇게 하는겁니다..^^ 그럼 테이블 스페이스의 크기를 늘려볼까요?
SVRMGR> alter tablespace test add datafile '/home//oracle/oradata/ORCL/test_02.dbf' size 30K; Statement processed. |
흠 그럼 데이타 파일 크기를 늘리죠.
SVRMGR> !ls -al /home/oracle/oradata/ORCL/test_02.dbf -rw-r----- 1 oracle dba 53248 Apr 13 06:05 /home//oracle/oradata/ORCL/test_02.dbf |
크기가 늘어났군요.. 그럼 지워볼까요..
SVRMGR> drop tablespace test; Statement processed. |
지금은 테이블 스페이스 안에 데이타가 없어서 그냥 이렇게 지워졌지만.. 데이타가 들어있다면
drop tablespace test including contents; |
이렇게 하세요.. 그럼 어디 데이타 파일도 지워졌나 보죠.
SVRMGR> !ls -al /home//oracle/oradata/ORCL/ total 2363032 drwxr-x--x 2 oracle dba 4096 Apr 13 06:02 . drwxr-x--x 3 oracle dba 4096 Nov 27 05:54 .. -rw-r----- 1 oracle dba 4315136 Apr 13 06:11 control01.ctl -rw-r----- 1 oracle dba 4315136 Apr 13 06:11 control02.ctl -rw-r----- 1 oracle dba 83888128 Apr 13 05:48 drsys01.dbf -rw-r----- 1 oracle dba 10487808 Apr 13 05:48 indx01.dbf -rw-r----- 1 oracle dba 5244928 Apr 13 05:48 oemrep01.dbf -rw-r----- 1 oracle dba 26542080 Apr 13 05:48 rbs01.dbf -rw-r----- 1 oracle dba 512512 Apr 13 05:48 redo01.log -rw-r----- 1 oracle dba 512512 Apr 13 06:08 redo02.log -rw-r----- 1 oracle dba 183502848 Apr 13 05:48 system01.dbf -rw-r----- 1 oracle dba 10487808 Apr 13 05:48 temp01.dbf -rw-r----- 1 oracle dba 12288 Apr 13 06:08 test_01.dbf <== -rw-r----- 1 oracle dba 53248 Apr 13 06:08 test_02.dbf <==데이타파일은 이렇게 2개 살아있죠 -rw-r----- 1 oracle dba 2087411712 Apr 13 05:48 users01.dbf |
이 데이타 파일을 지워보겠습니다..
SVRMGR> !rm -f /home//oracle/oradata/ORCL/test_* |
자 이렇게 해서 지워졌습니다..
SVRMGR> shutdown immediate; SVRMGR> exit |
데이타 베이스를 종료하고 나왔습니다.. 지금까지 테이블스페이스를 만들어보고, 조정하고, 지워봤습니다. 자, 이제 temp 라는 테이블 스페이스를 유실시킨 후 복구 실습을 한번 해보겠습니다. 역시 오라클을 시동하시구요,
[oracle@crow oracle]$ svrmgrl SVRMGR> connect internal SVRMGR> startup |
그럼 먼저 temp 테이블 스페이스의 크기와 경로를 알아보구요
SVRMGR> select tablespace_name, bytes, file_name from dba_data_files; TABLESPACE_NAME BYTES FILE_NAME ------------------------------ ----------ㅡ------------------------------------------------ SYSTEM 183500800 /home/oracle/oradata/ORCL/system01.dbf OEM_REPOSITORY 5242880 /home/oracle/oradata/ORCL/oemrep01.dbf RBS 26540032 /home/oracle/oradata/ORCL/rbs01.dbf -------------------------------------------------------------------------------- TEMP 10485760 /home/oracle/oradata/ORCL/temp01.dbf<==여기 보이시죠 -------------------------------------------------------------------------------- USERS 2087409664 /home/oracle/oradata/ORCL/users01.dbf INDX 10485760 /home/oracle/oradata/ORCL/indx01.dbf DRSYS 83886080 /home/oracle/oradata/ORCL/drsys01.dbf 7 rows selected. |
경로는 아까 본 거기이고 크기는 1M 네요.
SVRMGR> shutdown abort <== failure가 되었다고 가정하는 것입니다. ORACLE instance shut down. |
[oracle@crow ORCL]$ svrmgrl SVRMGR> connect internal Connected. |
위처럼 파일을 지우고요. 아래처럼
SVRMGR> alter database open; Statement processed. |
이렇게 데이타베이스를 open 합니다 그리고 나서
SVRMGR> drop tablespace temp including contents; Statement processed. |
이렇게 temp 스페이스를 지웁니다.. 그런다음 아래와 같이
SVRMGR> create tablespace temp datafile '/home/oracle/oradata/ORCL/temp01.dbf' size 1M; Statement processed. |
아까 경로 와 크기를 알아둔 거 있자나요. 그걸 그대로 이용해서 하나 다시 만들어줍니다..그럼 끝.. 이제 오라클을 종료하고요..
SVRMGR> shutdown immediate |
스타트업을 합니다..
SVRMGR> startup ORACLE instance started. Total System Global Area 71998864 bytes Fixed Size 64912 bytes Variable Size 54984704 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytes Database mounted. Database opened. |
잘 실행되네요.. 다시 종료시키죠..
SVRMGR> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SVRMGR> exit Server Manager complete. |
파일이 복구되어있는지 ls로 확인해 보면..
[oracle@crow ORCL]$ ls control01.ctl drsys01.dbf oemrep01.dbf redo01.log system01.dbf temp01.org control02.ctl indx01.dbf rbs01.dbf redo02.log temp01.dbf users01.dbf [oracle@crow ORCL]$ rm -f temp01.org <== 테스트가 끝났으므로 이젠 필요없는 파일을 지웁니다.. |
이렇게 테스트가 끝났습니다. 테이블스페이스 유실이었을때 이렇게 하면 됩니다.. 그런데 여기서 중요한 건 경로와 크기를 잘 알아야 한다는 것이겠죠.. 이걸 어떻게 기억하낭^^ 오늘은 이만 합니다..^^ 좀 난해하면 crow@dreamsta.com으로 연락주세요... 변변치 않는 실력에 답변까지 하기가 좀 뭐하지만 최선을 다해 답변해드리겠습니다.. 감사합니다.. 다음 강좌에서는 아카이브 모드와 노아카이브모드에대해서 알아보져.. 그럼 안녕히계세요..
출처 : http://cafe.naver.com/javalove.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=294&
안녕하세요? 백업/복구 과정 강좌를 맡게 된 영원한 초보 crow입니다.. 정식 명칭은 10의 redcrow 이구요 저도 초보라 강좌가 강좌답게 갈런지 의심스럽네요..^^ .. 현재 (주)DWS에서 허접한 개발자로 있구요 ^^ 처음 게임프로그래머로 시작을 했고 IT쪽은 이제 1년이 쪼금 안됐네요.. 나이요 절라 많아요 73년12월10일생이에요. 음력으로여^^ 아무쪼록 귀엽게 봐주시구요.. 백업은 저도 몇 달 전에 학원에서 듣고 써 먹지를 못했습니다.. 학원에서 배운 그대로 여러분에게 전달은 안되겠지만 한번 최선을 다해 보도록 하겠습니다.. 저도 테스트를 해봐야 하기에 컴터는 펜Ⅲ650 / 128M (리눅스 6.2/오라클 8i) 에 설치를 해놓았습니다.. 그리고 일단, 여러분.. 디비는 만들어보셨는지 ^^ 오라클을 설치하면 기본적으로 디비가 하나 만들어지지만, 그거말고 여러분이 디비를 하나 만들줄 알아야 합니다.. 전 여러분이 디비를 만들줄 안다는 가정하에서 들어가겠습니다.. 왜냐하면요.. 오라클 데이타베이스에 전반적인 메모리 구조와 프로세서 구조를 설명을 해야 하는데.. 저도 사실 가물가물하거든요 오라클을 안만진지가 어언~~~~~~~몇 개월돼서..^^ 그럼 일단 오라클 데이타 베이스에 데이타파일, 컨트롤파일, 리두로그파일 들이 어디에 있는지 알아보죠 이 파일들이 꼭 있어야 백업이 가능하고 복구가 가능합니다. 오늘은 이 파일들을 가지고 놀아보겠습니다. 그리고 이 파일들을 가지고 백업을 하는건 다음시간에 하기로 하구요.. 데이타 베이스에 데이터파일은 v$datafile, v$datafile_header, dba_data_files 이 값들을 추출해서 볼 수 있습니다.. 데이터파일이란 테이블스페이스를 구성하는 파일로 실제로 데이타가 저장되는 곳입니다. 한번 실습해보죠..
일단 오라클이 시동이 안되어있다면 이렇게 시동하세요.
[oracle@crow oracle]$ svrmgrl Oracle Server Manager Release 3.1.5.0.0 - Production (c) Copyright 1997, Oracle Corporation. All Rights Reserved. Oracle8i Release 8.1.5.0.2 - Production With the Java option PL/SQL Release 8.1.5.0.0 - Production SVRMGR> connect internal Connected. SVRMGR> startup ORACLE instance started. Total System Global Area 71998864 bytes Fixed Size 64912 bytes Variable Size 54984704 bytes Database Buffers 16777216 bytes Redo Buffers 172032 bytes Database mounted. Database opened. |
참고로 오라클 종료 명령어는 shutdown입니다. 이제 값을 추출해 보죠.
SVRMGR> select name from v$datafile; NAME -------------------------------------------------------------------------------- /home/oracle/oradata/ORCL/system01.dbf /home/oracle/oradata/ORCL/oemrep01.dbf /home/oracle/oradata/ORCL/rbs01.dbf /home/oracle/oradata/ORCL/temp01.dbf /home/oracle/oradata/ORCL/users01.dbf /home/oracle/oradata/ORCL/indx01.dbf /home/oracle/oradata/ORCL/drsys01.dbf |
데이타 파일 위치와 이름들이 보이시죠.. 디비를 한번 만들어보신 분들은 그 사용 목적이 뭔지 아실껍니다.. 모르시는 분들도 그냥 이런게 있구나만 알고 계십시요. 이 강좌는 오라클 백업과 복구이기 때문에 자세한 설명은 다음에 제가 오라클 디비를 만들어 볼 때 해드리거나 아니면 다른 참고 자료를 읽어보세요.. 제가 잘가는 database.sarang.net에 가보시면 오라클뿐만아니라 여러가지 많은 디비들의 자료가 있습니다. 그럼 계속 나가보겠습니다.. 데이터파일에 관한 정보는 v$datafile과 v$datafile_header, dba_files 에 있습니다. 또 있던거 같은데 생각이 나질 않네요...^^; 우리는 지금 데이타파일의 위치와 이름만 알면 되니까 이거면 충분합니다. 그리고 컨드롤파일의 위치와 파일 이름은 v$controlfile, 리두로그는 v$logfile, v$log를 통해서 볼 수 있습니다..
SVRMGR> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /home/oracle/oradata/ORCL/control01.ctl /home/oracle/oradata/ORCL/control02.ctl |
컨트롤 파일 위치 정보 입니다.. 컨트롤 파일은 데이타 화일들의 위치나 Online/Offline 상태, 온라인 로그화일들의 위치와 가장 최근의 system change number 값과 가장최근에 발행한 CheckPoint 시간과 각종 오라클 관련 정보를 저장하는곳입니다.
SVRMGR> select * from v$logfile; GROUP# STATUS MEMBER ======================================================= 1 STALE /home/oracle/oradata/ORCL/redo01.log <--현재 이것만 사용하고 있습니다.. 2 /home/oracle/oradata/ORCL/redo02.log <--이건 아직 사용대기중입니다.. |
로그파일 위치와 파일이름이 출력되었습니다. 리두로그파일은 변화된 모든 데이타를 저장하며 데이타베이스 복구 작업의 경우에만 사용됩니다. 참고로 데이타베이스 대한 정보는 v$database 로 보실 수 있습니다. 그리고 각 v$view 에 대한 필드명을 알고 싶으면, 알고 있겠지만 desc v$database, v$datafile(테이블명); 아시겠져.. 오늘 강좌를 이걸로 마치겠습니다 다음 강좌에서는 테이블스페이스를 하나 만들고 테이블을 만들어서 데이타를 넣어서 그걸 가지고 백업해보겠습니다.. 그럼 이만..^^
출처 : http://blog.naver.com/cjj721004?Redirect=Log&logNo=120116296911
1. SYS_CONNECT_BY_PATH(column,char)은 데이터의 셀프조인에 의한 트리구조 데이테에 대해서 한row에서 표현할때 사용하는 것입니다.
간단히 말씀드리면, FIle시스템에서 파일이 위치하는 경로에 대해서 표현하는것과 같다고 생각하시면 됩니다.
2. "START WITH rnum = 1" 은 경로중에서 root에 해당하는 부분을 어디서 부터 할것인지 설정한는 조건을 말합니다.
3. "CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR a = a"은 계층을 만들기 위한 조인부분으로 경로의 다음 연결 경로를 지정하는 역할을 합니다.
즉, A -> B -> C 로 데이터의 연결을 만들기 위함입니다.
4. 이함수는 Oracle에서 사용하는 것으로, 특별한 환경 설정은 없으며, 단지 Data에 대해서 계층구조를 가지고 있다면 언제든지 사용을 할수 있습니다.
가장 대표적인 예제로는 회의의 조직도 Data가 되겠네요.. ^^
출처 : Tong - redyoon님의 DB통
간단한 샘플
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
다음의 실행하면 결과값은 다음과 같이 나올 것이다.
A | B
1 | 엄마,아빠,이모
2 | 삼촌,오빠
---------------
어떻게 만들어지는지 분석모드
SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL
A B
1 엄마
1 아빠
1 이모
2 삼촌
2 오빠
일단 기본 데이터를 정의한다.
SELECT A,B,
ROW_NUMBER() OVER( ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
A B RNUM
1 엄마 1
1 아빠 2
1 이모 3
2 삼촌 4
2 오빠 5
기본데이터를 ROW_NUMBER() OVER( ORDER BY A) 를 사용해서 A 컬럼을 기준으로 정렬을해서 RNUM 을 정의해준다.
SELECT A
,SUBSTR(SYS_CONNECT_BY_PATH(B, ','),2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
A B
1 엄마
1 엄마,아빠
1 엄마,아빠,이모
2 삼촌
2 삼촌,오빠
위에 나온데이터를 기준으로 SYS_CONNECT_BY_PATH 를 써줬다.
START WITH RNUM = 1 시작 기준점을 RNUM 1을 시작점으로 잡고
CONNECT BY PRIOR RNUM = RNUM - 1 RNUM과 하나작은 RNUM과 결합하면서,
AND PRIOR A = A A값을 기준으로 같은거 끼리 묶어줬다.
CONNECT BY PRIOR RNUM = RNUM - 1 를 이해할때는 하나의 BOM을 생각하면 될거다.
시작은 RNUM = 1 로 시작하고
RNUM 이 2이면 RNUM 1과 2를 합치면서 A와 A가 같은걸 결합
RNUM 이 3이면 RNUM 3과 2를 합치고 A와 A가 같은걸 결합
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
A B
1 엄마,아빠,이모
2 삼촌,오빠
그룹함수를 써서 A로 그룹지어서 가장큰 값을 뽑아오면 끝
[출처] SYS_CONNECT_BY_PATH , CONNECT BY|작성자 아기대장
/* Formatted on 2010/10/07 14:11 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'A' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'B' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'C' AS col2
FROM DUAL)
SELECT col1, LTRIM (SYS_CONNECT_BY_PATH (col2, ','), ',') AS col1
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col1) rn,
COUNT (*) OVER (PARTITION BY col1) cnt
FROM tmp)
WHERE LEVEL = cnt
START WITH rn = 1
CONNECT BY PRIOR col1 = col1 AND PRIOR rn = rn - 1;
2) 세로를 가로의 컬럼 데이터로 만들기
/* Formatted on 2010/10/07 16:07 (Formatter Plus v4.8.8) */
WITH tmp AS
(SELECT '1' AS col1, 'a' AS col2
FROM DUAL
UNION ALL
SELECT '2' AS col1, '가' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'b' AS col2
FROM DUAL
UNION ALL
SELECT '1' AS col1, 'c' AS col2
FROM DUAL)
SELECT col1, MIN (DECODE (r, 1, col2)), MIN (DECODE (r, 2, col2)),
MIN (DECODE (r, 2, col2))
FROM (SELECT col1, col2,
ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col2) r
FROM tmp)
GROUP BY col1
- 편리한 나머지 남용하는 사례가 늘고 있다;
가. NULL값의 처리
- 예의 널 공포증 문제
- 함부로 NVL함수를(특히 행 단위 처리 내에서) 사용하면 비효율이 발생한다.
- NULL 값은 애초에 비교 대상에서 제외되므로, NULL인 행을 선택하는 DECODE가 아니면 NVL을 사용할 필요가 없다.
- SUM(DECODE…) 연산에서는 NULL은 아예 연산에서 제외되므로 굳이 NVL(col, 0)을 사용할 필요가 없다. (NVL을 DECODE 안에 사용하면 쓸데없는 연산이 발생한다.)
- SUM(DECODE…)에서 DECODE로 추출된 행 전체의 값이 NULL이어서 결과가 NULL인 경우, 0으로 표시해주고 싶다면, SUM 바깥에 NVL을 사용해주면 된다.
- 두 개 이상의 열의 연산에 대한 SUM이 필요한 경우
: DECODE문 안에 NVL을 안 쓰면 한 열만 NULL인 행도 연산에서 제외되는 문제 발생
à 각 열의 SUM에 대한 연산으로 전개하면 굳이 NVL을 사용하지 않아도 된다.
- ELSE 부의 값이 0인 경우 SUM에서는 NULL인 경우와 결과가 달라지지 않으므로 굳이 ELSE부를 쓸 이유가 없다. (ELSE부 값이 없으면 NULL처리되므로 수행속도의 차이 발생)
- SUM(DECODE…)에서 가장 부하가 큰 부분은 DECODE 콤보; 부분이다.
- 특히 nested decode는 매우 부하가 크다.
- 수행속도를 위해 DECODE를 단순하게 만들어 줄 필요가 있다.
- 수행속도 절감 사례;
- 여러 열에 대해 계속 다중 DECODE를 쓰는 경우
: 각 열을 하나로 concatnation해서 DECODE하면 하나의 DECODE로 줄일 수 있다. (DECODE 안은 좀 길어지지만;)
- 범위 처리(a : SUM(DECODE(SIGN(b-x), 1, DECODE(SIGN(a-x), -1, y)))
à SUM(DECODE(ABS(a-x)+ABS(b-x), b-a, y))
- 열 조합에 의한 경우의 수가 매우 많은 경우
: 2진분류나 10진분류등 기수법 체계를 활용한다.
- 여러가지 수학함수를 이용해서 최대한 간단한 수학적 모델을 만들어낸다.
- SUM(DECODE…)를 이용해 추출할 열이 많은 경우의 부하를 줄이기 위해 GROUP BY를 사용할 수 있다.
SUM(DECODE(공통수식, 경우1, 값1)), SUM(DECODE(공통수식, 경우2, 값2)), SUM(DECODE(공통수식, 경우3, 값3)), ……
à SUM(DECODE(A, 경우1, 값1)), SUM(DECODE(A, 경우2, 값2)), SUM(DECODE(A, 경우3, 값3)), …... FROM (SELECT 공통수식 A, …… GROUP BY 공통수식)
è 결과값은 뒤의 SQL이 좀더 보기 흉해-_-지지만 수행속도는 훨씬 빠르다.
(GROUP BY를 한 번더 사용하는 성형수술은 부하가 크지 않다.)
- SUM(DECODE…)를 이용해 추출할 열이 많은 경우의 부하를 줄이기 위해 GROUP BY를 사용할 수 있다.
- 개수만 알아내는 데에는 SUM보다 COUNT가 수행속도면에서 훨씬 유리하다. (근데 대체 개수 알아내는데 SUM을 왜 쓰냐? -_-)
- COUNT(특정 열) 보다 COUNT(*)이 더 빠르다.
- *는 ‘모든 열’이 아니라 ‘조건을 만족하는 열에 대한 wild card’의 의미를 가진다.
- 일관성을 지키지 않은 못된-_- 테이블을 처리해야 하는 경우, GROUP BY 연산에서 해당 열이 NULL인 행과 NULL이 아닌 행에 대해 각각 새로운 행이 생성되는 경우가 있다.
à MIN을 이용해 해결한다.
- SUM, AVG등은 숫자에만 가능하지만, MIN, MAX등은 모든 형태에 다 사용 가능하다.
- 뻔한 얘기;
3.3.1. 확장 UPDATE문
- DECODE와 같이 조건 처리를 할 수 없는 DBMS에서는 효용이 적다.
- UPDATE에서 가장 중요한 것은 UPDATE할 대상 집합의 명확화이다.
- UPDATE 구문에서는 WHERE절이 선택하는 집합의 크기가 UPDATE의 작업량을 결정하므로 잘 관리해야 한다.
- WHERE절에 있는 서브쿼리가 먼저 수행되면, 결과물이 상수의 집합이 되므로 WHERE절의 처리 범위를 줄일 수 있다.
- 행을 FETCH해서 가공, 처리, 다시 UPDATE하는 절차적 PL/SQL 프로그래밍 루틴을, UPDATE와 SELECT를 이용해서 하나의 SQL로 만들 수 있다.
- 프로그램 루틴의 SQL화 작업 시 주의점
1. SQL이 매우 커지므로 큰 롤백 세그먼트가 필요하며, 롤백이나 커밋시에 매우 많은 행 이 수정되므로 다른 작업에 영향을 줄 수가 있다.
2. 처리 시에 발생하는 개별 행의 에러를 선별하기가 곤란하다.
3. 하나의 SELECT 가공 결과로 여러 테이블을 UPDATE할 수 없다.
4. 서브쿼리에 조건을 만족하는 값이 하나도 없어 선택된 행이 없는 경우, NULL이 업데 이트 될 수 있다. à 가장 critical한 문제점
è EXISTS루틴 등으로 해결
- Error와 Fail의 차이점 : Error는 SQL이 수행되지 않았을 때, Fail은 수행되었으나 결 과가 공집합일 때 발생한다.
- GROUP함수에서 SQL은 절대 실패하지 않는다. (최소 하나의 행이 생성된다.)
è 실패한 SQL에서는 NVL을 사용해도 NULL 값의 발생을 막을 수 없지만, GROUP 함수와 같이 실패하지 않은 SQL에서는 NVL을 사용해 NULL값을 가공할 수 있다.
5. UPDATE문은 항상 UPDATE되는 테이블이 선행해야 하고, SET 절 내의 서브쿼리는 항상 나중에 반복수행되므로, 서브쿼리에서 가공을 위해 추출해야 하는 행이 많을 때에 는 다중처리 방법을 이용해야 한다.
- 오라클 7.3 이상부터 적용
- 과거에 비해 비교적 자유롭게 조인 뷰에서 INSERT, DELETE, UPDATE를 할 수 있다. (그래도 여전히 제약이 많다.)
- 오라클 7.3부터 FROM절에 하나 이상의 테이블이나 뷰가 위치하는 조인이 된 뷰를 수정할 수 있게 됨
- 제약조건 : DISTINCT, 그룹함수(SUM, AVG등), 집합처리(UNION, INTERSECT등), GROUP BY와 HAVING처리, ROWNUM의 사용, 순환처리(CONNECT BY/START WITH 구문)을 사용한 뷰는 수정할 수 없다.
- 최종 뷰로 병합 가능한 뷰만 수정할 수 있다.
- 조인된 테이블 중에서 키보존 테이블만 수정할 수 있다.
- 조인으로 인해 변화가 일어난 집합의 논리적인 기본키가 자신의 기본키대로 유지되는 테이블 (조인을 했어도 자기 집합의 키 레벨은 변하지 않는 테이블)
- 1:M 조인의 경우 - 조인 결과의 키는 M측 테이블의 키 레벨이 되므로, M측 테이블은 키 레벨이 변하지 않지만, 1측 테이블은 조인 집합을 식별하는 기본키가 될 수 없다.
- M:M 조인의 경우 – 조인 결과는 둘의 Cartesian Product 만큼의 행이 생성 되므로, 두 테이블 모두 키보존 테이블이 될 수 없다. (M:M은 구현 단계에서 나와서는 안되는 구조이므로 별 걱정할 필요 없다-_-)
- 1:1 조인의 경우 – 조인 결과가 양쪽 테이블의 키 레벨과 같으므로, 두 테이블 모두 키보존 테이블이 될 수 있다.
- 키 레벨이 변경되지 않았어도 OUTER JOIN에 의해 생성된 집합에서는 최하위 테이블이 키보존 테이블이 될 수 없는 경우가 있다. (M측 테이블이 OUTER JOIN되는 경우 NULL 필드들이 양산되므로 더 이상 기본키의 역할을 할 수 없다.)
- 조인뷰의 UPDATE에서 WHERE절에는 아무거나 올 수 있지만, SET 절에는 키보존 테이블의 열만 올 수 있다.
- 조인뷰의 DELETE에서는 반드시 단 하나의 키보존 테이블만 가져야 한다.
- 조인뷰의 INSERT는 DELETE와 같이 하나의 키보존 테이블만 가져야 가능하며, 여기에 덧붙여 원 테이블의 제약조건(constraints)을 만족해야 한다. (당연한 얘기;)
이 문제는 오라클만의 문제가 아니고 거의 모든 RDBMS 제품들에 대해 공통적인 문제입니다.
일단 SQL 튜닝에서는 모든 상황에 항상 맞는 것은 없습니다.
즉, SQL 튜닝엔 왕도가 없다는 말입니다. 수학공식 외우듯이 외워서 튜닝을 하는 것은 아니며 그때 그때 데이터의 분포, 서버의 상태, 인덱스의 유무 및 SQL trace나 tkprof결과 등의 각종 참조가능한 수치들을 분석하여 튜닝방향을 정합니다.
상황에 따라 다른 모든 경우엔 가장 안 좋던 방법이 특정 경우엔 최적의 솔루션이 될 수 있습니다.
참고하세요...;
질문의 3가지 + @ 방법의 두드러진 특징만 구별할 수 있어도 판단에 많은 도움이 되겠지요.
A 집합에서 B집합의 데이터를 제외한 나머지만 구하는 방법은 질문의 3가지를 포함하여 상황에 따라 보통 크게 5가지정도를 주로 쓰게 됩니다. 하나씩 특징만 간단히 적겠습니다...자세한 내용은 직접 공부하세요...;
1. not in ...
SELECT * FROM A WHERE a.key not in (SELECT b.key FROM B)
형태의 구문이며, B쪽을 먼저 access하여 b.key로 a.key에 공급자역할을 하는 서브쿼리로 쓰고 싶을 때 주로 사용합니다.
2. not exists ...
SELECT * FROM A WHERE not exists (SELECT * FROM B WHERE b.key = a.key)
형태의 구문이며, A쪽을 먼저 access하고 나서 a의 각 row들을 not exists로 조사하여 filtering하는 처리를 할 때 주로 사용합니다. 즉, B를 access하기 전에 A쪽의 전체범위가 먼저 access됩니다.
이 때의 서브쿼리는 공급자가 아닌 확인자역할만 해 줄 수 있습니다.
3. minus ...
SELECT key, col1, col2 FROM A
MINUS
SELECT key, col1, col2 FROM B
형태의 구문이며, 테스트 해 보면 아시겠지만 MINUS는 특성 상 sort와 중복제거 수행을 동반합니다.
그러므로 가장 이해하기는 간단하나 대용량에서는 사용 시 주의해야 합니다.
A나 B집합의 access대상이 대량인 경우 대량의 sort와 중복제거가 발생하므로 이들 처리에 많은 시간이 소요될 수 있는 쿼리입니다.
4. Outer + Null Check ...
SELECT * FROM A, B WHERE A.key = B.Key(+) AND B.Key IS NULL
형태의 구문이며, 위의 not in이나 not exists가 주로 Nested Loop Join 또는 Nested Loop Anti Join 방법을 수행하는데 비해 대용량의 경우 Hash Join이나 Merge Join을 유도하여 성능을 보장받을 수 있는 방법입니다.
단, 각 DBMS 마다 A LEFT OUTER JOIN B ON ~ , (*)등으로 아우터조인에 대한 표현은 약간 씩 다릅니다.
5. UNION ALL + Group count 또는 count() over() 분석함수 이용등 ...
SELECT *
FROM(
SELECT a.*
, COUNT(DISTINCT gbn) OVER(PARTITION BY key) AS cnt
, COUNT(DISTINCT DECODE(gbn, 'A', 1)) OVER(PARTITION BY key) AS a_cnt
FROM(
SELECT 'A' AS gbn, key, col1, col2 FROM A UNION ALL
SELECT 'B' AS gbn, key, col1, col2 FROM B
) a
)
WHERE cnt < 2 AND a_cnt = 1
형태의 구문이며, UNION ALL은 MINUS와 달리 sort나 중복제거를 하지 않고 별다른 조인도 없기 때문에 양쪽집합에 scan할 마땅한 인덱스가 없거나 하는 상황에서 위력을 발휘할 수 있는 솔루션입니다.
GROUP BY와 COUNT 함수로도 위의 의미를 그대로 만들 수 있습니다...분석함수나 통계함수를 지원하지 않는 DBMS들은 COUNT() OVER() 대신 GROUP BY / COUNT로 변경해야겠지요...;
이외에도 구현할 수 있는 방법들이야 더 있겠지만, 대부분의 상황들에서 위의 예시들이 주로 많이 쓰인다는 것을 거듭 밝힙니다.
