튜닝이론 정리-2

인덱스를 경유해 데이터를 읽는 과정은 인덱스 수직적 탐색, 수평적 탐색 그리고 테이블 액세스 순으로 진행됩니다. 수직적 탐색은 인덱스 블록의 랜덤 엑세스를 통해 수평적 탐색의 시작점을 찾는 과정입니다.

 

랜덤 엑세스는 하나의 블록 전체를 읽어 조건을 만족하는 하나의 데이터 ROW를 얻고 나머지는 버립니다. 시퀀셜 엑세스는 한 블록 전체의 레코드를 모두 읽어오는 방식입니다.

 

인덱스 트리의 리프블록은 인덱스 구성 칼럼을 기준으로 정렬돼있어 수평적 스캔을 통해 범위에 포함되는 레코드를 순차적으로 읽을 수 있습니다.

 

TABLE FULL SCAN은 정렬 여부에 관계없이 모든 블록의 레코드를 읽습니다.

 

인덱스 스캔의 궁극적이 목적은 인덱스 리프블록 내의 ROWID를 찾는 것입니다. ROWID에는 데이터 레코드의 위치정보가 담겨있습니다.

수평적 탐색을 통해 조건에 맞는 인덱스레코드를 스캔하고 레코드내의 ROWID를 찾습니다. 인덱스 스캔시 TABLE RANDOM ACCESS는 인덱스 수평적 스캔의 결과에 해당하는 레코드의 수 만큼 발생합니다.

 

인덱스 튜닝의 핵심 원리는 수평적 스캔을 최소화하여 인덱스 선택도(범위스캔에서 테이블 엑세스까지 이어지는 인덱스의 비율)를 높이고 조건 칼럼을 최대한 인덱스에 포함시키고 버퍼피닝등 DBMS에서 제공하는 기능을 이용해 테이블 랜덤 엑세스의 발생을 최소화하는 것입니다.

 

검색조건에 해당하는 모든 칼럼이 인덱스에 포함돼있으면 테이블 랜덤 엑세스 비효율은 없습니다. 테이블에서 별도의 필터링이 발생하지 않고 스캔범위에 포함된 테이블 ROW 만을 읽습니다. 수평적 스캔의 인덱스 선택도를 최적화하는 방법은 인덱스 매칭도를 100%에 가깝게 만드는 것 입니다.

 

인덱스 루트 블록의 ROW 수는 하위 블록의 수 만큼 존재합니다. 루트블록과 브랜치블록의 각 레코드에는 매핑된 하위 블록키의 시작점과 종료점 그리고 Data Block Address가 담겨있습니다.

 

리프블록은 매핑된 테이블 레코드의 인덱스에 포함된 칼럼값으로 이루어진 키와 데이터 블록을 찾을 수 있는 ROWID로 이루어져 있습니다.
리프블록 레코드에 담긴 ROWID는 DBA(오브젝트 번호 + 파일 번호 + 블록 주소) + 로우 번호로 구성됩니다.

 

INDEX RANGE SCAN을 위해서는 조건절에 인덱스의 선두칼럼이 반드시 있어야 합니다. 수평적 스캔의 시작점을 결정해야하기 때문입니다. 인덱스는 전체 데이터의 10~20% 정도를 읽을 때 최적의 성능을 발휘할 수 있습니다. 인덱스 이용시 테이블 랜덤 엑세스가 발생하기 때문에 대량의 데이터를 읽는 조건에는 부적합합니다. 단, 비슷한 데이터가 모여있는 정도인 클러스터링 팩터가 높으면 손익분기점도 높아집니다.

 

수평적 스캔의 인덱스 선택도를 높이기 위해서는 필터조건 없이 모두 Driving 조건으로 인덱스 엑세스 해야합니다. 테이블 랜덤 엑세스를 최소화하기 위해서는 검색조건에 사용된 칼럼이 인덱스에 많이 포함돼있어야합니다. 즉, 인덱스에서 필터링이 완료되어 불필요한 TABLE ACCESS를 줄여야합니다.

 

INDEX FULL SCAN은 인덱스를 구성하는 선두칼럼은 조건절에 없지만 후위칼럼이 조건절에 존재할 때 모든 인덱스의 키를 읽어오기위해 사용합니다. INDEX UNIQUE SCAN은 유니크 인덱스를 = 조건으로 읽을 때 발생합니다.

 

