[SQLD] 실전 문제 : 옵티마이저 원리

SQLD 실전 문제 : 옵티마이저 원리

문제 1

Q. CBO(비용 기반 옵티마이저)는 쿼리 최적화 과정에 비용(Cost)를 계산한다. 다음 중 비용(Cost)과 가장 거리가 먼 것은?

① 비용이란 기본적으로, SQL 수행 과정에 수반될 것으로 예상되는 I/O 일량을 계산한 것이다.
② 데이터베이스 Call 발생량도 옵티마이저의 중요한 비용 요소이다.
③ 옵티마이저가 비용을 계산할 때, CPU 속도, 디스크 I/O 속도 등도 고려할 수 있다.
④ 최신 옵티마이저는 I/O에 CPU 연산 비용을 더해서 비용을 계산한다.

 

정답 : ②

해설 : 데이터베이스 Call은 옵티마이저가 수립한 실행 계획에 따라 SQL을 수행하는 과정에, 또는 옵티마이저에게 실행 계획을 수립해 달라고 요청하는 과정에 발생한다.

 

문제 2

Q. 다음 중 규칙 기반 옵티마이저(RBO)가 사용하는 규칙으로 가장 부적절한 것은?

① 고객유형코드에 인덱스가 있으면, 아래 SQL에 인덱스를 사용한다.

select * from 고객 where 고객유형코드 = 'CC0123'

② 고객명에 인덱스가 있으면, 아래 SQL에 인덱스를 사용해 order by 소트 연산을 대체한다.

select * from 고객 order by 고객명

③ 연령과 연봉에 인덱스가 하나씩 있으면, 아래 SQL에 연봉 인덱스를 사용한다. between 조건(닫힌 조건)이 부등호 조건(열린 조건)보다 아무래도 스캔 범위가 작을 가능성이 높기 때문이다.

select * from 사원 where 연령 >= 60 and 연봉 between 3000 and 6000

④ 직급에 인덱스가 있고, 직급의 종류 개수가 5개 이상이면 인덱스를 사용한다.

select * from 사원 where 직급 = '대리'

 

정답 : ④

해설 : 직급의 종류 개수(NDV, Number Of Distinct Value)는 CBO가 사용하는 가장 대표적인 통계 정보이다.

 

※ 규칙 기반 옵티마이저(Rule-Based Optimizer, 이하 RBO)는 다른 말로 '휴리스틱(Heuristic) 옵티마이저' 라고 불리며, 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행 계획을 선택한다. 여기서 규칙이란 액세스 경로별 우선순위로서, 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓는 주요인이다.

 

문제 3

Q. 다음 중 전체 범위 최적화(ALL_ROWS) 방식의 옵티마이저 모드에 대한 설명으로 가장 거리가 먼 것은?

① 쿼리의 최종 결과 집합을 끝까지 Fetch 하는 것을 전제로, 시스템 리소스를 가장 적게 사용하는 실행 계획을 선택한다.
② 부분 범위 최적화(FIRST_ROWS)와 비교할 때, Index Scan보다 Table Full Scan하는 실행 계획을 더 많이 생성한다.
③ DML 문장은 옵티마이저 모드와 상관 없이 항상 전체 범위 최적화 방식으로 최적화된다.
④ 가장 빠른 응답속도(Response Time)를 목표로 한다.

 

정답 : ④

해설 : 전체 범위 최적화는 빠른 Response Time 보다 Throughput 중심으로 최적화를 시행한다.

 

문제 4

Q. 다음 중 비용 기반 옵티마이저(Cost-Based Optimizer)가 사용하는 비용 계산식으로 가장 적절한 것을 2개 고르시오. (※ NDV = Number Of Distinct Value)

① 선택도(Selectivity) = 1 / NDV
② 선택도(Selectivity) = NDV / 총 로우 수
③ 카디널리티(Cardinality) = NDV
④ 카디널리티(Cardinality) = 총 로우 수 / NDV

 

정답 : ①, ④

해설 :

- Selectivity = 1 / NDV

- Cardinality = 총 로우 수 × Selectivity = 총 로우 수 / NDV

 

