2과목(3장: SQL최적화 기능)

옵티마이저

- 규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔

- 제일 높은 우선순위는 ROWID를 활용하여 테이블을 엑세스 하는 방법

(제일 높은 우선순위 : 행에 대한 고유 주소를 사용하는 방법)

 

실행계획

- SQL처리 흐름도는 SQL문의 처리 절차를 시각적으로 표현한 것으로 인덱스 스캔 및 전체 테이블 스캔 등의 액세스기법을 표현할 수 있으며 성능적인 측면도 표현할 수 없음

인텍스 범위 스캔은 결과 건수 만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있음

 

관계형 데이터베이스의 인덱스

- 기본인덱스(PRIMARY KEY)는 UNIQUE& NOT NULL 제약 조건을 가짐

- 보조 인덱스는 UNIQUE INDEX가 아니라면 중복데이터의 입력이 가능

- 자주 변경되는 속성을 인덱스로 선정할 경우, UPDATE, DELETE성능에 좋지 않은 영향을 미칠 수 있음

=> 인덱스 후보로 적절하지 않음

- 테이블의 전체데이터를 읽는 경우는 인덱스가 거의 불필요

(테이블 전체를 읽는 경우에는 인덱스를 사용하지 않은 FTS를 사용)

- 인덱스는 조회만을 위한 오브젝트이며 삽입/삭제/갱신의 경우, 오히려 부하를 가중시킴

- B TREE(BALANCE TREE)는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스

- 인덱스가 존재하는 상황에서 데이터를 입력하면, 매번 인덱스 정렬이 일어나므로 데이터 마이그레이션과 같이 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고 데이터 삽입이 끝난 후 인덱스를 다시 생성하는 것이 좋음

 

비용기반 옵티마이저(CBO : COST BASED OPIMIZER)

- 테이블 및 인덱스 등의 통계정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O자원량 등을 계산하여 가장 효율적인것으로 예상되는 실행계획을 선택하는 옵티마이저를 말함

 

실행계획을 통해서 알수있는 정보

- 액세스 기법, 질의 처리 예상 비용, 조인순서, 실제처리 순서 

*실제처리 건 수는 트레이스 정보로 알 수 있음

 

실행계획

- 실행계획은 SQL처리를 위한 실행절차와 방법을 표현

- 실행계획은 조인 순서, 액세스 기법, 조인 방법 등이 표현

- CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표시됨

- 동일 SQL문에 대해 실행계획(=실행방법)이 달라져도 결과는 같음

- 실행계획은 위->아래/ 안->밖으로 읽는다

1. NESTED LOOPS
2.   HASH JOIN
3.      TABLE ACCESS (FULL) TAB1
4.      TABLE ACCESS (FULL) TAB2
5.   TABLE ACCESS (BY ROWID) TAB3
6.      INDEX (UNIQUE SCAN) PK_TAB3

처리 순서는 3 4 2 5 6 1

SQL처리 흐름도

- SQL처리 흐름도는 SQL실행계획을 시각화

- SQL처리 흐름도만 보고서는 실행시간을 알 수 없음

 

INDEX의 종류

1) B TREE(BALANCE TREE)

- 브랜치블록, 리프블록으로 구성

- 브랜치블록은 분기목적/ 리프블록은 인덱스를 구성하는 컬럼값으로 정렬

- 일반적으로 OLTP시스템 환경에서 가장 많이 사용

 

2) CLUSTERED 인덱스

- 인덱스의 리프페이지가 = 곧, 데이터 페이지 임

- 리프페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적 정렬이 되어 저장됨

 

3) BITMAP 인덱스

- 시스템에서 사용될 질의 시스템 구현 시, 모두 알 수 없는 경우인 DW 및 AD-HOC질의 환경을 위해 설계

- 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조

 

인덱스

- 인덱스를 생성할 때, 정렬 순서를 오름/내림 차순으로 설정 가능

- 비용기반 옵티마이저는 SQL을 수행하는데 있어 소요되는 비용을 계산하여 실행계획을 생성하므로

   =>인덱스가 존재하더라도 전체 테이블 스캔이 유리하다고 판단할 수도 있음

- 규칙기반 옵티마이저의 규칙에 따라 적절한 인덱스가 존재하면 전체테이블 스캔보다 항상 인덱스를 사용하려고 함 

- 인덱스 범위 스캔은 결과 건 수 만큼 반환하지만 결과가 없으면 한 건도 반환하지 않을 수 있음

