SQL자격검정실전문제 - 인덱스와 조인(기본원리/튜닝/조인기본원리/고급조인기법)

인덱스 탐색과정에 대해 서술하시오.

 

인덱스 탐색과정은 수직적 탐색과 수평적 탐색 과정으로 구분됩니다. 수평적 탐색은 인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 좌에서 우 또는 우에서 좌로 스캔합니다. 수직적 탐색은 수평적 탐색을 위한 시작 지점을 찾는 과정입니다.

 

-- emp_idx : deptno + sal + comm
select /*+index asc(e emp_idx)*/ empno, sal, comm, hiredate
from emp e
where deptno = 20
and sal between 2000 and 3000
and comm <= 100

 

 

위 쿼리의 경우 수평적 탐색의 시작지점은 인덱스 정렬 순서 상 deptno = 20 and sal = 2000을 만족하는 첫 번째 레코드 입니다.

 

INDEX RANGE SCAN에 대해 서술하시오.

 

인덱스 루트 블록에서 수평적 스캔 시작점까지 수직적으로 탐색한 후에 리프블록을 필요한 범위만 스캔하는 방식입니다. 인덱스 선두 칼럼을 조건절에 사용하지 않으면 인덱스 수평적 스캔의 시작점을 알 수 없기 때문에 INDEX RANGE SCAN이 불가합니다.

 

INDEX SKIP SCAN에 대해 서술하시오

 

루트 블록 또는 브랜치 블록에서 읽은 칼럼 값의 정보를 이용해 조건에 부합하는 레코드를 포함할 가능성이 있는 하위 블록만 골라서 액세스하는 방식입니다. 인덱스 선두칼럼이 누락됐지만 값의 distinct가 낮은경우나 선두 칼럼이 조건절에서 between, 부등호, like 같은 범위검색 조건일때 주로 사용됩니다.

 

INDEX FULL SCAN에 대해 서술하시오

 

수직적 탐색없이 인덱스 리프블록을 처음부터 끝까지 수평적으로 탐색합니다. 데이커 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택합니다.

 

INDEX UNIQUE SCAN

 

 

수직적 탐색만으로 데이터를 찾는 스캔방식입니다. UNIQUE 인덱스를 = 조건으로 탐색하는 경우에 해당합니다.

 

INDEX FAST FULL SCAN

 

인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 MULTIBLOCK READ 방식으로 스캔합니다.

 

비트맵 인덱스

 

비트맵의 조합에의해 데이터를 매핑하는 인덱스입니다. 트리기반 인덱스에 비해 필요한 저장공간이 적고 탐색시 필요한 연산횟수도 적습니다.

테이블이 크고 칼럼의 분포도가 낮은(중복데이터가 많은) 경우에 주로 사용되며 넓은 범위의 대량 데이터를 검색할 때 유용합니다. 여러 비트맵 인덱슬르 동시에 사용할 수 있고 Bitwise Not 연산도 가능합니다. 반대로 DISTINC VALUE가 많은 즉 분포도가 높은 칼럼을 기준으로 비트맵 인덱스를 생성하면 트리형 인덱스보다 훨씬 많은 공간을 차지합니다.

 

비트맵 인덱스의 단점은 DML 부하가 심하다는 것입니다. 레코드 하나만 변경되도라고 해당 비트맵 범위에 속하는 모든 레코드에 lock이 발생합니다. 이에 OLTP 성 환경에서는 비트맵인덱스를 사용할 수 없고 읽기 위주의 대용량 DW환경에 적합합니다.

 

인덱스 설계를 위해 고려해야할 요소에 대해 서술하시오.

 

- 쿼리수행빈도
- 업무상중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장공간
- 인덱스 관리비용

 

클러스터링 팩터에 대해 서술하시오.

 

클러스터링 팩터는 특장 칼럼을 기준으로 같은 값의 데이터가 모여있는 정도를 의미합니다. CF 가 좋으면 테이블 엑세스 횟수에 비해 블록 I/O가 적게 발생합니다. buffer pinning이 발생하기 때문입니다.

 

결합 인덱스 구성방안에 대해 서술하시오.

 