INDEX SKIP SCAN은 인덱스를 구성하는 선두 칼럼에 대한 조건은 없지만 선두 칼럼의 DISTINCT가 낮을때 사용합니다. 선두칼럼을 = 조건이 아닌 범위조건으로 해도 사용 가능합니다.

 

INDEX FAST FULL SCAN은 테이블 랜덤 엑세스가 필요없는 상황(쿼리에서 사용된 모든 칼럼이 인덱스에 존재하는 경우)에서 세그먼트 단위로 인덱스 블록을 읽는 방식입니다. MULTI BLOCK READ 방식으로 읽기때문에 속도가 빠릅니다.

 

스캔 범위 내의 인덱스 리프노드를 통해 테이블을 읽을 때, 다음 번 READ에서 현재 읽은 동일한 DATA BLOCK을 리드할 경우, 현재 읽은 BLOCK이 AGE-OUT EH되지 않도록 핀을 걸어 해당 주소가 가리키는 메모리 번지수를 PGA에 저장하여 다음 접근시 즉시 찾아가는 기법을 버퍼 PINNING이라고 합니다.

 

리프노드의 키 순서대로 테이블 블록의 레코드가 모여있으면 버퍼피닝의 효과가 극대화 됩니다. 인덱스 순서와 동일하게 테이블 레코드가 모여있는 정도를 클러스터링 팩터라고 합니다. 클러스터링 팩터는 가장 자주 쓰는 인덱스에 맞게 최적화 되도록 테이블을 리빌드하면 큰 효과가 있습니다.

 

클러스터링 팩터를 높이기 위해서 클러스터드 인덱스를 활용할 수 있습니다. 특정 유니크 칼럼으로 인덱스를 구성하고 PK 칼럼이 같은 데이터는 같은 블록에 저장합니다. 리프블록에 ROWID 대신 실제 pk 값 을 보관하므로 세컨드리 인덱스로 조회하면 성능이 현저히 감소합니다.

오라클의 IOT는 리프노드에 테이블의 모든 칼럼의 값이 존재합니다. 인덱스 수직 + 수평 스캔만으로 데이터를 찾을 수 있습니다.

IOT는 기존 값들이 모두 정렬돼있어 새로운 레코드가 추가되면 인덱스 스플릿이 발생합니다. 스플릿을 통해 인덱스 로우가 저장되는 블록의 주소가 바뀝니다. 이로인데 ROWID가 바뀝니다. 이런 이유로 인덱스 리프노드에 ROWID를 보관하지 않고 KEY + pk 칼럼의 값을 보관합니다. 그렇기에 SECONDARY INDEX로 조회시 다시 수직적 탐색이 발생합니다.

 

조건절의 칼럼이 모두 인덱스 구성 칼럼이면 랜덤엑세스의 비효율은 없습니다. 테이블에서 필터링이 발생하면 불필요한 랜덤 엑세스로 인한 비효율이 발생합니다.

 

수평적 스캔의 선택도를 높이는 방법은 이넫그 선두 테이블을 = 또는 in 조건으로 조회하는 것입니다 . DRIVING 조건은 수평적 스캔의 시작점과 끝점을 결정합니다. 인덱스의 스캔 범위를 줄이는데 중요한 요소입니다. CHECK 조건은 스캔 범위에 속한 인덱스에 대해 랜덤 엑세스를 진행할지 결정하는 역할을 합니다.

 

인덱스 스캔효율

 

인덱스 매칭도

 

비교 연산자의 종류와 칼럼 순서에 따른 인덱스 레코드의 군집도, 인덱스 선행칼럼이 조건절에 없는 경우 인덱스 매칭도가 저하됩니다.

 

BETWEEN 조건을 IN-LIST-ITERATOR 로 변경했을때 인덱스 스캔효율

IN-LIST-ITERATOR

 

