SQLD04 :: SQL최적화의 원리(1)

ㅡ. 옵티마이저

SQL개발자가 SQL 작성하고 실행할때 옵티마이저로 SQL을 어떻게 실행할지에 대해 계획을 수립한다. 이러한 계획을 수립한뒤 SQL을 실행한다. 규칙 기반 옵티마이저 보다 비용기반 옵티마이저를 기본적으로 수행하며, 옵티마이저의 실행방법을 알려주는것이 힌트(HINT) 다.

 

ㅡ. 인덱스

인덱스란 데이터를 빠르게 검색할 수 있는 방법이다. 인덱스 키로 정렬되어 있기 때문에 원하는 데이터를 빠르게 조회할 수 있다. 하나의 테이블에서 여러 개의 인덱스를 생성할 수 있다. 또한, 하나의 인덱스에는 여러개의 칼럼으로 구성된다. 테이블 생성할때 기본키는 자동으로 인덱스를 생성하고 인덱스 이름은 SYSXXXX 로 제공된다. 

 

ㅡ. 인덱스구조

Root Block, Branch Block, Leaf Block

Root Block : 가장 상위 

Branch Block : 다음 단계 주소

Leaf Block : 인덱스 키와 ROWID로 구성, 인덱스 키는 정렬되어 저장

 

ㅡ. 인덱스 생성

CREATE INDEX문을 사용해서 생성, 기본은 오름차순

 

ㅡ. 인덱스 스캔

Unique Index SCAN : 인덱스 키 값 중복되지 않으면, 해당 인덱스를 사용할 때 발생

Index Range SCAN : Select 문 특정 범위 조회 Where 문 사용할때 발생

Index Full SCAN : 인덱스 키가 많은 경우 Leaf Block의 처음부터 끝까지 읽는다. 

 

ㅡ.

SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 30;

SELECT * FROM EMP,DEPT : DEPT테이블에서 SYSXXXX 인덱스를 INDEX UNIQUE SCAN 한다. 

WHERE EMP.DEPTNO = DEPT.DEPTNO : INDEX에서 DEPT 테이블 ROWID 사용해서 조회 

*ROWID : 데이터베이스 내 데이터 공유의 주소, 이를 통해 데이터에 접근할 수 있다. 

(ref : http://www.gurubee.net/lecture/2927)

AND EMP.DEPTNO = 30; : EMP테이블을 전체 스캔(FULL SCAN) 

 

ㅡ. Nested Loop 조인

하나의 테이블에서 데이터 찾고, 그 다음 테이블 조인하는 방식(여기서 먼저 조회되는 테이블을 외부테이블 그다음 조회되는 테이블 내부테이블 이라고 한다.)

여기서 스캔의 범위를 줄이기 위해 외부 테이블을 먼저 찾는다. (작은것부터) 

Nested Loop 은 Random Access 양을 줄여야 성능이 향상된다. 

 

ㅡ. Sort Merge 조인

두 개의 테이블을 SORT_AREA라고 하는 메모리 공간에 모두 로딩하고 솔트를 수행한다. 두개 테이블에서 솔트가 완료되면 두개 테이블 병합한다. 

Sort Merge 조인은 정렬이 발생하기 때문에 데이터양이 많아지면 성능이 떨어진다. 

 

ㅡ. Hash 조인

두개의 테이블 중에서 작은 테이블을 Hash 메모리에 로딩, 두 개의 테이블의 조인 키를 사용해 해시테이블을 생성, 해시함수를 이용해서 주소계산하고 이 주소를 사용해서 테이블을 조인하기에 CPU 연산을 많이 한다. Hash조인시에는 먼저 오는 테이블에 충분한 크기의 메모리가 로딩되어야 한다. 

 

ㅡ. 옵티마이저는 SQL 실행계획을 수립한다. 

 

ㅡ. 테이블에서 데이터가 저장되어 있는 최상위 위치는 = High Water mark 

 

ㅡ. 파티션 인덱스

Global Index = 여러개 파티션에서 하나의 인덱스 사용

Local Index = 파티션 별로 각자 인덱스 사용

Prefixed Index = 파티션 키와 인덱스 키 동일

Non Prefixed Index = 파티션 키와 인덱스 키 다름

 

인덱스 생성시 어떤 키워드를 사용했냐, 파티션 키와 인덱스를 보자 다음으로! 

 

ㅡ. From절에서 기술한 테이블 순서대로 조인을 하는 힌트는? 

Oracle = ORDERED

MS-SQL = option

 

ㅡ. 특정 사용자에게 특정 테이블에 권한을 부여할때, Grant를 사용한다. 

 

ㅡ. 

Create table [table-name](
   ID CHAR(10) PRIMARY KEY,
   NAME VARCHAR(20) NOT NULL,
   AGE NUMBER(3) DEFAULT 1
);

 

ㅡ. 

학생 테이블의 학생사번은 학교 테이블의 학교이름을 참조하는 외래키이다.

Foreign key 학생사번 References 학교(학교이름)

 

ㅡ. 

Null은 모르는값이나 값이 정해지지 않은것이지 0과 같은 값이 아니다. 

 

ㅡ. 

테이블 및 인덱스 등의 통계 정보를 활용해 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 
가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저를 CBO(비용기반옵티마이저) 라고 한다. 

 

*ref : SQL개발자 이론서+문제집, SQL 자격검정 실전문제