- 조건절에 항상 사용되거나 적어도 자주 사용되는 칼럼
- 선정된 칼럼중 = 조건으로 자주 조회되는 칼럼을 앞쪽에 배치
- 소트 오퍼레이션을 생략하도록 하기위한 칼럼추가

 

IS NULL 조건의 인덱스 스캔 조건에 대해 서술하시오.

 

인덱스 구성 컬럼중 하나라도 NOT NULL 칼럼이면 IS NULL 조회에 인덱스를 사용할 수 있습니다.

 

테이블 순서대로 NL 조인하는 방안에 대해 서술하시오

 

- ORDERED USE_NL(후행테이블)

 

효율적인 HASH JOIN 을 위한 테이블 구성요건에 대해 서술하시오.

 

한 쪽 테이블이 가용메모리에 담길정도로 작아야하고 BUILD INPUT 해시 키 칼럼에 중복 값이 거의 없어야 합니다. 조인 연결고리에 equi-join이 하나라도 잇어야합니다. 작은 집합으로 해시 테이블을 생성하고 큰 집합을 읽으면서 해시를 탐색하는게 유리.

 

HASH JOIN 선택 기준에 대해 서술하시오.

 

- 조인 칼럼에 대한 적당한 인덱스가 없어서 NL 조인이 비효율적인 경우.
- 조인 칼럼에 인덱스가 있더라도 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 엑세스량이 많아서 RANDOM ACCESS 부하가 예상될때.
- SORT MERGE JOIN 하기에는 두 테이블이 소트부하가 클때
- 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할때

 

스칼라 서브쿼리의 특징에 대해 서술하시오.

 

함수처럼 한 레코드당 정확히 하나의 값 만을 리턴하는 서브쿼리를 말합니다. 주로 SELECT-LIST에 사용되지만 몇 가지 예외사항을 제외하면 대부분의 위치에서 사용가능합니다. 입력 값과 출력값을 SHARED POOL에 있는 RESULT CACHE에 캐싱했다가 같은 입력 값에 대해서는 캐싱된 값을 리턴합니다.

 

선분이력에 대해 서술하시오.

 

특정 사건의 이력의 시작시점만을 관리하는 것을 점이력 모델이라 하고, 시작시점과 종료시점을 함께 관리하는 것을 선분이력 모델이라고 합니다.

 

select
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객명 = '홍길동'
and c1.고객번호 = c.고객번호
and c2.고객번호 = c.고객번호
and :search_date between c1.시작일자 and c1.종료일자
and :search_date between c2.시작일자 and c2.종료일자

 

LOCAL 파티션 인덱스에 대해 서술하시오.

 

LOCAL 파티션 인덱스는 테이블 파티션과 1:1로 대응하도록 파티셔닝한 인덱스입니다. 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블 파티션 키가 인덱스 파티션 키가 됩니다. 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리합니다. 테이블에 파티션 ADD/DROP/SPLIT/EXCHANGE 작업 시, 인덱스에도 자동반영됩니다. 테이블 파티션 키가 SQL 조건절에 없을때 파티션 인덱스를 사용하면 인덱스 사용에 비효율이 발생합니다.

 

ORACLE이 지원하는 파티셔닝 유형에 대해 서술하시오.

 

RANGE 파티셔닝

 

파티션 키 값의 범위로 파티션을 분할합니다. 파티셔닝의 가장 일반적인 형태로, 주로 날짜 칼럼을 기준으로 합니다.

 

HASH 파티셔닝

 

파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션을 매핑합니다. 데이터가 모든 파티션에 고르게 분산되도록 DBMS에서 관리합니다. 그렇기 때문에 각 로우의 저장 위치를 예측할 수 없습니다. 파티션 키의 데이터 분포가 고른 칼럼일때 효과적입니다. 병렬처리 시 성능효과가 극대화 되고 DML 경합 분산에 효과적입니다.

 

LIST 파티셔닝

 

불연속적인 값의 목록을 각 파티션에 지정합니다. 순서와 상관없이 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장합니다.

 

COMPOSITE 파티셔닝

 

RANGE나 LIST 파티션 내에 또 다른 서브 파티션을 구성합니다. 서로 다른 파티션이 갖는 이점을 동시에 누릴 수 있습니다.