IN 조건의 수만큼 인덱스 수직적 탐색이 발생합니다. 인덱스의 높이가 높은 경우 BETWEEN 조건보다 효율이 떨어집니다. 드라이빙 조건의 변별력이 좋아 검색구간을 줄일 수 있다면 IN-LIST ITERATOR로 탐색하는 것보다 범위 스캔이 더 유리합니다. 인덱스 선행 칼럼에 대한 조건절의 좌변을 가공하면 인덱스를 사용할 수 없습니다. 인덱스 후행 칼럼을 가공하면 드라이빙 조건으로 사용할 수 있는 조건절이더라도 필터 조건으로 사용합니다.

 

INDEX SKIP SCAN을 이용한 비효율 해소

 

한달에 10만건의 데이터가 쌓이는 테이블에 판매구분이 a인 데이터가 1만건, b인 데이터가 9만건 쌓입니다. 이 테이블을 조회하는 쿼리의 조건절은 판매구분은 동차조건, 판매월은 BETWEEN 조건입니다. INDEX 는 판매월 + 판매구분 으로 구성돼있습니다.

 

SELECT
FROM T_판매이력
WHERE 판매구분 = 'A'
AND    판매월 BETWEEN '202111' AND '202211'

 

인덱스 선두칼럼인 판매월을 BETWEEN 조건으로 비교하면 RANGE SCAN이 발생합니다. 이때 판매구분이 b인 9만건의 데이터에 대한 비효율이 발생합니다.

이때 BETWEEN 조건을 IN으로 바꾸면 수직적 스캔을 늘어나지만 수평적 스캔이 현저히 감소합니다. 만약 INDEX SKIP SCAN으로 읽으면 IN-LIST ITERATOR 처럼 수직적 스캔이 발생하지 않아 근소하게 유리합니다.

 

INDEX SKIP SCAN의 조건은 인덱스 선두칼럼에 대한 조건절이 없고 선두 칼럼의 DISTINCT가 낮아야 유리하지만 선두 칼럼이 있더라도 = 조건으로 비교하지 않으면 INDEX SKIP SCAN이 가능합니다.

 

범위검색이 많을때 발생하는 비효율

 

-- INDEX (상품코드 + 주문일자 + 주문유형)

SELECT 상품코드, 주문일자, 주문유형
FROM 주문
WHERE 상품코드 = :PORD_CD
AND 주문일자 = :ORDER_DT
AND 주문유형 = :ORDER_TYPE

 

위 쿼리의 스캔 비효율은 없습니다. 조건절에 사용된 칼럼이 모두 DRIVING 조건이 되기 때문입니다.

 

-- INDEX (상품코드 + 주문일자 + 주문유형)
SELECT 상품코드, 주문일자, 주문우형
FROM 주문
WHERE 상품코드 = :PORD_CD
AND 주문유형 = :ORDER_TYPE

 

위 쿼리의 경우 인덱스 구성컬럼중 가운데 칼럼이 조건절에 존재하지 않아 후행 칼럼은 모두 인덱스 필터조건이 됩니다. 이 경우 인덱스 스캔 비효율이 발생합니다.

 

-- INDEX : 상품코드 + 주문일자 + 주문유형

SELECT 상품코드, 주문일자, 주문우형
FROM 주문
WHERE 상품코드 = :PORD_CD
AND 주문일자 LIKE :ORDER_DT || '%'
AND 주문유형 = :ORDER_TYPE

 

위 쿼리처럼 중간칼럼이 LIKE 조건인 경우 후행칼럼은 모두 FILTER 조건이 됩니다. 인덱스 스캔 비효율이 발생합니다.

위 쿼리는 아래와 같이 변경할 수 있습니다. 주문일자가 있는 레코드는 인덱스 스캔 비효율없이 조회할 수 있고 없는 경우는 어쩔 수 없는 비효율이 발생합니다 .

 

-- INDEX : 상품코드 + 주문일자 + 주문유형
-- 지역 조건 참여 여부에 따른 SQL 분기
-- 주문일자 조건이 있을 경우
SELECT 상품코드, 주문일자, 주문우형
FROM 주문
WHERE  :ORDER_DT IS NOT NULL
AND 상품코드 = :PORD_CD
AND 주문일자 = :ORDER_DT -- 선택조건
AND 주문유형 = :ORDER_TYPE
UNION ALL
-- 주문일자 조건이 없을 경우
SELECT 상품코드, 주문일자, 주문우형
FROM 주문
WHERE :ORDER_DT IS NULL
AND 상품코드 = :PORD_CD
AND 주문유형 = :ORDER_TYPE

 

