옵티마이저

Optimizer :  SQL 실행계획을 수립하고, SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어

같은 SQL문이더라도 어떻게 실행하냐에 따라 성능이 달라진다. (소요시간, 자원사용량 등)

=>SQL문을 분석한 후 일정한 기준을 통해 실행계획을 세워줘야함. 이때 사용되는 것이 옵티마이저!

 

SQL문 작성 - Parsing(문법 검사, 구문 분석) - 옵티마이저 (비용기반/규칙기반) - 실행계획(plan_table 저장) - SQL 실행

 

1. 옵티마이저는 '비용기반 옵티마이저'와 '규칙기반 옵티마이저'가 있으며, 최신 오라클은 비용기반을 default로 사용한다.

2. 비용기반 옵티마이저는 시스템 통계와 오브젝트 통계를 통해 해당 SQL문 실행에 대한 총 비용을 계산하고, 총 비용이 가장 적은 쪽으로 실행 계획을 수립한다.

3. 규칙 기반 옵티마이저는 15가지 우선순위를 기준으로 실행계획을 수립한다.

 

INDEX : 데이터의 색인(목차)와 동일. 원하는 데이터를 빠르게 조회할 수 있다.

- 인덱스틑 인덱스 키를 기준으로 정렬되어 있어 탐색이 빨라진다.

- Primary Key(기본키)는 자동으로 INDEX가 된다.

- 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고, 하나의 인덱스는 여러 컬럼으로 구성될 수 있다.

 

index unique scan : 인텍스 키값이 중복되지 않을 때 해당 키를 통해 탐색

index range scan : 특정 범위를 조회하는 where문을 사용하여 해당 영역을 스캔

index full scan : 인덱스의 처음부터 끝까지 모두 스캔

 

문제) 다 맞는 문제!

1. 규칙기반 옵티마이저에서 가장 높은 우선순위는 전체 테이블 스캔 방식이 아닌, 일반적으로 rowid를 기반으로 스캔하는 것이 가장 높은 우선순위를 가진다.

2. 인덱스는 내림차순으로 생성/정렬된다.

3. 인덱스 범위 스캔은 스캔 범위에 따라 단수의 결과 혹은 0건의 결과 출력도 가능하다.

4. 같은 sql문은 실행계획이 다르더라도 결과는 동일하다.

5. 자주 변화하는 속성은 인덱스로 설정하는 것이 좋지 않다. 삭제 및 조회 등의 작업에 있어서 변화를 초래하지 않는 것이 좋다.

6. 보조인덱스는 unique 속성을 가진 인덱스가 아니라면 중복 데이터 입력 가능하다.

7. 인덱스를 통한 스캔은 항상 전체 테이블 스캔보다 효율적이다? => 랜덤 엑세스의 경우에는 많은 양의 데이터를 읽을 때 부하가 커서 오히려 전체 테이블 스캔이 유리할 수 있다.

8. 비용 기반 옵티마이저는 전체 스캔이 인덱스 스캔보다 유리할 수도 있다.

9. 파티션 테이블은 파티션에 대해 인덱스를 생성할 수 있다. => 파티션 키에 대해 인덱스 생성이 가능하며, 그 경우 해당 인덱스를 global인덱스라고 부른다.

10. 인덱스의 수가 증가할 경우 입력, 삭제, 수정 속도가 느려질 수 있다.

11. 인덱스 생성이 가능한 데이터 타입은 varchar, number, date, char 모두 가능하다.

12. 인덱스 종류는 순차 인덱스, 비트맵, 결합 인덱스, 클러스터, 해시 인덱스가 있다.

 

self join : 테이블 명과 컬럼명이 모두 일치하기 때문에 꼭, alias를 써줘야한다.

ex) select t1.col1, t2.col2

from 테이블명 t1, 테이블명 t2

where t1.col1 = t2.col1

 

cross join : 서로 연관된 컬럼이 없을 경우 수행하는 조인

 

옵티마이저  조인(Optimizer Join)

join을 수행하는 과정에서 성능을 최적화하기 위해 Optimizer Join 방식을 선택하여 hint로 기입해줌!

(* ordered use_hash, ordered use_merge)

 