※ 선택도 → 카디널리티 → 비용 → 인덱스 방식, 조인 순서, 조인 방법 등 결정

※ 카디널리티 = 총 로우 수 × 선택도 = num_rows / num_distinct

 

문제 5

Q. 다음 중 통게 정보 수집 시 고려사항을 설명한 것으로 가장 부적절한 것은?

① 시간/주기 : 부하가 없는 시간대에 가능한 한 빠르게 수집을 완료해야 함.
② 표본(Sample) 크기 : 가능한 한 많은 양의 데이터를 읽도록 해야 함.
③ 정확성 : 표본(Sample) 검사하더라도 전수 검사할 때의 통계치에 근접해야 함.
④ 안정성 : 데이터에 큰 변화가 없는데도 매번 통계치가 바뀌지 않아야 함.

 

정답 : ②

해설 : 통계 정보 수집 시 시스템에 많은 부하를 주므로 대용량 테이블에는 흔히 표본 검사 방식을 사용한다. 표본 검사 시, 가능한 한 적은 양의 데이터를 읽고도 전수 검사할 때의 통계치에 접근하도록 해야 한다.

 

300x250

 

문제 6

Q. 다음 중 동일 결과를 반환하는 SQL에서 가장 효율적인 것은? (단, ItemAmtFunc는 저장형 함수이고, 상품 테이블의 PK는 상품코드임.)

① 

SELECT 상품코드,
       ItemAmiTunc(상품코드, SYSDATE) 당일매출,
       ItemAmtFunc(상품코드, SYSDATE-1) 전일매출, 
       (ItemAmtFunc(상품코드, SYSDATE) - ItemAmtFunc(상품코드, SYSDATE-1))*1OO / 
               ItemAmtFunc(상품코드, SYSDATE) 중감율
FROM 상품
WHERE 상품분류코드='110';

② 

SELECT 상품코드, 당일매출, 전일매출, (당일매출-전일매출) * 100 / 당일매출 증감율
FROM (SELECT ROWNUM, 상품코드, 
             ItemAmtEunc(상품코드, SYSDATE) 당일매출, 
             ItemAmtFunc(상품코드, SYSDATE-1) 전일매출,
      FROM 상품
      WHERE 상품분류코드 ='110')

③ 

SELECT 상품코드, 당일매출, 전일매출, (당일매출-전일매출) * 100 / 당일매출 증감을
FROM (SELECT 상품코드, 
             ItemAmtFunc(상품코드, SYSDATE) 당일매출,
             ItemAmtFunc(상품코드, SYSDATE-1) 전일매출,
      FROM 상품
      WHERE 상품분류코드='110')

④ 

SELECT 상품코드, 
       (SELECT ItemAmtFunc(상품코드, SYSDATE) FROM DUAL) 당일매출,
       (SELECT ItemAmtFunc(상품코드, SYSDATE-1) FROM DUAL) 전일매출,
       (SELECT (ItemAmtFunc(상품코드, SYSDATE) - ItemAmtFunc(상품코드, SYSDATE-1))*100 /
                ItemAmtFunc(상품코드, SYSDATE)
        FROM DUAL) 증감율
FROM 상품
WHERE 상품분류코드 = '110'

 

정답 : ②

해설 : ROWNUM을 이용해 View Merging을 방지했으므로 성능 개선에 도움이 된다.

 

문제 7

Q. Oracle에서 no_merge 힌트를 사용하지 않고도 아래 SQL문에 뷰 머징(View Merging)이 발생하지 않게 하려고 한다. 다음 중 (  ㄱ  ) 안에 들어갈 키워드로 가장 적절한 것은?

SELECT * 
FROM (SELECT (     ㄱ    ), EMP_NAME, HIRE_DATE, SAL, DEPT_NO
      FROM EMP
      WHERE DEPTNO = 30) X