주문일자 칼럼에 NOT NULL 제약이 있는 경우 다음과 같이 sql 단순화가 가능합니다. 바인드변수 :ORDER_DT 값이 NULL 이면 '주문일자 = 주문일자' 비교연산의 결과로 항상 'TRUE'를 리턴합니다. 하지만 주문일자 칼럼이 NULL을 허용하는 칼럼인 경우 실제 NULL인 레코드가 결과에 포함되지 않아 아래와 같은 쿼리는 사용할 수 없습니다.

 

-- 
SELECT 상품코드, 주문일자, 주문우형
FROM 주문
WHERE 상품코드 = :PORD_CD
AND 주문일자 = NVL(:ORDER_DT, 주문일자)
AND 주문유형 = :ORDER_TYPE

 

INDEX RANGE SCAN을 유도하면서 하나의 칼럼이 CHECK 조건일때 IN-LIST-ITERATOR를 선택할 것인지 아니면 FILTER 조건으로 읽도록 할 것인지 여부는 선두 칼럼의 변별력에 달려있습니다.

 

동일한 칼럼을 두개의 범위 검색조건으로 사용할 때 주의사항

 

값의 범위가 적은 조건을 드라이빙 조건으로, 넓은 조건을 필터 조건으로 하는 것이 유리합니다.

 

select *
from (
  --- 이전 조회와 도서명이 같은 경우
  select /*+ index(도서 도서명_idx) */
  rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
  From 도서
  where 도서명 = :last_book_nm
  and rowid > :last_rid
  union all
  --- 이전 조회보다 도서명이 큰 경우
  select /*+ index(도서 도서명_idx) */
  rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
  From 도서
  where 도서명 like :last_book_nm || '%'
  and 도서명 > :last_book_nm
)
where rownum <= 10;

 

LIKE 조건으로 조회하는 경우보다 부등호로 조회하는 경우의 스캔 범위가 적기때문에 등호조건의 칼럼을 DRIVING 조건으로 하는 것이 유리합니다.

 

BETWEEN과 LIKE의 스캔범위 비교

 

RANGE SCAN의 필터조건으로 쓰이는 칼럼의 조건으로는 BETWEEN이 더 유리합니다.

 

-- IDX : 판매월(D) + 판매구분코드(c)
SELECT count(*)
FROM 집계
WHERE 판매월 BETWEEN '201901' AND '201902' -- D
AND 판매구분 = 'A' -- C

 

BETWEEN을 사용한 경우 판매구분이 FILTER 조건이지만 BETWEEN의 시작점과 끝점에서는 판매구분이 DRIVING 조건처럼 동작합니다. 스캔의 범위를 줄이는데 기여했기 때문입니다.

 

select count(*)
from 집계
where 판매월 LIKE '2019%' -- D
AND 판매구분 = 'A' -- C

 

LIKE를 사용한 경우 판매구분이 동일하게 FILTER 조건이지만 판매월의 시작점과 끝점이 명확하지 않기때문에 판매구분이 스캔범위를 줄이는데 기여하지 못합니다.

 

선분이력과 인덱스 스캔 효율

 

SELECT *
FROM 주소변경이력
WHERE 고객번호 = 'C101'
AND '20190831' BETWEEN 시작일 AND 종료일

 

위와 같은 선분이력에서 시작일과 종료일 중 어느것을 인덱스 선두에 둘지는 검색일에 따라 달라집니다. 검색일이 최근이라면 종료일이 검색일인 레코드의 수가 적을 것이고 검색일이 예전시점이라면 시작일이 검색일인 데이터가 많을 것 입니다. 그러므로 최근 데이터를 읽을 때는 종료일 칼럼이 인덱스 선두로 오고 과거 데이터를 읽을 때는 시작일 칼럼이 선두로 오는것이 유리합니다. 인덱스 수정이 불가하다면 INDEX_DESC 힌트를 통해 뒤에서 읽는 방안을 고려할 수 있습니다.

 

B*TREE 인덱스 이상현상

 