SELECT /* orderde use_nl(B) */ A.*

FROM  table1  A  JOIN  table2  B  ON  A.id  =  B.id;

 

3가지 종류의 옵티마이저 조인(Optimizer Join)

 

Nested Loop JOIN

- 선행 테이블 (외부 테이블, Driving Table)을 먼저 조회하여 연결 대상 데이터를 찾고, 그 다음 테이블(내부 테이블)을 연결함.

 

- 먼저 처리되는 데이터의 양 - 선행 테이블(외부테이블, Driving Table)의 처리범위에 따라 처리량이 결정됨.

- 선행 테이블의 크기가 작은 것을 찾아야 함.

 

- 로우들간의 처리, 테이블 간의 처리 모두 순차적으로 일어난다.

- 최적의 순서를 찾아주는 것이 중요하다.

 

- RANDOM ACCESS 발생 (선행테이블에서 두 번째 테이블을 참조할 때 발생함)

- 성능 지연을 줄이기 위해 RANDOM ACCESS가 적은 양이 발생하도록 해야함.

 

- 선행 테이블 처리 범위가 많거나, 연결 테이블에서의 랜덤앤세스 범위가 많다면 SORT MERGE JOIN보다 불리해지는 경우가 있음

- INDEX가 필요, Unique Index시 유리함

- 온라인 트랜젝션 처리(OLTP)에 유용함

 

(문제. 중첩된 반복문과 동일한 형식이고 선행 테이블의 조건을 만족하는 경우의 수만큼 반복적으로 수행된다.)

 

Sort Merge JOIN

- 두 테이블을 각각 정렬하고, 완료되면 병합함

- 정렬이 발생하기 때문에 데이터 양이 많을 경우 느려진다.

- 정렬 대상 데이터 양이 많을 경우 임시 디스크를 사용하기 때문에, 성능이 저하됨

- EQIU JOIN, non-EQUI JOIN 모두 가능함.

 

HASH JOIN

- 두 테이블 중 작은 테이블을 HASH 메모리에 로딩하고, 두 테이블의 조인 키를 사용하여 해시 테이블을 생성함

- 두 테이블을 동시에 스캔함

- 선행 테이블에는 작은 데이터가 먼저 와야함

 

- 시스템 자원을 최대한 활용 가능하며, 너무 많이 사용될 우려도 있음

- 대용량 처리에 빠른 처리 속도를 보임

 

- EQUI JOIN에서만 가능함

- INDEX를 사용하지 않음

 

- 해시 조인은 먼저 선행 테이블을 결정하고, 선행 테이블에서 주어진 조건(WHERE)에 해당하는 레코드를 선택한다. 해당 행이 선택되면 JOIN key를 기준으로 해시 함수를 사용해서, 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾는다. 후행 테이블의 JOIN key를 사용해서 해시함수를 적용하여 해당 버킷을 검색한다.

 

PL/SQL이란?

SQL을 확장시켜 다양한 절차적 프로그래밍을 가능하게 한 언어

 

- Block 구조로 되어있어서 기능별로 모듈화가 가능

- Declare 문으로 시작하며, 변수 및 상수를 선언하여 사용 가능

- DML, IF, LOOP 문 등 다양한 절차적 언어를 사용

( 문제. PL/SQL 언어로 Procedure, User Defined Function, Trigger 객체를 작성할 수 있다. / DECLARE, BEGIN ~ END는 필수, EXCEPTION은 선택 / Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고, 일반적 SQL 문장은 SQL 실행기가 처리한다.

 

분산 데이터베이스

하나의 데이터베이스시스템이 네트워크를 통해 물리적으로 분리된 데이터베이스들을 제어하는 형태의 DB

- 성능 향상 : 분산 데이터베이스가 병렬 작업을 하기 때문에 속도가 빠름

- 모듈화가 되어 있어 다른 모듈에 영향을 주지 않고 시스템 갱신이 가능

- 분산 데이터베이스 추가를 통한 용량 확장이 용이함

- 중요 데이터를 보호하기 용이함

- 신뢰성이 높음

- 관리와 통제가 어려움

- 보안관리, 무결성 통제가 어려움

- 복잡한 구조를 가지고 있음