SQL자격검정실전문제 - 고급SQL튜닝(고급SQL활용)

소트와 관련된 오퍼레이션 유형에 대해 서술하시오.

 

SORT AGGREGATE

 

전체 로우를 대상으로 집계를 수행할 때 나타나는 오퍼레이션입니다. 실제 소트가 발생하지는 않습니다.

SORT ORDER BY

ORDER BY 절을 수행할때 나타나는 오퍼레이션 입니다.

SORT GROUP BY

SORTING 알고리즘을 사용해 그룹별 집계를 수행할 때 나타나는 오퍼레이션 입니다.

SORT UNIQUE

선택된 결과 집합에서 중복 레코드를 제거하고자 할때 나타납니다. Union 연산자나 Distinct 연산자를 사용할 때가 대표적입니다.

SORT JOIN

Sort Merge Join 할때 나타나는 오퍼레이션입니다.

WINDOW SORT

WINDOW FUNCTION을 수행할때 나타나는 오퍼레이션 입니다.

NULL 연산에 대해 서술하시오

칼럼끼리 연산할때는 NULL 이 포함되면 연산결과는 항상 null입니다. 레코드끼리 연산할 때는 값이 Null인 레코드는 제외됩니다.

        col1    col2    col3    (col1 + col2 + col3) (col2 + col3)
        10        20        null         null                null
        15        null    null        null                null
        50        70        20            90                    90
sum()     75        90        20            0                    90

 

union을 union all로 대체하기위한 조건에 대해 서술하시오.

 

두 결과집합이 상호 배타적이어야 합니다. 조회절에 PK 칼럼이 포함되면 모든 레코드는 상호배타적 상태가 됩니다.

 

ORACLE DIRECT PATH INSERT 방식에 대해 서술하시오

 

- INSERT SELECT 문장에 /+append/ 힌트 사용
- 병렬 모드로 insert
- direct 옵션을 지정하고 SQL*Loader로 데이터를 로드
- Create Table As Select 문장으로 수행

ORACLE INSERT ALL 구문에 대해 서술하시오.

INSERT ALL은 한꺼번에 여러 개의 테이블에 데이터를 입력하거나, 하나의 테이블로부터 다른 여러 개의
테이블로 데이터를 복사하여 입력할 때 사용합니다. 즉, 여러개의 테이블에 동시에 데이터를 입력하기 위해 사용하는 구문입니다.

 

• INSERT INTO : 1개의 테이블에 1개의 행을 입력하기
• INSERT INTO ... SELECT ... : SELECT 테이블에서 검색한 컬럼의 데이터를 INTO 테이블의 컬럼에 삽입
• INSERT ALL INTO ... INTO .. SELECT ... : 여러 테이블에 여러 행 입력, 다른 테이블에 동시에 같은 행 입력하기ORACLE APPEND 모드에 대해 서술하시오.
• APPEND 모드는 옵티마이저에게 Direct path i/o를 사용해 insert 하도록 지시하는 힌트입니다. Direct Path I/O 수행시 버퍼를 거치지않고 데이 터를 입력합니다. Redo와 Undo 발생량을 최소화하고 데이터입력을 위해 빈 블록인 FreeList를 조회하지 않습니다. 버퍼 캐시를 거치치 않고 데이터를 세그먼트 HWM 뒤쪽으로 순차적으로 입력합니다. 이러한 특징으로 Conventinal Path I/o에 비해 빠른 속도가 보장됩니다.
  단점으로는 Exclusive 모드의 테이블 Lock 이 발생하므로 동시성이 저하됩니다.
•  APPEND 힌트를 사용하면 사용되는 모든 테이블에 Exclusive mode TM Lock이 설정됩니다.

 

DML 튜닝방안에 대해 서술하시오.

 

대량의 데이터를 일반 UPdate/delete 문으로 갱신하면 상당히 오랜 시간이 소요됩니다. 그 이유는 다음과 같습니다.

 

- 테이블 데이터를 갱신하는 본연의 작업
- 인덱스 데이터까지 갱신
- 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재후 갱신
- 내부적으로 Redo와 Undo 정보 생성
- 블로에 빈 공간이 없으면 새 블록 할당

 

DML 튜닝 방안으로는 대량 데이터에 대해 작업할 경우 인덱스를 Unusable 상태로 변경하고 재생성하고 Insert의 경우 nologging 모드로 변경후 진행합니다.

 

alter table table_name nologging

 

대량 데이터에 대한 dml 작업은 기존 데이터를 임시 테이블에 저장하고 테이블을 Truncate 한 후 임시 테이블을 이용해 다시 입력합니다. 수정가능 조인뷰나 Merge문을 활용해 서브쿼리 테이블의 읽기 횟수를 최소화합니다.

 

파티셔닝이 필요한 이유에 대해 서술하시오

 

관리적 측면으로는 파티션 단위 백업, 추가, 삭제, 변경을 용이하게 하기 위함이고 성능적 측면에서는 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산에 목적이 있습니다. 저장 효율은 오히려 나빠질 수 있습니다.

 

파티션되지 않은 테이블을 새롭게 파티셔닝하더라도 쿼리는 수정하지 않아도 됩니다. 기존에 사용되던 인덱스를 파티셔닝하면 그 인덱스를 엑세스하던 일부 sql 성능이 나빠질 수 있습니다. PARTITION PRUNING이 작동합니다.

 

 

파티션 인덱스의 종류에 대해 서술하시오.

 

- Local prefixed partition index
- Local NonPrefixed Partition Index
- Global Prefixed Partiion Index
- 비파티션 인덱스

 

배치 프로그램의 특징에 대해 서술하시오

 

사용자와 상호작용없이 대량의 데이러틀 처리하는 일련의 작업을 묶어 정기적으로 반복 수행하거나 정해진 규칙에 따라 자동으로 수행합니다. 개별 프로그램 측면에서도 최초 응답속도보다는 전체 처리속도 최적화를 목표로 설정합니다. 파티션과 병렬처리를 적적하게 사용하면 시간을 단축할 수 있습니다. 여러 배치 프로그램에서 공통적으로 사용하는 집합을 정의해 이를 임시 테이블로 생성합니다.

 

오라클 병렬 프로세싱에 대해 서술하시오.

일련의 처리과정을 모두 병렬처리하면 병렬 효과가 극대화됩니다. 옵티마이저는 병렬 프로세스간 통신량을 최소화하려합니다.
병렬 DML 시, Exclusive Tm Lock이 발생하므로 테이블을 갱신하는 다른 트랜잭셕에 블로킹이 발생합니다 병렬 update는 Redo Log를 생성하지 않습니다.

 

pq_distribute 힌트에 대해 서술하시오.

 

pq_distribute 힌트를 사용함으로써 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있습니다. pq_distribute 로 성능 개선이 가능한 경우는 다음과 같습니다.

 

- 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할때.
- 기존 파티션 키를 무시하고 다른 키값을 동적으로 재분할 하고 싶을때
- 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키려 할때
- 기타 여러가지 이유로 데이터분배 방식을 변경하고자 할때

 

Partial Partiton Wise Join

둘 중 하나의 테이블만 파티셔닝이 된 경우 파티셔닝 되지 않은 한 쪽 테이블을 같은 기준칼럼으로 파티셔닝 하고 Full Partition Wise Join 합니다.