B-TREE 인덱스에서는 unbalanced index 현상이 발생하지 않습니다. INDEX SKEW 현상은 대량의 데이터를 삭제한 후 발생하는 현상으로 빈 블록은 FREE-LIST로 등록은 되지만 저장공간을 반환하지는 않습니다. 이 현상을 해결하기 위해서는 INDEX-REBUILD가 필요합니다.

 

INDEX SPARSE는 대량의 삭제작업 이후 인덱스의 밀도가 낮아지는 현상입니다. 마찬가지로 INDEX-REBUILD가 필요합니다.

 

인덱스 분할에 의한 경합이 높을때나 NL 조인에서 반복적으로 엑세스 되는 인덱스 높이가 높아지면 인덱스 재생성이 필요합니다. 대량의 DELETE 작업 수행 후 재입력되기까지 긴 기간이 소요될때 혹은 일정한 레코드 수 임에도 인덱스가 계속 커질때 인덱스의 재생성이 필요합니다.

 

BITMAP 인덱스

 

DISTINCT VALUE의 수가 적은 칼럼에 유리한 인덱스 구성방식입니다. 용량이 적어 인덱스가 여러개 필요한 대용량 테이블에 적합합니다. 다양한 분석관점이 필요한 테이블에서 주로 사용합니다.

 

select *
from t
where 성병 = '여'
and 연령대 = '30대'
and 고객등급 = 'vip'
and 지역 = '서울'

 

비트맵 인덱스 사용시 OR 연산을 하더라도 부하가 발생하지 않습니다.

 

함수기반 인덱스

 

SELECT * FROM 주문
WHERE NVL(주문수량, 0) < 100

CREATE INDEX EMP_X01 주문 (nvl(주문수량, 0));

 

주문 수량이 NULL인 레코드의 값을 0으로 하는 인덱스를 생성합니다. 대소문자를 구분해서 입력받은 데이터를 구분없이 조회할때 흔히 사용됩니다. 데이터 입력, 수정시 함수를 적용해야 함으로 부하가 발생합니다. 특히 사용자 생성함수일 경우 더 큰 부하가 발생합니다.

 

리버스 인덱스

 

입력값이 순차적인 경우 인덱스 블록경함으로 초당 트랜잭션 처리량이 감소합니다. 이때 입력값을 거꾸로 저장하여 데이터를 고르게 분포하도록 합니다. = 검색만 사용가능합니다.

 

조인

 

NESTED LOOP JOIN

 

iNNER 테이블의 인덱스에 조인 기준 칼럼이 반드시 존재해야 합니다. 조인과정에서 인덱스를 사용해 INNER 테이블에 랜덤 엑세스 합니다. NL 조인은 한 레코드씩 순차적으로 진행합니다. 인덱스 스캔이 많이 발생하므로 대용량 처리시 한계가 있습니다. 그렇기때문에 NL 조인은 OLTP 성 쿼리에 적합합니다.

 

PREFETCH

 

-- nl 조인시 전통적인 실행 계획
--- EXECUTION PLAN

-- NESTED LOOPS
--  TABLE ACCES OF DEPT
--    INDEX RANGE SCAN OF I_DEPT
--  TABLE ACCES OF EMP
--    INDEX RANGE SCAN OF I_EMP

-- PREFETCH 작동시 실행 계획

-- TABLE ACCES OF EMP
--  NESTED LOOPS
--    TABLE ACCES OF DEPT
--      INDEX RANGE SCAN OF I_DEPT
--    INDEX RANGE SCAN OF I_EMP

 

SORT MERGE JOIN

 

조인 기준 칼럼을 정렬기준으로 두 테이블을 PGA에 정렬합니다. 정렬후 NL 조인과 동일한 방식으로 조인을 진행합니다. PGA 영역에서 동작하므로 경합이 없고 속도가 빠릅니다. 조인 칼럼이 인덱스에 포함되있는지와 관련 없습니다.

 

HASH JOIN

 

두 테이블 중 조인 대상레코드의 수가 적은 레코드를 읽어 PGA에 있는 HASH AREA에 적재합니다. 이를 BUILD INPUT이라 합니다.
그리고 반대쪽 큰 집합을 읽어 HASH AREA에 한 건씩 대입하면서 조인합니다. 이를 PROBE INPUT이라 합니다. BUILD INPUT의 조인기준칼럼 값을 해싱한 값과 PROBE INPUT의 조인기준칼럼을 해상한 값이 일치하면 조인합니다.

 

