SQL - 옵티마이저, INDEX

Optimizer 

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

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

- SQL문을 분석한 후 일정한 기준을 통해 실행계획을 세워줘야 하는데 이때 사용되는게 오티마이저이다.

 

비용기반 옵티마이저

- 시스템 통계와 오브젝트 통계를 통해 해당 SQL문 실행에 대한 총 비용을 계산

- 총 비용이 가장 적은 쪽으로 실행 계획을 수립한다.

 

규칙기반 옵티마이저

- 15가지 우선순위를 기준으로 실행계획을 수립한다.

- 일반적으로 ROWID를 기반으로 스캔하는 것이 가장 높은 우선순위를 가진다.

 

참고) 최신 Oracle은 비용기반을 default로 한다.

 

INDEX 

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

- 인덱스는 인덱스 키를 기준으로 정렬되어 있어서 탐색이 빠르다.

- PRIMARY KEY는 자동적으로 INDEX가 된다.

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

- INDEX UNIQUE SCAN : 인덱스 키값이 중복되지 않을 때 해당 키를 통해 탐색

  INDEX RANGE SCAN : 특정 범위를 조회하는 WHERE 문을 사용하여 해당 영역을 스캔

  INDEX FULL SCAN : 인덱스 처음부터 끝까지 모두 스캔

- 내림차순으로 생성 및 정렬된다.

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

- 자주 변화하는 속성을 인덱스로 설정하는 것은 좋지 않다.

- UNIQUE 속성을 가진 인덱스가 아니라면 중복 데이터 입력 가능하다.

- 랜덤 엑세스의 경우에는 많은 양의 데이터를 읽을 대 부하가 커서 오히려 전체 테이블 스캔이 유리할 수 있다.

- 파티션 키에 대한 인덱스를 생성 가능하며 그 경우 해당 인덱스를 Global 인덱스라고 부른다.

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

- 인덱스 생성은 VARCHAR, NUMBER, DATE, CHAR 모두 가능하다.

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

 

 

Optimizer Join

- 조인을 수행하는 과정에서 성능을 최적화하기 위해 Optimizer Join 방식을 선택하여 Hint로 기입해줌

    EX)

        SELECT /* ORDERED USE_NL(B) */ A.*

        FROM TABLE1 A JOIN TABLE2 B ON A.ID = B.ID

        /*와 */ 사이에 조인방시을 힌트로 줌

- 3가지 종류(NESTED LOOP JOIN)

    NESTED LOOP JOIN

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

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

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

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

    > 최적의 순서를 찾아주는 것이 중요

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

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

    > 선행 테이블 처리 범위가 많거나, 연결 테이블에서의 랜덤엑세스 범위가 많다면 SORT MERGE JOIN보다 불리해 지           는 경우가 있음( SORT MERGE JOIN은 정렬을 먼저하고 조인 하는것)

    > INDEX가 필요, UNIQUE INDEX시 유리함

    > 온라인 트랜잭션 처리(OLTP)에 유용함

    > 중첩된 반복문과 동일한 형식이다.

    > 선행 테이블의 조건을 만족하는 경우의 수만큼 반복적으로 수행된다.

 

    SORT MERGE JOIN

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

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

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

    > EQUI JOIN, non-EQUI JOIN 모두 가능함

 

    HASH JOIN

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

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

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

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

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

    > EQUI JOIN에서만 가능함

    > INDEX를 사용하지 않음

    > 해시조인은 먼저 선행 테이블을 결정하고, 선행테이블에서 주어진 조건(WHERE)에 해당하는 레코드를 선택한다.

       해당 행이 선택되면 JOIN KEY를 기준으로 해시 함수를 사용해서, 해시 테이블을 메인 메모리에 생성하고 후행 테이           블에서 주어진 조건에 만족하는 행을 찾는다. 후행 테이블의 JOIN KEY를 사용해서 해시함수를 적용하여 해당 버킷을         검색한다.

    > 작은 테이블을 선행 테이블로 사용하는 것이 좋다.

    > 정렬이 없어서 부담되는 대량배치 작업에서 유리하다.

 

참고) FROM 절에 여러개의 JOIN절이 많이 있어면 한번에 JOIN작업이 이뤄지는게 아니라 많아야 2개씩 작업된다.