WHERE HIRE_DATE BETWEEN T0_DATE('20100101', ‘YYYYMMDD') AND TO_DATE('20101231', 'YYYYMMDD')

① TO_DATE(SYSDATE, 'YYYYMMDD')
② ROWNUM
③ EMPNO
④ ROWID

 

정답 : ②

해설 : 뷰(View) 안에 rownum을 사용하면 뷰 머징(View Merging)을 방지하는 효과가 나타난다.

 

※ 힌트를 사용하지 않고 뷰 머징(View Merging) 방지하는 방법

- 집합(set) 연산자 : union, union all, intersect, minus

- connect by 절

- ROWNUM pseudo 칼럼

- select-list 에 집계 함수(avg, count, max, min, sum) 사용

- 분석 함수(Analytic Function)

 

문제 8

Q. 다음 중 뷰 머징(View Merging)을 불가능하게 하는 경우가 아닌 것은?

① 뷰 안에 ROWNUM을 사용한 경우
② 뷰 안에 Group By를 사용한 경우
③ 뷰 안에 윈도우 함수(Window Function)를 사용한 경우
④ 뷰 안에 UNION 연산자를 사용한 경우

 

정답 : ②

해설 : Group By를 포함한 뷰는 자주 Merging이 발생한다.

 

문제 9

Q. 다음 중 아래 SQL에 대한 설명으로 가장 적절한 것은? (단, 쿼리 변환(Query Transaction)이 동작하는 것으로 가정함.)

select *
from (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
      from emp
      where job = 'CLERK'
      union all
      select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
      from emp
      where job = 'SALESMAN') v
where v.deptno = 30

① emp 테이블에 job + deptno로 구성된 인덱스를 만들면, job 조건만 인덱스 액세스 조건으로 사용되고, deptno 조건은 필터로 처리된다.
② emp 테이블 job에 단일 컬럼 인덱스를 만들면, 이 인덱스를 정상적으로 사용할 수 없다. 즉, Index Range Scan할 수 없다.
③ emp 테이블에 deptno + job으로 구성된 인덱스를 만들면, job과 deptno에 대한 조건 모두를 인덱스 액세스 조건으로 사용할 수 있다.
④ emp 테이블 deptno에 단일 컬럼 인덱스를 만들면, 이 인덱스를 정상적으로 사용할 수 없다. 즉, Index Range Scan 할 수 없다.

 

정답 : ③

해설 : deptno 조건이 인라인 뷰 안으로 파고 들어간다. 따라서 다음과 같은 SQL을 기준으로 문제를 풀면 쉽다.

select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
from emp
where job = 'CLERK'
and deptno = 30
union all
select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
from emp
where job = 'SALESMAN'
and deptno = 30

 

문제 10

Q. 다음 중 아래 SQL을 처리하는 데 있어 옵티마이저가 선택할 수 있는 옵션으로 가장 부적절한 것은?

alter table 과금 add constraint 과금_pk primary key(고객번호, 과금월);
alter table 수납 add constraint 수납_pk primary key(고객번호, 수납일자);

select e1.고객번호, e1.과금액, e2.수납액, el.과금액 - e2.수납액 미수금액
from (select 고객번호, sum(과금액) 과금액 from 과금 group by 고객번호) el,
     (select 고객번호, sum(수납액) 수납액
      from 수납
      where 고객번호 = 10
      group by 고객번호) e2
where e1.고객번호 = e2.고객번호
and e2.수납액 > 0

① 두 인라인 뷰(Inline View)를 풀어(View Merging) 고객번호 조인을 먼저 처리한 후에, 고객번호로 group by하면서 과금액과 수닙액을 구한다.
② 인라인 뷰 e2에 있는 「고객번호 = 10」 조건을 e1에 전달해 줌으로써 과금_pk 인덱스를 사용해 처리한다.
③ 메인 쿼리에 있는 「수납액 > 0」 조건을 인라인 뷰 e2에 제공함으로써 조인 연산 전에 필터링이 일어나도록 한다.
④ 사용자가 작성한 SQL 형태 그대로, 각 인라인 뷰를 따로 최적화한 후에 조인한다.

 

정답 : ①

해설 : ①번 방식으로 처리할 경우, Cartesian Product 가 발생해 결과가 틀릴 수 있다.

 

728x90