HASH JOIN은 조인기준칼럼을 등치조건으로 비교하는 경우만 사용가능합니다. 해시조인은 랜덤엑세스가 발생하지 않습니다. 이미 PGA에 올라온 레코드끼리 비교하기 때문에 빠른 탐색이 가능합니다.

 

BUILD INPUT이 HASH AREA에 담길 정도로 크기가 작아야합니다. OVERFLOW 시 디스크 공간인 TEMPORARY SPACE를 이용합니다.

 

BUILD INPUT의 조인기준 칼럼은 중복이 없어야합니다.

 

HASH 조인은 조인컬럼에 대한 인덱스가 없어 NL 조인이 비효율적인 경우 유용합니다. 또한 NL 조인시 OUTER TABLE에서 INNER 테이블로 조인 엑세스량이 많아서 RANDOM ACCESS 부하가 심할때 유용합니다.

 

소트머지조인하기에 두 테이블이 너무 커서 부하가 예상되는 경우와 대용량 테이블을 조회할 때도 유용합니다. 단, CPU와 메모리 사용률이 크게 증가하기 때문에 수행빈도가 높고, OLTP 환경하에 성능향상을 위한 용도로 사용해선 안됩니다.

 

SELECT  /*+USE_HASH(D E)*/
D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME
FROM DEPT.D, EMP.E
WHERE D.DEPTNO = E.DEPTNO

-- 0  SELECT STATEMENT
-- 1      HASH JOIN
-- 2        TABALE ACCESS FULL DEPT
-- 3        TABLE ACCESS FULL  EMP

 

HASH JOIN 관련 힌트

 

 

USE_HASH(BUILD INPUT, PROBE INPUT)

 

HASH 조인 실행 계획

 

-- MERCH - APPR - CARD - PROB 
                -- CUST    
SELECT
/*+
    LEADING(APPR CARD MERCH PROB CUST)
    USE_HASH(CARD) USE_HASH(MERCH) USE_HASH(PROB) USE_HASH(CUST)
    SWAP_JOIN_INPUTS(APPR)         -- BUILD INPUT
    NO_SWAP_JOIN_INPUTS(CARD)    -- PROBE INPUT
    NO_SWAP_JOIN_INPUTS(MERCH)     -- PROBE INPUT
    NO_SWAP_JOIN_INPUTS(PROB)    -- PROBE INPUT
    NO_SWAP_JOIN_INPUTS(CUST)    -- PROBE INPUT
*/
*
FROM T_CUST CUST, T_PROD PROD, T_CARD CARD, T_MERCH MERCH, T_APPR APPR
WHERE APPR.APPR_DT BETWEEN '20200701' AND '20200702'
AND MERCH.MERCH_NO = APPR.MERCH_NO
AND CARD.CARD_NO = APPR.CARD_NO
AND PROB.PROD_NO = CARD.PROD_NO
AND CUST.CUST_NO = CARD.CUST_NO

-- EXECUTION PLAN
    HASH JOIN
      HASH JOIN
        HASH JOIN
          HASH JOIN
              INDEX RANGE SCAN OF APPR_01 INDEX
              TABLE ACCESS FULL OF T_CARD TABLE
          TABLE ACCESS FULL OF T_MERCH
        TABLE ACCESS FULL OF T_PROB
      TABLE ACCESS FULL OF T_CUST

SELECT
/*+
    LEADING(APPR CARD MERCH PROD CUST)
    USE_HASH(CARD) USE_HASH(MERCH) USE_HASH(PROD) USE_HASH(CUST)
    SWAP_JOIN_INPUTS(APPR)         -- BUILD INPUT
    SWAP_JOIN_INPUTS(MERCH)     -- BUILD INPUT
    SWAP_JOIN_INPUTS(PROB)        -- BUILD INPUT
    SWAP_JOIN_INPUTS(CUST)        -- BUILD INPUT
    NO_SWAP_JOIN_INPUTS(CARD)     -- PROBE
*/
*
FROM T_CUST CUST, T_PROD PROD, T_CARD CARD, T_MERCH MERCH, T_APPR APPR
WHERE APPR.APPR_DT BETWEEN '20200701' AND '20200702'
AND MERCH.MERCH_NO = APPR.MERCH_NO
AND CARD.CARD_NO = APPR.CARD_NO
AND PROB.PROD_NO = CARD.PROD_NO
AND CUST.CUST_NO = CARD.CUST_NO