- 인덱스 목적은 조회 성능 최적화

- INSERT, UPDATE, DELETE 등의 DML처리 성능을 저하시킬 수도 있음

- B-TREE인덱스는 일치 및 범위 검색에 적절한 구조를 가짐

- 랜덤 액세스는 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식

(랜덤액세스의 부하가 크기 때문에 매우 많은 양의 데이터를 읽을 경우에는 인덱스 스캔보다 테이블 전체 스캔이 유리)

- 인덱스는 조회목적에는 효과적이지만 INSERT, UPDATE, DELETE에는 오히려 많은 부하를 줄 수도 

- SQL SERVER의 클러스터형 인덱스는 ORABLE의 IOT와 유사

- 인덱스는 INSERT, DELETE 작업과는 다르게 UPDATE작업에는 부하가 없을 수도 있음

 

 

OPTIMIZER 실행계획

- ORACLE 규칙기반 옵티마이저에서 가장 우선순위가 높은 규칙은 SINGLE ROW BY ROWID액세스 기법

- 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계 정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다.

 

- ORACLE의 실행계획에 나타나는 기본적인 JOIN 기법으로는 NL JOIN, HASH JOIN, SORT MERGE JOIN 등이 있음

- NL JOIN(NESTED LOOP JOIN)은 데이터를 집계하는 업무보다는 OLTP의 목록처리 업무에 많이 사용됨.

- DW등의 데이터 집계 업무에서 많이 사용되는 JOIN기법은 HASH JOIN 또는 SORT MERGE JOIN

 

SORT MERGE JOIN

- SORT MERGE JOIN은 조인칼럼을 기준으로 데이터를 정렬하여 조인을 수행

- SORT MERGE JOIN은 주로 스캔방식으로 데이터를 읽음

- SORT MERGE JOIN은 랜덤액세스로 NL조인에서 부담이되던 넓은 범위의 데이터를 처리할 때 이용되던 조인 기법

- SORT MERGE JOIN은 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시영역(디스크)를 사용하기때문에 성능이 떨어질 수 있음

- SORT MERGE JOIN을 수행하기에 두 테이블이 너무 커서 소트부하가 심할 때는 HASH JOIN이 유용

(NL -SORT - HASH)

- 조인 칼럼에 적당한 인덱스가 없어서 NL조인이 비효율적일때 사용 가능

- DRIVING TABLE의 개념이 중요

- 조인 조건의 인덱스 유무에 영향을 받지 않음

- EQUI(동등), NON-EQUI(비동등) 둘다 사용가능 

- 

 

NL JOIN (NESTED LOOP JOIN)

- 조인컬럼에 적당한 인덱스가 있어서 자연조인(NATURAL JOIN)이 효율적일 때 유용

- DRIVING TABLE의 조인데이터양이 큰 영향을 주는 조인 방식

- NL조인은 주로 랜덤 액세스 방식으로 데이터를 읽음

- 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량테이블을 온라인조회하는 경우에 유용

- 선택도가 낮은(결과행의 수가 적음) 테이블이 선행테이블로 선택되는 것이 일반적으로 유리

- 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량테이블을 조인할때는 NL조인이 적합

 

HASH JOIN

- HASH JOIN은 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 때에도 사용 가능

- HASH JOIN은 해쉬 함수를 이용하여 조인을 수행하기 때문에 = 로 수행하는 조인(=동등 조인에서만 사용가능)

- 해쉬기법을 이용하여 조인을 함

- 행의 수가 작은 테이블을 선행 테이블로 선택하는 것이 유리

- HASH JOIN은 SORT MERGE JOIN보다 일반적으로 더 우수한 성능을 보이지만, 

  조인대상 테이블이 JOIN KET 컬럼으로 정렬되어 있을 떄는 SORT MERGR JOIN이 더 우수한 성능을 낼 수도 있음

 

HASH JOIN이 더 효과적일 수 있는 조건

- 한쪽 테이블이 주메모리의 가용메모리에 담길 정도로 충분히 작고 해시키 속성에 중복값이 적을때 효과적

- 조인 컬럼에 적당한 인덱스가 없어서 자연조인이 비효율적일때

- 자연조인시, 드라이빙(DRIVING) 집합 쪽으로 조인 액세스량이 많아 RANDOM 액세스 부하가 심할때

- SORT MERGE JOIN을 하기에는 두 테이블이 너무 커서 소트부하가 심할 때