Alice's Story

** Sub Query
* 단일행 서브쿼리 (스칼라 서브쿼리)
'=' ,'<', '>'  연산자를 사용한 단일행 서브쿼리

* 다중행 서브 쿼리
서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리.
다중 행 비교 연산자를 사용 가능.

IN : 메인쿼리의 비교 조건이 서브쿼리의 결과중에서 하나라도 일치하면 참. '=' 비교만 가능
ANY, SOME : 메인쿼리의 비교 조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참. '=' 과 '<' , '>' 범위 비교도 가능함
> any (작은값), < any(큰값)
ALL : 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 모든값이 일치하면 참.
> all(큰값), < all(작은값)
EXISTS : 메인 쿼리의 비교조건이 서브 쿼리의 결과중에서 만족하는 값이 하나라도 존재하면 참.

* 다중 칼럼 서브쿼리
서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브 쿼리이다.
PAIRWISE 방식과 UNPAIRWISE 방식이 있다.

* 상호 연관 서브쿼리
메인쿼리와 서브쿼리간에 검색 결과를 교환하는 서브쿼리이다

*** 서브쿼리 사용시 오류사항
단일행 서브쿼리오류 ? 서브쿼리 리턴값이 1개가 아닌경우
메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않을 경우
서브쿼리내에 ORDER BY 절을 사용할 경우
서브쿼리 검색 결과가 NULL일 경우- NULL과 연산결과는 모두 NULL 됨

*문제 : 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과 이름과 지도교수 이름을 출력하시요.
select s.name, s.weight, d.dname, p.name
from student s, department d, professor p
where s.deptno=d.deptno
and s.profno= p.profno(+)
and s.weight< (select avg(weight) from student where deptno=(select deptno from student where name='이광훈'))

** DML
* INSERT INTO table [(column[ ,column,…]) ] VALUES (value [, value,…]);

* UPDATE table SET column = value [, column=value,…] [WHERE condition];

* DELETE [FREOM ] table [WHERE condition];
DELETE FROM table WHERE (column1, column2,..) = (SELECT s_column1,s_column2.. FROM table2 [WHERE condition2])

* MERGE INTO [table] [alias]
USING [table | view| subquery ] alias
ON [ join condition]
WHEN MATCHED THEN
UPDATE SET …..
WHEN NOT MATCHED THEN
INSERT INTO ….
VALUES …;

* COMMIT
트랜잭션 내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료

* ROLLBACK
트랜잭션 내의 모은 SQL 명령문에 의해 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료

* 시퀀스
CREATE SEQUENCE sequence
[INCREMENT BY n] <- 시퀀스 번호의 증가 값으로 기본값은 1
[START WITH n] <- 시퀀스 시작번호로 기본값은 1
[MAXVALUE n | NOMAXVALUE] <- 생성 가능한 시퀀스 최대값
[MINVALUE n | NOMINVALUE] <-CYCLE일 경우 새로 시작되는 값
[CYCLE | NOCYCLE] <- 시퀀스 번호를 순환 사용할 것인지 지정
[CACHE n | NOCACHE] <- 시퀀스 생성속도를 개선하기 위해 캐싱여부 지정

* CURRVAR과 NEXTVAL
시퀀스에서 생성된 현재 번호를 확인하거나 다음 번호를 생성하는 함수
CREATE SEQUENCE s_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100;

SELECT s_seq.NEXTVAL FROM dual;
SELECT s_seq.CURRVAL FROM dual;
INSERT INTO student(studno, name, deptno) VALUES (s_seq.NEXTVAL, '홍길동',101);
DROP SEQUENCE s_seq;

권순용의 DB 이야기