-- EXECUTION PLAN
    HASH JOIN
      TABLE ACCESS FULL OF T_CUST
      HASH JOIN
        TABLE ACCESS FULL OF T_PROD
        HASH JOIN
          TABLE ACCESS FULL OF T_MERCH
          HASH JOIN
              INDEX RANGE SCAN OF APPR_01
              TABLE ACCESS FULL OF T_CARD 

 

실행계획과 실제 순서가 다른 경우

 

- SELECT 절의 SCARLA SUBQUERY
- HASH 조인의 계단형 실행계획

 

테이블 실행 우선 순위 지정 힌트

- LEADING(A,B,C)
- ORDER : 테이블에 선언된 순서대로

 

조인방식지정힌트

 

- USE_NL(LOOKUP_TABLE)
- USE_MERGE(PROBE_INPUT)
- USE_HASH(PROBE_INPUT)

 

조인순서의 중요성

 

NL 조인

 

-- INDEX_EMP(SAL)
SELECT *
FROM EMP E, DEPT D
WHERE E.SAL > 1500
AND E.JOB = 'MANAGER'
AND D.DEPTNO = E.DEPTNO;

 

NL 조인은 조건절에서 필터된 레코드 수가 적은 쪽에서 큰쪽으로 조인하는것이 유리합니다.

 

SORT MERGE JOIN

 

두 조인테이블이 모두 PGA SORT AREA에 담기는 경우 작은 테이블이 DRIVING 되는거것이 유리하지만, PGA가 OVERFLOW로 DISK SORT가 필요한 경우 오히려 큰 테이블이 DRIVING 되는 것이 유리합니다.

 

HASH JOIN

 

HASH AREA에 충분히 담길정도로 적은 레코드의 테이블이 DRIVING 되는것이 유리합니다.

 

select *
from T1, T2
WHERE T1.C1 = 'A'
AND T2.C11 = T1.C11
AND T2.C2 = 'A'

 

조건에 해당하는 테이블의 레코드 수가 적은 쪽이 DRIVING 테이블이 돼야합니다. DRIVING 테이블의 인덱스에는 조인기준 칼럼이 포함되있지 않아도 상관없지만 PROBE 테이블에는 포함되어 있는것이 유리합니다.

 

OUTER 조인

 

OUTER NL 조인

 

SELECT *
FROM EMP E, DEPT D
WHERE E.SAL > 5000
AND E.DEPTNO = D.DEPTNO(+)

SELECT *
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO

-- EXECUTION PLAN
SELECT STATEMENT
    NESTED LOOPS(OUTER)
        TABLE ACCESS BY INDEX ROWID OF EMP(TABLE)
            INDEX RANGE SCAN OF IDX_EMP(INDEX)
        TABLE ACCESS BY INDEX ROWID OF DEPT(TABLE)
            INDEX RANGE SCAN OF IDX_DEPT(INDEX)

 

OUTER 테이블은 항상 DRIVING 테이블로 동작하기 때문에 힌트를 통해 강제로 LOOK_UP 테이블로 지정할 수 없습니다. 그 이유는 OUTER TABLE이 LOOKUP TABLE이 되면 DRIVING 테이블과 조인되지 못한 레코드를 보여줄 방법이 없기 때문입니다.

 

// 불가
SELECT /*+LEADING(D) USE_NL(E)*/
*
FROM EMP E, DEPT D
WHERE E.SAL > 5000
AND E.DEPTNO = D.DEPTNO(+)

 

OUTER SORT MERGE JOIN

 

소트머지조인또한 힌트를 통해 OUTER TABLE을 LOOKUP TABLE로 강제로 변경할 수 없습니다.

 

OUTER HASH JOIN

 

SELECT /*+LEADING(E) USE_HASH(D)*/
*
FROM EMP E, DEPT D
WHERE D.DEPTNO(+) = E.DEPTNO

 

