반응형
인덱스가 사용되지 않는 경우
1. 인덱스 컬럼이 비교되기 전에 변형(Suppressing)이 일어나는 경우
2. 인덱스 컬럼이 부정형(not, <>)으로 조건을 기술한 경우
3. 인덱스 컬럼이 null로 비교되는 경우
4. 1,2,3의 원칙을 준수했음에도 경우에 따라 옵티마이저가 필요에 따라 사용하지 않는 인덱스가 생길 수 있음
서로 다른 Type 컬럼 비교 시 변형 유형
문자 = 숫자
문자 Type이 숫자 Type으로 변형 됨. 마치, to_number(문자) = 숫자와 동일한 기능을 DBMS 내부적으로 수행
숫자 like '문자%'
문자% 가 숫자 Type으로 변형될 수 없으므로 숫자 Type이 문자 Type으로 변형
NOT IN
Col NOT IN (SubQuery...) 에서 SubQuery는 항상 나중에 수행되거나 Filtering 조인방식으로
수행된다.
NULL
null은 인덱스에 저장되지 않는다. 따라서 [index column] is not null 로 조건 절에 기술하는 것은
[index column] > ‘’ or [index column] > 0 과 같이 바꿔도 같은 결과가 나오며, full scan하던 것을
Index scan 하게 되므로 속도 향상도 꾀할 수 있다.
단, rule based 옵티마이저 모드이거나, cost based 옵티마이저 모드라면 first_rows로 설정되었을 때
가능하다. all_rows로 되어있다면 full scan을 하게 된다.
옵티마이저의 취사 선택
인덱스 머지를 피하기 위해 조건순위에 따른 취사선택, 힌트, 통계정보, 옵티마이저 모드 등에 따라
원칙을 준수했음에도 인덱스를 사용하지 않는 경우가 발생할 수 있다.
1. 순위(ranking) 차이
col1 = ‘1’ and col2 like ‘a%’ (col1, col2 각각 index) 인 경우에 rule based 옵티마이저에서는
= 이 like 보다 순위가 높으므로(= 로 비교했을 때가 분포도가 더 좁다고 판단하기 때문. 똑똑한넘-_-)
col1 index만 사용하고 col2는 비교 조건으로 사용한다. 하지만 cost based 옵티마이저에서는
순위가 낮더라도 like의 분포도가 더 좋다면 col2 index를 사용한다.
col1 = ‘1’ and col2 = ‘a’ 인 경우 rule based 옵티마이저에서는 인덱스 머지가 일어난다.(두 컬럼의
순위가 동일하므로)
그렇다면... col1 like ‘1%’ and col2 like ‘a%’ 인 경우 rule based 옵티마이저에서는 나중에 생성된
인덱스를 사용하고(범위가 넓어진다고 생각되는 like, >, <, between 등의 비교 연산자와 같이 사용될
경우에는 두 가지 중 한가지만 사용하고 나머지는 포기한다), cost based 옵티마이저에서는 통계정보에 의해서 인덱스를 선택한다.
2. 낮은 비용의 선택
인덱스가 있더라도 cost based 옵티마이저일 때는 all_rows냐 first_rows냐에 따라서 인덱스 사용여부가 결정된다.
3. 힌트(Hint)에 의한 선택
힌트를 사용하여 사용자가 원하는 실행계획을 유도할 수 있다. 하지만 힌트는 어디까지나 훈수역학일뿐... 옵티마이저가 힌트를 사용하여 더 많은 비용이 들 것을 판단하면 힌트를 무시하는 경우도 있다.
(호.. 좀 똑똑한걸..-_-?)
인덱스 생성
알다시피 인덱스 생성은 중요하고(-_ㅡ;) 10~15% 이하의 분포도를 가지는 컬럼에 대해서만 인덱스를
생성하는 것이 바람직하다. 또한 여러 개의 인덱스가 사용된다는 것은 인덱스들 간에 먼저 비교하여
같은 rowid를 가진 것만 찾아 테이블의 row를 access하는 『인덱스 머지』가 발생하기 때문에 바람직
하지 않다.
인덱스 머지는 scan 방식이므로, 때에 따라서는 index로 테이블 랜덤액세스를 하는 것보다 효율적일 수도
있다. 결합인덱스는 인덱스 머지의 결과 중 성공한 것만 모아놓은 것과 같은 결과이다.
결합인덱스 선정 시 주의사항
결합인덱스에서 비록 처리 범위를 줄여주지 못하는 컬럼이 포함되어 있더라도, 테이블 액세스를 줄일 수 있으
므로(인덱스에 포함된 컬럼이 조건으로 들어오면 구지 테이블을 읽지 않고 비교가 가능하다) 결합인덱스
컬럼 선정에 신중해야 한다.
또, 항상사용되어지는 컬럼이 선행되었는지 ‘=’로 비교되는지 여부도 중요하다.
인덱스 선정
1. 해당테이블의 모든 액세스 형태 수집
- 반복 수행되는 액세스 형태 찾기
- 분포도가 양호한 컬럼들 액세스 유형 조사
- 자주 넓은 범위 조건이 부여되는 경우 찾기
- 조건에(WHERE절에..) 자주 사용되는 주요 컬럼의 액세스 유형 조사
- 자주 결합되어 사용되는 컬럼들의 조합 및 정렬 순서 조사. 결합인데스 선정 시에는 연결순서를
확실히 한다.(결합도[어떤 컬럼이 더 자주 결합되어 사용되는지..], 결합조건[ex. ‘=’, ‘LIKE’ ...],
정렬순서)
- 역순으로 추출되는 경우. 넓은 범위를 역순으로 보여주기 위해서는 집계테이블로도 무리인
경우가 있다. 이때에는 INDEX_DESC를 이용하여 처리하는 수밖에 없다.(부분범위처리)
- 일련번호를 부여하는 경우 찾기
- 통계자료추출을 위한 액세스 유형조사. 통계자료를 위해서는 넓은 범위처리가 불가피하다.
따라서 클러스터링이나 잘 조합된 결합인덱스로 해결해야 한다.
2. 반복되는 액세스 형태 찾기
1000번 반복되는 경우 0.02초를 0.01초만 줄일 수 있어도 엄청난 속도 향상을 가져온다.
인덱스 선정 시 가장 중요한 요인은 항상 사용되어지는 가이다. 분포도가 아무리 좋더라도
항상 사용되어지지 않으면(ex. 일련번호..) 의미가 없다.
SALENO, ITEM(SALENO가 분포도가 더 좋다고 가정) 각각이 모두 자주 사용되고, SALENO+ITEM도
자주 사용되는 경우, 인덱스선정은 분포도가 더 좋은 SALENO를 선행으로 하는 SALENO+ITEM
결합인덱스를 생성하고 ITEM만 인덱스는 생성하지 않는다.
왜냐하면, WHERE SALENO = ‘1’ AND ITEM=’123’으로 조건이 들어온 경우 SALENO+ITEM인덱스를
사용하지 않고 ITEM만 있는 단독 인덱스를 사용할 수도 있기 때문이다. ITEM은 ITEM과 자주 사용
되는 컬럼과 결합인덱스로 생성해야 한다.
3. 클러스터링 검토
클러스터링할 컬럼은 인덱스와는 달리 분포도가 넓은 것이 오히려 유리하며, 좋은 분포도의 컬럼을
클러스터링하면 효과가 없다. 검색속도 향상에만 좋고 입력,수정,삭제는 부하가 증가하는 단점도 있다.
특히 하나의 트랜잭션에 다량의 데이터가 처리되는 경우는 많은 부하가 발생하므로 이런 애플리케이션에서는 클러스터링을 사용하지 않는 것이 바람직하다.
4.
5.
!! 인덱스 선정은 해당 테이블에 적용되는 모든 조회조건을 선정하여 최소의 인덱스로 최대의 효과를 내는 인
덱스들을 찾는 것이다. 클러스터링, 결합인덱스 등을 최대한 고려하고, 인덱스 우선순위(규칙기준옵티마이저
의 경우 결합인덱스가 있더라도 단독으로 사용되는 인덱스가 있으면 결합인덱스를 사용하지 않는다.)를 잘
판단하여 결정해야 한다.
조인 순서
처리범위란 주어진 조건을 만족하는 Row수(결과Set)만을 의미하는 것이 아니라, 그 조건의 범위를 찾는데
사용되는 주처리 영역(Main Driving)의 액세스 범위를 의미한다. 즉, 주어진 조건을 만족하는 결과가 전체
테이블의 1/100이더라도 인덱스가 없어 전체테이블을 읽었다면 처리범위는 전체테이블이 되는 것이다.
Nested Loop 조인에서는 가장 먼저 수행되는 집합의 처리범위가 전체 일량을 좌우한다. 나중에 수행되는
집합은 먼저 수행되는 결과에 종속적이다.
조인 성공률
세 개 이상 조인하는 경우 조인 성공률이(성공한 Row수) 낮은 집합의 조인이 먼저 일어나는 것이 유리하다.
Driving Table이 동일해도 두 번째로 처리되는 테이블에 따라 처리 일량은 천지차이이다.
예를 들어, A 테이블의 모두가 조인에 성공한다고 가정했을 경우 A : B = 1 : 10 이고, A : C = 1 : 1 의 분포도
를 가질 때 A → B → C 보다 A → C → B 의 연결이 최대 10배 이상 일량을 줄일 수 있다.
연결고리상태
1. 연결고리정상 : 연결되는 컬럼 모두에 인덱스가 있는 경우
A → B, B → A 모두 동일한 액세스 횟수이다. A : B = 1 : M 이더라도 인덱스를 통하여
액세스 되므로 처리일량은 같다. 단, 연결고리가 정상인 상태에서는 먼저 처리범위를 줄여
주는 테이블이 먼저 처리되면 수행속도는 향상된다.
예를들어, (아래의 SQL의 결과는 2row 이다)
SELECT a.FLD1, .., b.FLD1, …
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND a.FLD2 LIKE ‘ABC%’ → 만족하는 Row Count = 200
AND a.FLD1 = ‘10’ → 만족하는 Row Count = 10
인 경우 TAB1 이 먼저 처리된 경우 a.FLD2 LIKE ‘ABC%’ 조건에서 200row의 결과가 Return 되므로
최소 200회 이상을 처리해야 한다.(TAB2와의 연결에서 아무 결과가 없더라도..) 하지만, TAB2를
먼저 수행한 경우에는 a.FLD1 = ‘10’ 에서 10row가 Return되므로 최대 20회만 처리하면 된다.
엄청난 차이다!
2. 한쪽 연결고리 이상 : 어느 한쪽의 연결고리에 인덱스(or 클러스터)가 없는 경우
- 인덱스가 있는 쪽을 먼저 읽으면 대응되는 값을 찾기 위해 인덱스가 없는 쪽을 매번
Full Scan 해야 한다. 따라서, 옵티마이저는 한쪽 연결고리 이상일 경우 인덱스가 없는 쪽을
먼저 읽는다. 언뜻 보면 큰 문제 없어 보이지만, 연결고리정상인 상태였다면 처리범위가 적은
쪽을 먼저 읽도록 실행계획이 수립될 것이나 연결고리이상 상태이므로 인덱스가 없는 쪽을
먼저 읽도록 실행계획이 고정되어 버리게 된다.
- 인덱스가 있더라도 컬럼에 변형이 일어나는 경우에는 연결고리 이상과 같은 결과를 초래한다.
데이터 타입이 서로 다른 컬럼을 조인하는 경우에도 내부적인 컬럼 변형이 일어나므로 설계 시
컬럼 정의에도 유의하여야 한다.
3. 양쪽 연결고리 이상 : 양쪽 연결고리 모두에 인덱스(or 클러스터)가 없는 경우
어느 방향으로 조인을 하더라도 문제가 되므로 옵티마이저는 Sort Merge 방식으로 실행계획을
수립한다.
조인의 효율
연결고리 상태, 액세스의 방향, 인덱스의 구성이 매우 중요한 결정요인이다.
최적화 측면에서 Nested Loop 조인은 First_Rows 이고 Sort Merge 조인은 All_Rows 이다.
Nested Loop Join
어떤 테이블의 처리범위를 하나씩 액세스 하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식
특징
1. Driving Table의 처리범위에서 순차적으로 수행되며 테이블간 연결도 순차적이다.
2. 먼저 액세스 되는 테이블의 처리범위에 의해 처리량이 결정된다. 선행적이다.
3. 나중에 처리되는 테이블은 스스로의 상수 값에 의해서 처리범위를 줄이는 것이 아니라,
값을 받아서 처리범위가 정해진다. 종속적이다.
4. Driving Table의 첫 Row는 랜덤액세스 이고 나머지는 Scan 방식이지만, 연결작업은 모두
랜덤액세스이다.
5. WHERE 절에 있는 모든 컬럼에 인덱스가 있더라도 모두 인덱스가 사용되는 것은 아니다.
연결되는 방향에 따라 인덱스들이 전혀 달라질 수 있다. Check 조건으로 사용되는 경우도 있다.
선택적이다.
6. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 영향을 끼친다.
연결고리 상태, 방향성
7. 부분범위처리가 가능하므로 마지막으로 Check 되는 조건의 범위가 넓을수록 아예 없다면
더욱 빨라진다.
사용기준
1. 부분범위처리를 하는 경우에 유리하다.
2. 조인되는 어느 한쪽이 상대방 테이블에서 추출되는 결과를 받아야 처리범위를 줄일 수 있는
상태라면 항상 유리하다.
3. 주 처리 방식이 랜덤액세스이므로 처리량이 적은 경우에 유리하다.
4. 연결고리 이상상태가 아닌 경우이어야 한다.
5. 순차적으로 처리되기 때문에 Driving Table이 수행속도에 많은 영향을 미친다.
6. 부분범위처리를 하는 경우 운반단위의 크기가 수행속도에 많은 영향을 미칠 수 있다.
운반단위가 적을수록 빨리 운반단위를 채울 수는 있으나, Fetch 횟수에서는 불리해지는 이중성을
가진다.
7. Driving Table의 처리범위가 많거나 연결 테이블의 랜덤액세스 양이 많다면 Sort Merge보다
불리해진다.
Sort Merge Join
양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔 하면서 연결고리의 조건으로 Merge해
가는 방식. 예를 들어... (모든 컬럼에 인덱스가 있다고 가정)
SELECT a.FLD1, ... , b.FLD2, ...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2 AND a.FLD1 = ‘AB’ AND b.FLD2 = ‘10’ 인 SQL이 Sort Merge Join으로 실행될 경우 a.FLD1과 b.FLD2만 인덱스를 사용하고 연결고리가 되는 KEY1, KEY2는 인덱스를 전혀 사용하지 않고
단지 Merge 조건으로만 사용한다. 때에 따라 아주 불리할 수도...
특징
1. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다. 동시적이다.
2. 다른 테이블에서 어떠한 상수 값도 제공받지 않는다. 즉, 자신에게 주어진 상수 값으로만 처리범위를
줄인다. 독립적이다.
3. 항상 전체범위처리만 가능하다.
4. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우를 제외하고는 모두 Scan방식으로 처리된다.
5. 위에서 언급했듯이 주어진 조건에 있는 모든 컬럼이 인덱스를 사용하는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 사용하지 않는다. 선택적이다.
6. 조인의 방향과는 전혀 무관하다. 무방향성.
7. 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용된다.
그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 머지할 작업 대상을 줄어 주기 때문에
중요한 의미를 가진다.
사용기준
1. 전체범위처리를 하는 경우에 유리하다
2. 상수 값을 받아 처리한 범위(Nested Loop Join)의 크기가 스스로 범위를 줄여 처리한 범위(Sort Merge Join)의 크기의 30% 이상이라면 Sort Merge Join이 유리하다. 그러나 부분범위처리가 되는
경우라면 달라진다.
3. 처리량이 많은 경우 Scan 방식이므로(랜덤액세스를 줄일 수 있다) 유리하다.
4. 연결고리이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하다.
5. 스스로 자신의 처리범위를 어떻게 줄이느냐가 수행속도에 많은 영향을 끼치므로 인덱스 구성이 무엇보다 중요하다.
6. 전체범위처리이므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 크기를 크게하여 Fetch 횟수를 줄이는 것이 바람직하다. (너무 크면 바보..-_-a)
7. on-line Application(처리량이 상대적으로 적은..)에서는 Nested Loop Join이 유리하므로 함부로 Sort Merge Join을 사용해서는 안 된다.
8. 옵티마이저 모드가 ‘All_Rows’인 경우는 자주 Sort Merge Join으로 실행계획이 수립되므로 부분범위처리를 하고자 한다면 옵티마이저 모드 지정에 주의해야 한다.
조건의 유무에 따른 Nested Loop Join과 Sort Merge Join의 영향
한쪽 조건이 없는 경우 Nested Loop Join은 영향을 받지 않으나 Sort Merge Join은 영향을 받는다.
WHERE a.key = b.key AND a.col1 = ‘1’ AND b.col2 = ‘abc’ 인 경우 a가 먼저 액세스 될 경우 b.col2=’abc’
는 체크 조건이 된다.(Nested Loop Join인 경우..) b.col2조건이 없더라도 NL 조인에서는 체크조건이 사라진것이므로 오히려 운반단위를 빨리 채울 수 있으므로 유리할 수 있다. 하지만 Sort Merge Join에서는 조건이 사라지므로 해서 b 테이블을 Full Scan 해야 하므로 엄청난 일량이 증가하게 된다.
클러스터 인덱스
일반인덱스는 각 Row마다 하나의 인덱스를 가지지만 클러스터 인덱스는 클러스터링 컬럼의 값마다 하나의
인덱스 Row를 가진다
클러시터링 팩터(Factor)란? 액세스하고자 하는 데이터가 얼마나 같이 모여있느냐는 것을 말한다.
클러스터링이란? 지정된 컬럼순서대로 저장시키는 물리적 기법이다.
클러스터링의 부하
1. 입력 시 부하
- 일반 테이블은 입력시점에 Freelist에서 할당 받은 저장공간에 무조건 저장하며 저장하고 있는 Block이 PCTFREE에 도달하면 새로운 Freelist를 요구한다. 그러나 클러스터링 테이블은 각 Row들에 따라 저장위치가 달라지므로(클러스터링 컬럼 값에 따라 물리적으로 모여있어야 하므로) Freelist 요구 횟수가 증가하게 된다. 최악의 경우 100개 Row가 입력될 때 100개의 Block을 요구할 수도 있다.
- 1천건, 1만건씩 넣어가며 테스트를 해야 하며, 10건, 100건의 소량 입력도 같이 테스트 해야 한다.
- 클러스터는 인덱스에 비해 인덱스 개수가 적으므로(중복되지 않기 때문에) 클러스터 인덱스가
입력 시 부하가 많다고 하더라도 큰 차이가 없다.
2. 수정 시 부하
- 클러스터 컬럼 값이 변하는 경우는 컬럼값만 변하는 것이 아니라 추가적인 클러스터 체인 블록이
발생한다. 그러므로 수정 작업에 따른 부하는 증가하지 않으나 많은 클러스터 체인이 발생하여 클러스터링 Factor를 나쁘게 하므로 빈번하게 수정이 발생되는 컬럼을 클러스터 컬럼으로 지정해서는 안 된다.
- 클러스터 컬럼이 아닌 컬럼이 변하는 경우는 일반 테이블과 동일하다. 따라서 수정이 빈번하지 않은 컬럼을 클러스터 컬럼으로 지정하였다면 수정에 대한 부하는 걱정하지 않아도 된다.
3. 삭제 시 부하
개별 Row의 삭제는 크게 문제가 되자 않는다. 다만, 테이블을 삭제하는 경우는(Drop) 내부적으로
‘Delete’가 수행되므로(클러스터 입장에서는 테이블은 Row의 일부이다) Rollback Segment에 저장하게 되어 부하를 발생한다. 일반 테이블 Drop은 자료사전의 정보를 삭제하고 할당된 공간을 해제 시키기만 하면 되므로 빠르다.(DDL문이므로 Rollback Segment에도 안 남긴다. 당연하자나-_-;)
클러스터 테이블을 삭제하는 방법
1. 삭제할 테이블을 모두 담을 수 있는 큰 Rollback Segment를 생성하여 작업 시 지정한다.
SQL> set transaction use rollback segment SEGMENT_NAME;
혹시 아래와 같은 에러가 발생하는 것은...
SQL> ORA-01453 : SET TRANSACTION must be first statement of transaction.
어떤 Transaction이 시작하여 종료되지 않은 상태이므로 이미 어떤 Rollback Segment가 지정되어 있기 때문이다. 이런 경우에는 commit이나 rollback 명령을 사용하여 Transaction을 종료한 다음에
수행해야 한다.
2. 클러스터내의 모든 테이블을 삭제하고자 하는 경우는 ‘Drop’이나 ‘Truncate’로 클러스터 자체를
삭제 시키는 것이 좋다.
SQL> drop cluster CLUSTER_NAME including tables;
SQL> truncate cluster CLUSTER_NAME;
일반 테이블과 마찬가지로 Drop은 Row 뿐만 아니라, 자료사전 정보도 삭제하고 Truncate는 Row는 삭제하지만 테이블 정보, 인덱스, 무결성, 트리거, 저장공간의 자료사전 정보는 그대로 보존한다.
- 클러스터 내에 있는 테이블에 참조무결성이 지정되어 있다면...
SQL> drop cluster CLUSTER_NAME including tables cascade constraints;
- 현재 공간을 유지하며(재사용) 삭제하려면...
SQL> truncate cluster CLUSTER_NAME reuse storage;
‘reuse’를 지정하지 않았다면 ‘drop storage’가 default가 되어 저장공간을 삭제하고 기본값만의
저장공간을 확보한다.
클러스터 컬럼 선정
1. 6블록 이상의 테이블. 그 이하는 의미 없다.
2. 다량의 범위(‘LIKE’, ‘BETWEEN’)를 빈번하게 액세스 하는 경우.
3. 클러스터 최대 size는 32766 byte 이다. 컬럼 값 별로 평균 20~100개 Row가 적당하다.
Row가 많다면 체인이 많이 발생하고, 적다면 저장공간이 낭비된다. 때에 따라서는 두개의 컬럼을
클러스터 키 컬럼으로 잡는 것도 고려한다.
클러스터는 되도록 단일테이블 클러스터로 사용하는 것이 좋다
클러스터링 체인(Chain)
일반 테이블은 컬럼 길이가 증가했을 때 체인이 발생한다. 반면에 클러스터 테이블의 하나의 클러스터가 하나의 레코드로 설명될 수 있으므로 일반 테이블의 컬럼에 해당하는 것이 클러스터 테이블에서는 Row가 된다. 따라서 Row의 증가에 따라 체인이 발생한다는 의미가 된다.(그럼 클러스터링-같은 블럭에 모이게한- 의미가 없자나..-_-;) 그러나 클러스터의 체인은 체인 되어진 Row들이 같은 블록에 모이기 때문에 크게 걱정할 필요는 없다. 어차피 클러스터 size를 초과하면 체인이 발생하므로 하나 이상의 체인으로 연결된 블록을 가질 수 밖에 없다.
클러스터 사용을 위한 조치
1. 클러스터 키 컬럼을 첫번째로 하는 인덱스를 생성하지 말 것. 때에따라 클러스터를 안 쓸 수도 있다.
2. Hint나 인덱스 컬럼 사용제한(ex. rtrim() etc..) 등을 통하여 실행계획을 고정시킨다.
클러스터 생성 예제
CREATE CLUSTER cluster_test (col1 varchar2(10))
PCTFREE 10 PCTUSED 60
SIZE 8600;
CREATE INDEX cluster_test_idx on CLUSTER cluster_test
PCTFREE 5
STORAGE (INITIAL 20K NEXT 10K);
RENAME table1 TO table2; è 기존에 생성되어 있는 테이블이 있는 경우
CREATE TABLE table1
(col1 varchar2(10) not null,
...
...
... )
CLUSTER cluster_test (col1);
부분범위처리의 특징
- order by를 사용했다 하더라도 조건 절에 order by 컬럼이 있으면 order by를 무시하고 부분범위처리 한다.
- ‘Sort’나 ‘Filter’(한 집합에서 다른 집합을 골라내는(여과하는) 작업 à minus, intersect, union)가 실행계획에 있으면 전체범위처리를 한 것이다.
(union all은 부분범위 처리 가능. union은 두 집합을 모두 액세스하여 정렬한 후 한가지 요소들만 골라내는
작업을 하므로 부분범위처리가 불가능하다. 어느 한 집합이 공집합이거나 중복이 없더라도 부분범위처리는
불가능하다.)
정렬작업은 메모리내에서 한번의 처리로 가능하면 1000건의 Row를 정렬하는데 0.1초 정도가 소요된다.
처리범위에 따른 수행속도 향상
SELECT * FROM order
WHERE orderno BETWEEN 1 AND 1000 è 1000건
AND custno LIKE ‘DN%’ è 10건
인 경우 (각각의 인덱스 생성되어 있음)
1. orderno가 먼저 액세스 된 경우
최초 처리범위가 1000회가 된다. 체크조건이 된 custno가 10건이므로 운반단위를 좀처럼 채우기가 힘들다.
2. custno가 먼저 액세스 된 경우
10건으로 처리범위가 좁고 체크조건인 orderno가 1000건이므로 대부분 운반단위를 채우므로 빠르다.
è 액세스를 주관하는 조건은 범위가 작을수록 일량이 줄고, 그렇지 않은 조건(체크조건)은 오히려
대상 범위가 넓을수록 일량이 줄어든다.
부분범위처리의 수행속도 원리
액세스 주관 조건범위
검증(Check) 조건 범위
수행속도
좁다
좁다
양호
넓다
양호
넓다
좁다
불량 (주관조건과 검증조건의 역할 교체로 해결)
넓다
양호
부분범위처리로의 유도
1. 인덱스나 클러스터를 이용한 Sort의 대체
order by 절 대신 인덱스 컬럼을 이용하여 Sort를 함으로 해서, 전체범위처리를 피할 수 있다.
Hint를 이용하여 역순 정렬(index_desc(...))도 가능하다.
2. 인덱스만으로 액세스
- 인덱스의 컬럼만으로 처리가 가능하다면 속도에 가장 치명적인 테이블 랜덤 액세스를 줄일 수 있다.
인덱스의 범위가 넓은 것은 그리 문제가 되지 않는다. 인덱스는 처음만 랜덤 액세스이고 그 다음부터는
Scan 방식이기 때문이다.
-인덱스만 사용하는 실행계획이 수립되기 위해서는
첫째. 사용된 모든 컬럼들이 하나의 인덱스에 결합되어 있거나...
둘째. 인덱스 머지가 되는 실행계획이 수립된다면 머지 되는 두 개의 인덱스 내에 모든 컬럼들이 속해
야 한다.(인덱스 머지가 되는 실행계획에서는 머지되는 인덱스가 'And-equal'로 표시된다)
3. MAX의 처리
-테이블의 최대값을 가져오기 위해서 별도의 테이블에 저장하여 읽고 수정하는 방식을 사용하는 것은
좋지 못하다.(Transaction 병목현상 및 Lock을 발생시키는 원인이 되기 때문이다)
- Sequence(insert value절, select-list절, update set절에서 사용가능) 는 오라클의 하나의 Object로써
테이블과는 달리 항상 메모리내에서 작업을 하며 사용자가 값을 수정해야 하는 일이 없으므로 병목현상이나 Lock이 발생하지 않아 양호한 수행속도를 보인다. 다만, 분류단위별로 일련번호를 주어야하는
경우에는 처리가 힘들어진다.(ex. 부서코드별 일련번호 부여)
- INDEX_DESC()를 이용하여 MAX 값을 가져온다.
SELECT MAX(seq) + 1 FROM PRODUCT WHERE DEPT='12300' (X)
SELECT /*+ INDEX_DESC(A INDEX1) */
SEQ + 1 FROM PRODUCT A WHERE DEPT='12300' AND ROWNUM=1 (O)
4. EXISTS의 활용
EXISTS는 서브쿼리가 부분범위처리로 수행하다가 조건을 만족하는 첫번째 로우를 만나는 순간 서브쿼리의 수행을 멈추고
메인 쿼리를 실행한다.
1. SELECT a,b,c FROM PRODUCT WHERE ord_date like '95%'
MINUS
SELECT a,b,c FROM SALE WHERE custno = 'DN01'
2. SELECT a,b,c FROM PRODUCT x
WHERE ord_date like '95%'
AND NOT EXISTS (SELECT * FROM SALE Y
WHERE y.ord_dept = x.ord_dept
AND y.ord_date = x.ord_date
AND y.custno = 'DN01')
1,2의 쿼리 결과는 같다. 1은 'ALL_ROWS'에 최적화되도록 Sort Merge Join으로 유도한 경우이다.
2는 부분범위처리로 유도한 경우이다. 2번에서 주의할 사항은 부분범위처리 속도 향상 원리에 의해서
액세스 주관범위가 아닌 조건의 범위가 넓으면 처리속도는 보장된다. 따라서 NOT EXISTS이므로
서브쿼리 결과가 적으면 유리하다. 물론 EXISTS에서는 많아야 유리할 것이다. 그렇지 못한 경우라면
오히려 1번 쿼리가 더 좋을 수 있다.
5. ROWNUM의 활용
ROWNUM은 ORDER BY, GROUP BY 가 수행되기 전에 이루어 진다.
1. SELECT dept, SUM(qty)
FROM PRODUCT
WHERE dept like '123%'
AND rownum <= 10 GROUP BY dept (X)
2. SELECT rownum, dept, totqty
FROM (SELECT dept, sum(qty) as totqty
FROM PRODUCT
WHERE dept like '123%'
GROUP BY dept)
rownum <= 10 (O)
특정 Row만 추출하기
SELECT dept, totqty
FROM (SELECT ROWNUM as RNUM, dept, totqty
FROM (SELECT dept, sum(qty) as totqty
FROM PRODUCT
WHERE dept like '123%'
GROUP BY dept))
WHERE RNUM = 5
AND rownum = 1
여기서 rownum=1 조건을 주지 않게 되면 RNUM=5를 찾았더라도 멈추지 않고 FROM 뒤에 기술한
Inline-view의 모든 로우를 끝날 때 까지 수행하게 된다.
출처 : 인터넷 ^^
1. 인덱스 컬럼이 비교되기 전에 변형(Suppressing)이 일어나는 경우
2. 인덱스 컬럼이 부정형(not, <>)으로 조건을 기술한 경우
3. 인덱스 컬럼이 null로 비교되는 경우
4. 1,2,3의 원칙을 준수했음에도 경우에 따라 옵티마이저가 필요에 따라 사용하지 않는 인덱스가 생길 수 있음
서로 다른 Type 컬럼 비교 시 변형 유형
문자 = 숫자
문자 Type이 숫자 Type으로 변형 됨. 마치, to_number(문자) = 숫자와 동일한 기능을 DBMS 내부적으로 수행
숫자 like '문자%'
문자% 가 숫자 Type으로 변형될 수 없으므로 숫자 Type이 문자 Type으로 변형
NOT IN
Col NOT IN (SubQuery...) 에서 SubQuery는 항상 나중에 수행되거나 Filtering 조인방식으로
수행된다.
NULL
null은 인덱스에 저장되지 않는다. 따라서 [index column] is not null 로 조건 절에 기술하는 것은
[index column] > ‘’ or [index column] > 0 과 같이 바꿔도 같은 결과가 나오며, full scan하던 것을
Index scan 하게 되므로 속도 향상도 꾀할 수 있다.
단, rule based 옵티마이저 모드이거나, cost based 옵티마이저 모드라면 first_rows로 설정되었을 때
가능하다. all_rows로 되어있다면 full scan을 하게 된다.
옵티마이저의 취사 선택
인덱스 머지를 피하기 위해 조건순위에 따른 취사선택, 힌트, 통계정보, 옵티마이저 모드 등에 따라
원칙을 준수했음에도 인덱스를 사용하지 않는 경우가 발생할 수 있다.
1. 순위(ranking) 차이
col1 = ‘1’ and col2 like ‘a%’ (col1, col2 각각 index) 인 경우에 rule based 옵티마이저에서는
= 이 like 보다 순위가 높으므로(= 로 비교했을 때가 분포도가 더 좁다고 판단하기 때문. 똑똑한넘-_-)
col1 index만 사용하고 col2는 비교 조건으로 사용한다. 하지만 cost based 옵티마이저에서는
순위가 낮더라도 like의 분포도가 더 좋다면 col2 index를 사용한다.
col1 = ‘1’ and col2 = ‘a’ 인 경우 rule based 옵티마이저에서는 인덱스 머지가 일어난다.(두 컬럼의
순위가 동일하므로)
그렇다면... col1 like ‘1%’ and col2 like ‘a%’ 인 경우 rule based 옵티마이저에서는 나중에 생성된
인덱스를 사용하고(범위가 넓어진다고 생각되는 like, >, <, between 등의 비교 연산자와 같이 사용될
경우에는 두 가지 중 한가지만 사용하고 나머지는 포기한다), cost based 옵티마이저에서는 통계정보에 의해서 인덱스를 선택한다.
2. 낮은 비용의 선택
인덱스가 있더라도 cost based 옵티마이저일 때는 all_rows냐 first_rows냐에 따라서 인덱스 사용여부가 결정된다.
3. 힌트(Hint)에 의한 선택
힌트를 사용하여 사용자가 원하는 실행계획을 유도할 수 있다. 하지만 힌트는 어디까지나 훈수역학일뿐... 옵티마이저가 힌트를 사용하여 더 많은 비용이 들 것을 판단하면 힌트를 무시하는 경우도 있다.
(호.. 좀 똑똑한걸..-_-?)
인덱스 생성
알다시피 인덱스 생성은 중요하고(-_ㅡ;) 10~15% 이하의 분포도를 가지는 컬럼에 대해서만 인덱스를
생성하는 것이 바람직하다. 또한 여러 개의 인덱스가 사용된다는 것은 인덱스들 간에 먼저 비교하여
같은 rowid를 가진 것만 찾아 테이블의 row를 access하는 『인덱스 머지』가 발생하기 때문에 바람직
하지 않다.
인덱스 머지는 scan 방식이므로, 때에 따라서는 index로 테이블 랜덤액세스를 하는 것보다 효율적일 수도
있다. 결합인덱스는 인덱스 머지의 결과 중 성공한 것만 모아놓은 것과 같은 결과이다.
결합인덱스 선정 시 주의사항
결합인덱스에서 비록 처리 범위를 줄여주지 못하는 컬럼이 포함되어 있더라도, 테이블 액세스를 줄일 수 있으
므로(인덱스에 포함된 컬럼이 조건으로 들어오면 구지 테이블을 읽지 않고 비교가 가능하다) 결합인덱스
컬럼 선정에 신중해야 한다.
또, 항상사용되어지는 컬럼이 선행되었는지 ‘=’로 비교되는지 여부도 중요하다.
인덱스 선정
1. 해당테이블의 모든 액세스 형태 수집
- 반복 수행되는 액세스 형태 찾기
- 분포도가 양호한 컬럼들 액세스 유형 조사
- 자주 넓은 범위 조건이 부여되는 경우 찾기
- 조건에(WHERE절에..) 자주 사용되는 주요 컬럼의 액세스 유형 조사
- 자주 결합되어 사용되는 컬럼들의 조합 및 정렬 순서 조사. 결합인데스 선정 시에는 연결순서를
확실히 한다.(결합도[어떤 컬럼이 더 자주 결합되어 사용되는지..], 결합조건[ex. ‘=’, ‘LIKE’ ...],
정렬순서)
- 역순으로 추출되는 경우. 넓은 범위를 역순으로 보여주기 위해서는 집계테이블로도 무리인
경우가 있다. 이때에는 INDEX_DESC를 이용하여 처리하는 수밖에 없다.(부분범위처리)
- 일련번호를 부여하는 경우 찾기
- 통계자료추출을 위한 액세스 유형조사. 통계자료를 위해서는 넓은 범위처리가 불가피하다.
따라서 클러스터링이나 잘 조합된 결합인덱스로 해결해야 한다.
2. 반복되는 액세스 형태 찾기
1000번 반복되는 경우 0.02초를 0.01초만 줄일 수 있어도 엄청난 속도 향상을 가져온다.
인덱스 선정 시 가장 중요한 요인은 항상 사용되어지는 가이다. 분포도가 아무리 좋더라도
항상 사용되어지지 않으면(ex. 일련번호..) 의미가 없다.
SALENO, ITEM(SALENO가 분포도가 더 좋다고 가정) 각각이 모두 자주 사용되고, SALENO+ITEM도
자주 사용되는 경우, 인덱스선정은 분포도가 더 좋은 SALENO를 선행으로 하는 SALENO+ITEM
결합인덱스를 생성하고 ITEM만 인덱스는 생성하지 않는다.
왜냐하면, WHERE SALENO = ‘1’ AND ITEM=’123’으로 조건이 들어온 경우 SALENO+ITEM인덱스를
사용하지 않고 ITEM만 있는 단독 인덱스를 사용할 수도 있기 때문이다. ITEM은 ITEM과 자주 사용
되는 컬럼과 결합인덱스로 생성해야 한다.
3. 클러스터링 검토
클러스터링할 컬럼은 인덱스와는 달리 분포도가 넓은 것이 오히려 유리하며, 좋은 분포도의 컬럼을
클러스터링하면 효과가 없다. 검색속도 향상에만 좋고 입력,수정,삭제는 부하가 증가하는 단점도 있다.
특히 하나의 트랜잭션에 다량의 데이터가 처리되는 경우는 많은 부하가 발생하므로 이런 애플리케이션에서는 클러스터링을 사용하지 않는 것이 바람직하다.
4.
5.
!! 인덱스 선정은 해당 테이블에 적용되는 모든 조회조건을 선정하여 최소의 인덱스로 최대의 효과를 내는 인
덱스들을 찾는 것이다. 클러스터링, 결합인덱스 등을 최대한 고려하고, 인덱스 우선순위(규칙기준옵티마이저
의 경우 결합인덱스가 있더라도 단독으로 사용되는 인덱스가 있으면 결합인덱스를 사용하지 않는다.)를 잘
판단하여 결정해야 한다.
조인 순서
처리범위란 주어진 조건을 만족하는 Row수(결과Set)만을 의미하는 것이 아니라, 그 조건의 범위를 찾는데
사용되는 주처리 영역(Main Driving)의 액세스 범위를 의미한다. 즉, 주어진 조건을 만족하는 결과가 전체
테이블의 1/100이더라도 인덱스가 없어 전체테이블을 읽었다면 처리범위는 전체테이블이 되는 것이다.
Nested Loop 조인에서는 가장 먼저 수행되는 집합의 처리범위가 전체 일량을 좌우한다. 나중에 수행되는
집합은 먼저 수행되는 결과에 종속적이다.
조인 성공률
세 개 이상 조인하는 경우 조인 성공률이(성공한 Row수) 낮은 집합의 조인이 먼저 일어나는 것이 유리하다.
Driving Table이 동일해도 두 번째로 처리되는 테이블에 따라 처리 일량은 천지차이이다.
예를 들어, A 테이블의 모두가 조인에 성공한다고 가정했을 경우 A : B = 1 : 10 이고, A : C = 1 : 1 의 분포도
를 가질 때 A → B → C 보다 A → C → B 의 연결이 최대 10배 이상 일량을 줄일 수 있다.
연결고리상태
1. 연결고리정상 : 연결되는 컬럼 모두에 인덱스가 있는 경우
A → B, B → A 모두 동일한 액세스 횟수이다. A : B = 1 : M 이더라도 인덱스를 통하여
액세스 되므로 처리일량은 같다. 단, 연결고리가 정상인 상태에서는 먼저 처리범위를 줄여
주는 테이블이 먼저 처리되면 수행속도는 향상된다.
예를들어, (아래의 SQL의 결과는 2row 이다)
SELECT a.FLD1, .., b.FLD1, …
FROM TAB2 b, TAB1 a
WHERE a.KEY1 = b.KEY2
AND a.FLD2 LIKE ‘ABC%’ → 만족하는 Row Count = 200
AND a.FLD1 = ‘10’ → 만족하는 Row Count = 10
인 경우 TAB1 이 먼저 처리된 경우 a.FLD2 LIKE ‘ABC%’ 조건에서 200row의 결과가 Return 되므로
최소 200회 이상을 처리해야 한다.(TAB2와의 연결에서 아무 결과가 없더라도..) 하지만, TAB2를
먼저 수행한 경우에는 a.FLD1 = ‘10’ 에서 10row가 Return되므로 최대 20회만 처리하면 된다.
엄청난 차이다!
2. 한쪽 연결고리 이상 : 어느 한쪽의 연결고리에 인덱스(or 클러스터)가 없는 경우
- 인덱스가 있는 쪽을 먼저 읽으면 대응되는 값을 찾기 위해 인덱스가 없는 쪽을 매번
Full Scan 해야 한다. 따라서, 옵티마이저는 한쪽 연결고리 이상일 경우 인덱스가 없는 쪽을
먼저 읽는다. 언뜻 보면 큰 문제 없어 보이지만, 연결고리정상인 상태였다면 처리범위가 적은
쪽을 먼저 읽도록 실행계획이 수립될 것이나 연결고리이상 상태이므로 인덱스가 없는 쪽을
먼저 읽도록 실행계획이 고정되어 버리게 된다.
- 인덱스가 있더라도 컬럼에 변형이 일어나는 경우에는 연결고리 이상과 같은 결과를 초래한다.
데이터 타입이 서로 다른 컬럼을 조인하는 경우에도 내부적인 컬럼 변형이 일어나므로 설계 시
컬럼 정의에도 유의하여야 한다.
3. 양쪽 연결고리 이상 : 양쪽 연결고리 모두에 인덱스(or 클러스터)가 없는 경우
어느 방향으로 조인을 하더라도 문제가 되므로 옵티마이저는 Sort Merge 방식으로 실행계획을
수립한다.
조인의 효율
연결고리 상태, 액세스의 방향, 인덱스의 구성이 매우 중요한 결정요인이다.
최적화 측면에서 Nested Loop 조인은 First_Rows 이고 Sort Merge 조인은 All_Rows 이다.
Nested Loop Join
어떤 테이블의 처리범위를 하나씩 액세스 하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식
특징
1. Driving Table의 처리범위에서 순차적으로 수행되며 테이블간 연결도 순차적이다.
2. 먼저 액세스 되는 테이블의 처리범위에 의해 처리량이 결정된다. 선행적이다.
3. 나중에 처리되는 테이블은 스스로의 상수 값에 의해서 처리범위를 줄이는 것이 아니라,
값을 받아서 처리범위가 정해진다. 종속적이다.
4. Driving Table의 첫 Row는 랜덤액세스 이고 나머지는 Scan 방식이지만, 연결작업은 모두
랜덤액세스이다.
5. WHERE 절에 있는 모든 컬럼에 인덱스가 있더라도 모두 인덱스가 사용되는 것은 아니다.
연결되는 방향에 따라 인덱스들이 전혀 달라질 수 있다. Check 조건으로 사용되는 경우도 있다.
선택적이다.
6. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 영향을 끼친다.
연결고리 상태, 방향성
7. 부분범위처리가 가능하므로 마지막으로 Check 되는 조건의 범위가 넓을수록 아예 없다면
더욱 빨라진다.
사용기준
1. 부분범위처리를 하는 경우에 유리하다.
2. 조인되는 어느 한쪽이 상대방 테이블에서 추출되는 결과를 받아야 처리범위를 줄일 수 있는
상태라면 항상 유리하다.
3. 주 처리 방식이 랜덤액세스이므로 처리량이 적은 경우에 유리하다.
4. 연결고리 이상상태가 아닌 경우이어야 한다.
5. 순차적으로 처리되기 때문에 Driving Table이 수행속도에 많은 영향을 미친다.
6. 부분범위처리를 하는 경우 운반단위의 크기가 수행속도에 많은 영향을 미칠 수 있다.
운반단위가 적을수록 빨리 운반단위를 채울 수는 있으나, Fetch 횟수에서는 불리해지는 이중성을
가진다.
7. Driving Table의 처리범위가 많거나 연결 테이블의 랜덤액세스 양이 많다면 Sort Merge보다
불리해진다.
Sort Merge Join
양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔 하면서 연결고리의 조건으로 Merge해
가는 방식. 예를 들어... (모든 컬럼에 인덱스가 있다고 가정)
SELECT a.FLD1, ... , b.FLD2, ...
FROM TAB1 a, TAB2 b
WHERE a.KEY1 = b.KEY2 AND a.FLD1 = ‘AB’ AND b.FLD2 = ‘10’ 인 SQL이 Sort Merge Join으로 실행될 경우 a.FLD1과 b.FLD2만 인덱스를 사용하고 연결고리가 되는 KEY1, KEY2는 인덱스를 전혀 사용하지 않고
단지 Merge 조건으로만 사용한다. 때에 따라 아주 불리할 수도...
특징
1. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다. 동시적이다.
2. 다른 테이블에서 어떠한 상수 값도 제공받지 않는다. 즉, 자신에게 주어진 상수 값으로만 처리범위를
줄인다. 독립적이다.
3. 항상 전체범위처리만 가능하다.
4. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우를 제외하고는 모두 Scan방식으로 처리된다.
5. 위에서 언급했듯이 주어진 조건에 있는 모든 컬럼이 인덱스를 사용하는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 사용하지 않는다. 선택적이다.
6. 조인의 방향과는 전혀 무관하다. 무방향성.
7. 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용된다.
그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 머지할 작업 대상을 줄어 주기 때문에
중요한 의미를 가진다.
사용기준
1. 전체범위처리를 하는 경우에 유리하다
2. 상수 값을 받아 처리한 범위(Nested Loop Join)의 크기가 스스로 범위를 줄여 처리한 범위(Sort Merge Join)의 크기의 30% 이상이라면 Sort Merge Join이 유리하다. 그러나 부분범위처리가 되는
경우라면 달라진다.
3. 처리량이 많은 경우 Scan 방식이므로(랜덤액세스를 줄일 수 있다) 유리하다.
4. 연결고리이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하다.
5. 스스로 자신의 처리범위를 어떻게 줄이느냐가 수행속도에 많은 영향을 끼치므로 인덱스 구성이 무엇보다 중요하다.
6. 전체범위처리이므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 크기를 크게하여 Fetch 횟수를 줄이는 것이 바람직하다. (너무 크면 바보..-_-a)
7. on-line Application(처리량이 상대적으로 적은..)에서는 Nested Loop Join이 유리하므로 함부로 Sort Merge Join을 사용해서는 안 된다.
8. 옵티마이저 모드가 ‘All_Rows’인 경우는 자주 Sort Merge Join으로 실행계획이 수립되므로 부분범위처리를 하고자 한다면 옵티마이저 모드 지정에 주의해야 한다.
조건의 유무에 따른 Nested Loop Join과 Sort Merge Join의 영향
한쪽 조건이 없는 경우 Nested Loop Join은 영향을 받지 않으나 Sort Merge Join은 영향을 받는다.
WHERE a.key = b.key AND a.col1 = ‘1’ AND b.col2 = ‘abc’ 인 경우 a가 먼저 액세스 될 경우 b.col2=’abc’
는 체크 조건이 된다.(Nested Loop Join인 경우..) b.col2조건이 없더라도 NL 조인에서는 체크조건이 사라진것이므로 오히려 운반단위를 빨리 채울 수 있으므로 유리할 수 있다. 하지만 Sort Merge Join에서는 조건이 사라지므로 해서 b 테이블을 Full Scan 해야 하므로 엄청난 일량이 증가하게 된다.
클러스터 인덱스
일반인덱스는 각 Row마다 하나의 인덱스를 가지지만 클러스터 인덱스는 클러스터링 컬럼의 값마다 하나의
인덱스 Row를 가진다
클러시터링 팩터(Factor)란? 액세스하고자 하는 데이터가 얼마나 같이 모여있느냐는 것을 말한다.
클러스터링이란? 지정된 컬럼순서대로 저장시키는 물리적 기법이다.
클러스터링의 부하
1. 입력 시 부하
- 일반 테이블은 입력시점에 Freelist에서 할당 받은 저장공간에 무조건 저장하며 저장하고 있는 Block이 PCTFREE에 도달하면 새로운 Freelist를 요구한다. 그러나 클러스터링 테이블은 각 Row들에 따라 저장위치가 달라지므로(클러스터링 컬럼 값에 따라 물리적으로 모여있어야 하므로) Freelist 요구 횟수가 증가하게 된다. 최악의 경우 100개 Row가 입력될 때 100개의 Block을 요구할 수도 있다.
- 1천건, 1만건씩 넣어가며 테스트를 해야 하며, 10건, 100건의 소량 입력도 같이 테스트 해야 한다.
- 클러스터는 인덱스에 비해 인덱스 개수가 적으므로(중복되지 않기 때문에) 클러스터 인덱스가
입력 시 부하가 많다고 하더라도 큰 차이가 없다.
2. 수정 시 부하
- 클러스터 컬럼 값이 변하는 경우는 컬럼값만 변하는 것이 아니라 추가적인 클러스터 체인 블록이
발생한다. 그러므로 수정 작업에 따른 부하는 증가하지 않으나 많은 클러스터 체인이 발생하여 클러스터링 Factor를 나쁘게 하므로 빈번하게 수정이 발생되는 컬럼을 클러스터 컬럼으로 지정해서는 안 된다.
- 클러스터 컬럼이 아닌 컬럼이 변하는 경우는 일반 테이블과 동일하다. 따라서 수정이 빈번하지 않은 컬럼을 클러스터 컬럼으로 지정하였다면 수정에 대한 부하는 걱정하지 않아도 된다.
3. 삭제 시 부하
개별 Row의 삭제는 크게 문제가 되자 않는다. 다만, 테이블을 삭제하는 경우는(Drop) 내부적으로
‘Delete’가 수행되므로(클러스터 입장에서는 테이블은 Row의 일부이다) Rollback Segment에 저장하게 되어 부하를 발생한다. 일반 테이블 Drop은 자료사전의 정보를 삭제하고 할당된 공간을 해제 시키기만 하면 되므로 빠르다.(DDL문이므로 Rollback Segment에도 안 남긴다. 당연하자나-_-;)
클러스터 테이블을 삭제하는 방법
1. 삭제할 테이블을 모두 담을 수 있는 큰 Rollback Segment를 생성하여 작업 시 지정한다.
SQL> set transaction use rollback segment SEGMENT_NAME;
혹시 아래와 같은 에러가 발생하는 것은...
SQL> ORA-01453 : SET TRANSACTION must be first statement of transaction.
어떤 Transaction이 시작하여 종료되지 않은 상태이므로 이미 어떤 Rollback Segment가 지정되어 있기 때문이다. 이런 경우에는 commit이나 rollback 명령을 사용하여 Transaction을 종료한 다음에
수행해야 한다.
2. 클러스터내의 모든 테이블을 삭제하고자 하는 경우는 ‘Drop’이나 ‘Truncate’로 클러스터 자체를
삭제 시키는 것이 좋다.
SQL> drop cluster CLUSTER_NAME including tables;
SQL> truncate cluster CLUSTER_NAME;
일반 테이블과 마찬가지로 Drop은 Row 뿐만 아니라, 자료사전 정보도 삭제하고 Truncate는 Row는 삭제하지만 테이블 정보, 인덱스, 무결성, 트리거, 저장공간의 자료사전 정보는 그대로 보존한다.
- 클러스터 내에 있는 테이블에 참조무결성이 지정되어 있다면...
SQL> drop cluster CLUSTER_NAME including tables cascade constraints;
- 현재 공간을 유지하며(재사용) 삭제하려면...
SQL> truncate cluster CLUSTER_NAME reuse storage;
‘reuse’를 지정하지 않았다면 ‘drop storage’가 default가 되어 저장공간을 삭제하고 기본값만의
저장공간을 확보한다.
클러스터 컬럼 선정
1. 6블록 이상의 테이블. 그 이하는 의미 없다.
2. 다량의 범위(‘LIKE’, ‘BETWEEN’)를 빈번하게 액세스 하는 경우.
3. 클러스터 최대 size는 32766 byte 이다. 컬럼 값 별로 평균 20~100개 Row가 적당하다.
Row가 많다면 체인이 많이 발생하고, 적다면 저장공간이 낭비된다. 때에 따라서는 두개의 컬럼을
클러스터 키 컬럼으로 잡는 것도 고려한다.
클러스터는 되도록 단일테이블 클러스터로 사용하는 것이 좋다
클러스터링 체인(Chain)
일반 테이블은 컬럼 길이가 증가했을 때 체인이 발생한다. 반면에 클러스터 테이블의 하나의 클러스터가 하나의 레코드로 설명될 수 있으므로 일반 테이블의 컬럼에 해당하는 것이 클러스터 테이블에서는 Row가 된다. 따라서 Row의 증가에 따라 체인이 발생한다는 의미가 된다.(그럼 클러스터링-같은 블럭에 모이게한- 의미가 없자나..-_-;) 그러나 클러스터의 체인은 체인 되어진 Row들이 같은 블록에 모이기 때문에 크게 걱정할 필요는 없다. 어차피 클러스터 size를 초과하면 체인이 발생하므로 하나 이상의 체인으로 연결된 블록을 가질 수 밖에 없다.
클러스터 사용을 위한 조치
1. 클러스터 키 컬럼을 첫번째로 하는 인덱스를 생성하지 말 것. 때에따라 클러스터를 안 쓸 수도 있다.
2. Hint나 인덱스 컬럼 사용제한(ex. rtrim() etc..) 등을 통하여 실행계획을 고정시킨다.
클러스터 생성 예제
CREATE CLUSTER cluster_test (col1 varchar2(10))
PCTFREE 10 PCTUSED 60
SIZE 8600;
CREATE INDEX cluster_test_idx on CLUSTER cluster_test
PCTFREE 5
STORAGE (INITIAL 20K NEXT 10K);
RENAME table1 TO table2; è 기존에 생성되어 있는 테이블이 있는 경우
CREATE TABLE table1
(col1 varchar2(10) not null,
...
...
... )
CLUSTER cluster_test (col1);
부분범위처리의 특징
- order by를 사용했다 하더라도 조건 절에 order by 컬럼이 있으면 order by를 무시하고 부분범위처리 한다.
- ‘Sort’나 ‘Filter’(한 집합에서 다른 집합을 골라내는(여과하는) 작업 à minus, intersect, union)가 실행계획에 있으면 전체범위처리를 한 것이다.
(union all은 부분범위 처리 가능. union은 두 집합을 모두 액세스하여 정렬한 후 한가지 요소들만 골라내는
작업을 하므로 부분범위처리가 불가능하다. 어느 한 집합이 공집합이거나 중복이 없더라도 부분범위처리는
불가능하다.)
정렬작업은 메모리내에서 한번의 처리로 가능하면 1000건의 Row를 정렬하는데 0.1초 정도가 소요된다.
처리범위에 따른 수행속도 향상
SELECT * FROM order
WHERE orderno BETWEEN 1 AND 1000 è 1000건
AND custno LIKE ‘DN%’ è 10건
인 경우 (각각의 인덱스 생성되어 있음)
1. orderno가 먼저 액세스 된 경우
최초 처리범위가 1000회가 된다. 체크조건이 된 custno가 10건이므로 운반단위를 좀처럼 채우기가 힘들다.
2. custno가 먼저 액세스 된 경우
10건으로 처리범위가 좁고 체크조건인 orderno가 1000건이므로 대부분 운반단위를 채우므로 빠르다.
è 액세스를 주관하는 조건은 범위가 작을수록 일량이 줄고, 그렇지 않은 조건(체크조건)은 오히려
대상 범위가 넓을수록 일량이 줄어든다.
부분범위처리의 수행속도 원리
액세스 주관 조건범위
검증(Check) 조건 범위
수행속도
좁다
좁다
양호
넓다
양호
넓다
좁다
불량 (주관조건과 검증조건의 역할 교체로 해결)
넓다
양호
부분범위처리로의 유도
1. 인덱스나 클러스터를 이용한 Sort의 대체
order by 절 대신 인덱스 컬럼을 이용하여 Sort를 함으로 해서, 전체범위처리를 피할 수 있다.
Hint를 이용하여 역순 정렬(index_desc(...))도 가능하다.
2. 인덱스만으로 액세스
- 인덱스의 컬럼만으로 처리가 가능하다면 속도에 가장 치명적인 테이블 랜덤 액세스를 줄일 수 있다.
인덱스의 범위가 넓은 것은 그리 문제가 되지 않는다. 인덱스는 처음만 랜덤 액세스이고 그 다음부터는
Scan 방식이기 때문이다.
-인덱스만 사용하는 실행계획이 수립되기 위해서는
첫째. 사용된 모든 컬럼들이 하나의 인덱스에 결합되어 있거나...
둘째. 인덱스 머지가 되는 실행계획이 수립된다면 머지 되는 두 개의 인덱스 내에 모든 컬럼들이 속해
야 한다.(인덱스 머지가 되는 실행계획에서는 머지되는 인덱스가 'And-equal'로 표시된다)
3. MAX의 처리
-테이블의 최대값을 가져오기 위해서 별도의 테이블에 저장하여 읽고 수정하는 방식을 사용하는 것은
좋지 못하다.(Transaction 병목현상 및 Lock을 발생시키는 원인이 되기 때문이다)
- Sequence(insert value절, select-list절, update set절에서 사용가능) 는 오라클의 하나의 Object로써
테이블과는 달리 항상 메모리내에서 작업을 하며 사용자가 값을 수정해야 하는 일이 없으므로 병목현상이나 Lock이 발생하지 않아 양호한 수행속도를 보인다. 다만, 분류단위별로 일련번호를 주어야하는
경우에는 처리가 힘들어진다.(ex. 부서코드별 일련번호 부여)
- INDEX_DESC()를 이용하여 MAX 값을 가져온다.
SELECT MAX(seq) + 1 FROM PRODUCT WHERE DEPT='12300' (X)
SELECT /*+ INDEX_DESC(A INDEX1) */
SEQ + 1 FROM PRODUCT A WHERE DEPT='12300' AND ROWNUM=1 (O)
4. EXISTS의 활용
EXISTS는 서브쿼리가 부분범위처리로 수행하다가 조건을 만족하는 첫번째 로우를 만나는 순간 서브쿼리의 수행을 멈추고
메인 쿼리를 실행한다.
1. SELECT a,b,c FROM PRODUCT WHERE ord_date like '95%'
MINUS
SELECT a,b,c FROM SALE WHERE custno = 'DN01'
2. SELECT a,b,c FROM PRODUCT x
WHERE ord_date like '95%'
AND NOT EXISTS (SELECT * FROM SALE Y
WHERE y.ord_dept = x.ord_dept
AND y.ord_date = x.ord_date
AND y.custno = 'DN01')
1,2의 쿼리 결과는 같다. 1은 'ALL_ROWS'에 최적화되도록 Sort Merge Join으로 유도한 경우이다.
2는 부분범위처리로 유도한 경우이다. 2번에서 주의할 사항은 부분범위처리 속도 향상 원리에 의해서
액세스 주관범위가 아닌 조건의 범위가 넓으면 처리속도는 보장된다. 따라서 NOT EXISTS이므로
서브쿼리 결과가 적으면 유리하다. 물론 EXISTS에서는 많아야 유리할 것이다. 그렇지 못한 경우라면
오히려 1번 쿼리가 더 좋을 수 있다.
5. ROWNUM의 활용
ROWNUM은 ORDER BY, GROUP BY 가 수행되기 전에 이루어 진다.
1. SELECT dept, SUM(qty)
FROM PRODUCT
WHERE dept like '123%'
AND rownum <= 10 GROUP BY dept (X)
2. SELECT rownum, dept, totqty
FROM (SELECT dept, sum(qty) as totqty
FROM PRODUCT
WHERE dept like '123%'
GROUP BY dept)
rownum <= 10 (O)
특정 Row만 추출하기
SELECT dept, totqty
FROM (SELECT ROWNUM as RNUM, dept, totqty
FROM (SELECT dept, sum(qty) as totqty
FROM PRODUCT
WHERE dept like '123%'
GROUP BY dept))
WHERE RNUM = 5
AND rownum = 1
여기서 rownum=1 조건을 주지 않게 되면 RNUM=5를 찾았더라도 멈추지 않고 FROM 뒤에 기술한
Inline-view의 모든 로우를 끝날 때 까지 수행하게 된다.
출처 : 인터넷 ^^
반응형