인덱스 관련 실행 계획은 SQL 최적화의 기본이다

 인덱스의 실행 계획은 다른 어떤 실행 계획보다 종류가 많다. 그래서인지 많은 사람들이 인덱스의 실행 계획에 대해 잘 이해하지 못하는 경우가 많다. 인덱스의 실행 계획은 인덱스를 이용했는지 안 했는지만을 의미한다고 생각하는 경우가 많은 것 같다. 이와 같은 생각이 우리로 하여금 SQL 최적화를 더욱 어렵게 만드는 원인일 것이다. 이제부터라도 인덱스에 대한 모든 실행 계획을 이해해야 할 것이다.

 SQL의 최적화에 있어서 인덱스의 실행 계획에 대한 이해는 매우 중요하다. 이는 대부분의 실행 계획에는 인덱스 관련 실행 계획이 생성되기 때문이다. 이와 같기 때문에 인덱스 관련 실행 계획을 이해하지 못한다면 SQL 최적화는 절대 수행할 수 없게 된다. 또한 인덱스 관련 실행 계획의 종류가 많기 때문에 인덱스 실행 계획에 대한 정확한 이해는 반드시 필요하다. 이번 호에서는 인덱스를 이용하는 실행 계획 중 Index Full Scan에 대한 실행 계획을 분석하는 방법에 대해 같이 한번 확인해 보도록 하자.

 인덱스도 Full Scan이 가능하다

 많은 사람들은 Full Scan하면 테이블에 대한 Full Scan만 생각하게 된다. 테이블에 대한 Full Scan이 보편적이기는 하지만 인덱스 또한 Full Scan이 발생할 수 있다는 것을 잊지 않길 바란다. 그렇다면 인덱스에 대한 Full Scan은 한 가지만 존재하는가? 인덱스에 대한 Full Scan에는 여러 가지 종류가 존재한다. 우선 인덱스에서 발생할 수 있는 모든 실행 계획을 확인해 보자. 

- Index Range Scan - Index Unique Scan - Index Full Scan - Index Fast Full Scan - Index Skip Scan - Index Min/Max Scan - Index Join

 인덱스를 엑세스하는 방법에는 위와 같은 형태가 존재한다. 이와 같이 우리가 생각지 못한 여러 방법으로 인덱스를 엑세스할 수 있다. 위의 인덱스 관련 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 어떤 실행 계획일까? 위의 인덱스 실행 계획 중 인덱스 Full Scan에 대한 실행 계획은 아래와 같이 두 가지이다.

 위의 두 가지 실행 계획 중 하나의 실행 계획이 생성된다면 이는 인덱스를 Full Scan한 실행 계획이 된다. 인덱스 Full Scan은 일반적으로 성능 저하를 발생시키므로 주의해야 될 실행 계획임을 명심하길 바란다.

 인덱스도 Full Scan의 수행을 이해하자

 인덱스 Full Scan은 단어 그대로 인덱스를 처음부터 끝까지 엑세스하는 실행 계획이다. 그렇기 때문에 일반적으로 성능 저하가 발생할 수 있다. 다음 예제를 확인해 보자.  

SQL> SELECT COL1, COL2 FROM TAB1 WHERE COL3 = 'AAA' ORDER BY COL4

 위의 SQL에서 TAB1 테이블에는 COL4+COL3 인덱스가 있다고 가정하자. 그렇다면 해당 SQL은 과연 COL4+COL3 인덱스를 이용할 수 있는가? 기본적으로는 대부분의 사람들은 해당 인덱스를 이용할 수 없다고 이야기할 것이다. 하지만 해당 SQL은 COL4+COL3 인덱스를 이용할 수 있다. 해당 인덱스를 이용한다면 어떻게 실행이 되겠는가? COL4 컬럼은 WHERE 절의 조건이 아니므로 처리 범위를 감소시킬 수 없게 된다. 하지만 해당 인덱스는 COL4 컬럼이 인덱스의 앞에 존재하므로 인덱스를 이용하여 처리 범위를 감소시킬 수는 없게 된다. 그렇기 때문에 해당 SQL은 COL4+COL3 인덱스를 처음부터 끝까지 엑세스하는 인덱스 Full Scan의 실행 계획이 생성되게 된다. 

SELECT STATEMENT SORT(ORDER BY) TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_IDX'