OUTER 테이블의 레코드 전체를 hash 테이블에 적재합니다. 조건에 맞는 INNER TABLE을 읽어 OUTER 테이블과 조인 성공한 레코드를 적재합니다. 다음으로 OUTER TABLE에서 조인에 실패한 레코드를 적재합니다.

 

RIGHT OUTER HASH JOIN

 

SELECT /*+LEADING(D) USE_HASH(E) SWAPT_JOIN_INPUTS(D)*/
*
FROM EMP E, DEPT D
WHERE D.DEPTNO(+) = E.DEPTNO

-- EXECUTION PLAN
SELECT STATEMENT
    HASH JOIN (RIGHT OUTER)
        TABLE ACCESS(FULL) OF DEPT(TABLE)
        TABLE ACCESS(FULL) OF EMP(TABLE)

 

INNER 집합인 DEPT를 BULID INPUT으로 읽어오고 OUTER 집합인 EMP로 조인합니다. 조인 성공여부와 상관없이 모두 결과집합에 포함합니다. 이처럼 HASH JOIN은 INNER 테이블을 build input으로 사용할 수 있습니다. 이를 RIGHT OUTER JOIN 방식이라고 합니다.

 

스칼라 서브쿼리

 

스칼라 서브쿼리는 한 칼럼에 대응되는 결과를 조회하는 쿼리입니다. 주로 SELECT 절에 사용됩니다.

 

SELECT EMPNO, DNAME, SAL, HIREDATE
, (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) DNAME
FROM EMP E
WHERE SAL >= 2000

 

스칼라 서브쿼리는 캐싱됩니다. [메인쿼리에서 참조하는 칼럼, 서브쿼리칼럼의 값] 형태로 캐싱됩니다. 메인쿼리의 레코드가 대량일 경우 스칼라서브쿼리를 사용하지 않고 OUTER 조인으로 변환할 수 있습니다.

 

Bypass_ujvc 힌트

 

1:M 관계를 조인하면 결과는 항상 M 레벨입니다. 이때 M 레벨의 결과 테이블에는 1 Level table의 unique key가 중복됩니다.

이때 1 LEVEL의 table을 비 키-보존 테이블, M_LEVEL의 테이블을 키-보존 테이블이라고 합니다. 키-보전 테이블은 업데이트 할 수 있지만 비 키-보전 테이블은 업데이트 할 수 없습니다.

 

조인 연결칼럼으로 GROUP BY 한 후 생기는 뷰에대한 업데이트는 가능합니다.

 

bypass_ujvc(bypass updatable join view check) : 수정가능 뷰 체크 생략

 

조인 응용

 

누적매출 구하기

 

SELECT T1.지점, T1.판매월, T1.매출액, T2.판매월, T2.매출액
FROM 매출 T1, 매출 T2
WHERE T2.지점 = T1.지점
AND T2.판매월 <= T1.판매월
ORDER BY T1.지점, T1.판매월, T2.판매월;

 

T1의 판매월을 기준으로 기준월보다 작은 T2의 판매월을 구합니다.

 

SELECT T1.지점, T1.판매월, MIN(T1.매출액) 당월 매출액, SUM(T2.매출액) 누적매출액
FROM 매출 T1, 매출 T2
WHERE T2.지점 = T1.지점
AND T2.판매월 <= T1.판매월
GROUP BY T1.지점, T1.판매월

 

oracle 8i 부터 제공하는 WINDOW 함수를 활용하면 좀 더 쉽게 누적합을 구할 수 있습니다.

 

SELECT 지점, 판매월, 매출액
SUM(매출액) OVER(PARTITION BY 지점 ORDER BY 판매월 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적매출
ㄹFROM 매출

 

윈도우함수

 

ROW_NUMBER, SUM, MAX, MIN, AVG, COUNT등 집계함수의 계산 범위를 지정합니다.

 

- ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 10 PRECEDING AND UNBOUNDED FOLLOWING
- CURRENT ROW AND 10 FOLLOWING

 

SELECT SUM(SAL) OVER() -- 전체범위 합
SELECT SUM(SAL) OVER (PARTITION BY) -- PARTITION 범위별 합
SELECT ROW_NUMBER() OVER (PARTITION BY ORDER BY ROW BETWEEN UNBOUNDED PREDEDING AND CURRENT ROW)