하드웨어적 방법을 통해 I/O의 성능을 높이는 것도 좋지만, 애플리케이션 측면에서 논리적인 I/O의 요청 횟수를 최소화하는 것이 I/O효율화 튜닝의 핵심이다.
SELECT *
FROM (
SELECT ROWNUM NO, 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명, 아이콘, 댓글개수
FROM (
SELECT A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, FUNC_ICON(D.질문유형코드) 아이콘, (SELECT..) 댓글개수
FROM 게시판 A, 회원 B, 게시판유형 C, 질문유형 D
WHERE A.게시판유형 = :TYPE
AND B.회원번호 = A.작성자번호
AND C.게시판유형 = A.게시판유형
AND D.질문유형 = A.질문유형
ORDER BY A.등록일자 DESC, A.질문유형, A.번호
)
WHERE ROWNUM <= 30
)
WHERE NO BETWEEN 21 AND 30
SELECT /*+ ORDERED USE_NL(B) USE_NL(C) USE_NL(D) */
A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, FUNC_ICON(D.질문유형코드) 아이콘, (SELECT..) 댓글개수
FROM (
SELECT A.*, ROWNUM NO ROWNUM NO
FROM (
SELECT 등록일자, 번호, 제목, 작성자번호, 게시판유형, 질문유형
FROM 게시판
WHERE 게시판유형 = :TYPE
AND 작성자번호 IS NOT NULL
AND 게시판유형 IS NOT NULL
AND 질문유형 IS NOT NULL
ORDER BY 등록일자 DESC, 질문유형, 번호
) A
WHERE ROWNUM <= 30
)A 회원 B, 게시판유형 C, 질문유형 D
WHERE A.NO BETWEEN 21 AND 30
AND B.회원번호 = A.작성자번호
AND C.게시판유형 = A.게시판유형
AND D.질문유형 = A.질문유형
(예2) 거래현황 집계 쿼리(p484)
사용자 의도대로 블록 액세스를 최소화하면서, 효율적인 쿼리 프로세싱을 할 수 있도록 최적의 옵티마이저 팩터를 제공해 주어야 한다.
1. 테이블 t 생성
2. owner, created에 대해 t_idx 인덱스 생성
3. t 테이블에 대한 통계정보 수집
4. alter session set optimizer_mode = 'ALL_ROWS';
SQL> select * from t
where owner = 'SYS'
order by created;
---------------------------------------------------------------------
Id Operation Name Rows Bytes Cost(%CPU)
---------------------------------------------------------------------
0 SELECT STATEMENT 1921 174K 202(2)
1 SORT ORDER BY 1921 174K 202(2)
2 TABLE ACCESS FULL T 1921 174K 202(2)
---------------------------------------------------------------------
=> 정렬된 결과 집합 전체를 Fetch할 것이므로 거기에 따라 최적화를 수행하도록 '목적'을 밝힘
옵티마이저는 owner, creted 순으로 정렬된 인덱스가 있더라도 그것을 사용하지 않고 테이블을 풀스캔한후에 정렬하는 방식을 택함
테이블 랜덤 액세스의 부하를 최소화
5. alter session set optimzer_mode = 'FIRST_ROWS';
SQL> select * from t
where owner = 'SYS'
order by created;
---------------------------------------------------------------------
Id Operation Name Rows Bytes Cost(%CPU)
---------------------------------------------------------------------
0 SELECT STATEMENT 1921 174K 1870(1)
1 TABLE ACCESS BY INDEX ROWID T 1921 174K 1870(1)
2 INDEX RANGE SCAN T_IDX 1921 8(0)
---------------------------------------------------------------------
=> 전체 결과 집합에서 처음 일부 레코드만 Fetch하다 멈출 것임을 옵티마이저에게 밝힘
옵티마이저는 랜덤 액세스가 많지 않을 것으로 믿고 정렬 부하를 없애는 방식을 택함
owner, created 순으로 정렬된 인덱스를 이용하면 정렬 작업을 따로 수행하지 않아도 됨
이 상태로 쿼리 결과를 끝까지 Fetch한다면 풀스캔으로 처리할때보다 더 느려지고 시스템 리소스도 낭비하는 결과를 초래하므로 옵티마이저 모드 설정은 매우 중요