위와 같이 실행 계획이 생성되면 해당 SQL은 COL4+COL3 인덱스를 Full Scan하게 되므로 인덱스의 처음부터 끝까지 엑세스하게 된다. 이와 같이 수행되는 경우 일반적으로 성능 저하가 발생하게 될 것이다. 이는 처리 범위를 감소시키지 못하고 인덱스를 처음부터 끝까지 엑세스하기 때문이다. 그렇다면 인덱스 Full Scan은 단점만을 가지는 것일까? 세상사가 다 그렇듯이 단점이 있으면 장점이 있고 장점이 있으면 단점이 존재할 것이다. 인덱스 Full Scan도 마찬가지이다.

 인덱스도 Full Scan의 장점과 단점을 분석하자

 인덱스 Full Scan은 앞서 언급한 것과 같이 인덱스를 처음부터 끝까지 엑세스해야 한다는 단점이 존재한다. 앞의 예제에서 COL3 조건을 만족하는 데이터는 100건이며 TAB1 테이블의 전체 데이터는 100,000건이라면 100,000건의 데이터를 인덱스에서 모두 엑세스하여 99,900건은 버리고 100건만을 취하여 결과로 추출하게 되므로 비효율이 발생하게 될 것이다. 이와 같다면 인덱스 Full Scan이 성능 저하를 발생시키는 것은 자명한 일이다.  

- 처리 범위 증가로 인한 성능 저하 발생 가능

이처럼 인덱스 Full Scan은 성능 저하를 발생시킬 수 있는 구조임에는 틀림없다. 하지만 TAB1 테이블이 100,000건이며 조건을 만족하는 데이터가 90,000건이라고 가정하자. 또한, 인덱스는 COL4+COL3+COL2+COL1으로 구성되어 있다면 해당 인덱스를 이용하는 순간 인덱스 Full Scan이 발생하는 것은 동일하다. 하지만 인덱스를 Full Scan하므로 인덱스에서 추출되는 데이터의 순서로 데이터가 추출될 것이다. 인덱스는 현재 COL4 컬럼으로 정렬되어 있으므로 해당 인덱스를 이용하는 순간 자동으로 COL4 컬럼으로 정렬된 데이터가 결과로 추출된다. 이와 같다면 해당 SQL은 정렬을 발생시키지 않게 된다. 또한 인덱스에 필요한 모든 컬럼이 존재하므로 테이블을 별도로 엑세스할 필요가 없으며 엑세스한 데이터의 대부분이 결과로 추출되므로 인덱스를 처음부터 끝까지 엑세스한 데이터에 대한 낭비가 거의 없게 된다. 이와 같다면 인덱스 Full Scan이 정렬도 없애며 테이블보다 크기가 작은 인덱스를 엑세스하게 되어 성능을 향상시킬 수 있다. 이와 같이 실행된다면 실행 계획은 다음과 같이 생성될 것이다. 

SELECT STATEMENT TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' INDEX (FULL SCAN) OF 'COL4_COL3_COL2_COL1_IDX'

 인덱스 Full Scan은 해당 테이블의 데이터를 대부분 추출하는 경우 다음과 같은 장점을 가지게 된다.  

- 테이블보다 작은 인덱스를 전부 엑세스하므로 I/O 양을 감소시킴 - 인덱스의 첫 번째 컬럼으로 정렬된 데이터를 자동으로 추출

위와 같이 인덱스 Full Scan은 인덱스 자동 정렬과 테이블을 엑세스하는 현상만 없다면 더 작은 크기의 인덱스를 엑세스하므로 디스크 I/O에서 장점을 가지게 된다. 물론 해당 테이블의 데이터 중 대부분의 데이터를 정렬하여 추출하는 경우에 해당할 것이다.  

이처럼 인덱스 Full Scan은 단점이 크지만 잘 사용한다면 자동 정렬 등의 장점을 효과적으로 이용할 수 있을 것이다. 이러한 점에 유의하여 인덱스 Full Scan 실행 계획이 생성된다면 많은 분석을 통해 최적화를 해야 하며 인덱스 Full Scan의 장점을 이용하여 SQL을 최적화할 수도 있다. 다음 호에서는 인덱스 Full Scan 중 하나인 Index Fast Full Scan에 대해 함께 확인해 보자.

 필자소개
권순용 kwontra@hanmail.net｜Data Consulting 업무를 수행하는 ㈜엑시엄의 대표이사이며 DBA로 시작하여 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 수행했다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 『Perfect! 오라클 실전 튜닝, 『초보자를 위한 오라클 10g』 및 『INSIDE SQL』이 있다. 또한, 데이터 액세스 최적화에 대한 특허를 출원했다.

 출처 : 한국 마이크로 소프트웨어 [2009년 5월호]
제공 : DB포탈사이트 DBguide.net