해설 :ERD에서 주문과 고객은 M:1 관게이고, 주문 테이블 고객번호는 Null 값을 허용하지 않는다. 뿐만 아니라 SQL문에서 조인 조건 외에 어디서도 고객 테이블을 참조하지 않고 있다. 따라서 고객과의 조인은 불필요하다.
문제 2
Q. 인덱스 탐색 과정은 수직적 탐색과 수평적 탐색으로 나눠서 설명할 수 있다. 수평적 탐색은 인덱스 리프 블록에 저장된 레코드끼리 연결된 순서에 따라 좌에서 우, 또는 우에서 좌로 스캔하기 때문에 '수평적'이라고 표현한다. 수직적 탐색은 수평적 탐색을 위한 시작 지점을 찾는 과정이라고 말할 수 있다. 다음 중 아래 SQL에서 EMP_IDX 인덱스의 수평적 탐색 시작 지점의 값을 보기에서 고르시오.
① 인덱스 정렬 순서 상 deptno = 20 조건을 만족하는 첫 번째 레코드 ② 인덱스 정렬 순서 상 deptno = 20 and sal = 2000 조건을 만족하는 첫 번째 레코드 ③ 인덱스 정렬 순서 상 deptno = 20 and sal = 2000 and comm = 100 조건을 만족하는 첫 번째 레코드 ④ 인덱스 정렬 순서 상 deptno = 20 and sal = 3000 and comm = 100 조건을 만족하는 첫 번째 레코드
해설 :인덱스 정렬 순서 상 deptno = 20 and sal = 2000 조건을 만족하는 첫 번째 레코드에서부터 수평적 탐색을 시작한다. comm 조건이 less than or equal인 점에 주목한다. comm 조건이 more than or equal 이면 deptno = 20 and sal = 2000 and comm = 100 조건을 만족하는 첫 번째 레코드에서부터 스캔을 시작한다.
문제 3
Q. 다음 중 아래와 같은 인덱스 상황에서 Index Range Scan이 불가능한 SQL은?
해설 : Index Skip Scan을 활용하려면 인덱스 선행 칼럼이 누락됐거나 부등호, between, like 같은 범위 검색 조건이어야 한다. ①번의 경우, 인덱스 선두 칼럼이 사용됐지만, 성별 칼럼이 조건에서 누락됐으므로 Index Skip Scan 활용이 가능하다.
※ Index Skip Scan
- 루트 또는 브랜치 블록에서 읽은 칼럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 "가능성이 있는" 하위 블록(브랜치 또는 리프 블록)만 골라서 액세스하는 방식
※ Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식
- 대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됨.
※ Index Unique Scan
- 수직적 탐색만으로 데이터를 찾는 스캔 방식
- Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동함.
※ Index Fast Full Scan
- Index Full Scan 보다 빠름. (인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문)
문제 5
Q. 다음 중 인덱스 구성이 아래와 같을 때 SQL을 처리하는 데 활용 가능한 인덱스 스캔 방식을 2개 고르시오.
create index emp_x01 on emp(deptno, sal);
select empno, ename, deptno, sal
from emp
where sal between2000and4000;
① index range scan ② index full scan ③ index fast full scan ④ index skip scan
해설 :sal 칼럼을 선두로 갖는 인덱스가 없으므로 range scan은 사용할 수 없다. fast full scan은 인덱스에 포함된 칼럼으로만 조회할 때 사용할 수 있다.
문제 6
Q. 다음 중 비트맵 인덱스에 대한 설명으로 가장 부적절한 것은?
① B*Tree 인덱스에 비해 테이블 Random 액세스를 획기적으로 줄여주므로 성별처럼 Distinct Value 개수가 적은 칼럼에도 좋은 성능을 나타낸다. ② 고객번호처럼 Distinct Value 개수가 아주 많을 때 B*Tree 인덱스보다 훨씬 많은 공간을 차지한다. ③ 여러 비트맵 인덱스를 동시에 사용 가능하고, Bitwise Not 연산도 가능하다. ④ Lock에 의한 DML 부하가 심각해서 OLTP성 환경에는 부적합하다.
해설 :계좌번호 =, 지점번호 between 조건만으로도 충분히 스캔 범위를 줄일 수 있다.
문제 8
Q. 다음 중 아래 SQL문을 튜닝하기 위한 분석을 진행 중 분석한 내용으로 가장 부적절한 것은?
create index 주문_idx on 주문(주문일자);
select 고객ID, 연락처, 고객등급
from 주문
where 주문일자 ='20110725'
and 배송상태 ='ING';
① [주문일자 = '20110725'] 조건만으로 인덱스 탐색이 이루어진다. ② [배송상태 = 'ING'] 조건에 대한 필터링은 테이블 액세스 단계에서 이루어진다. ③ SQL 문장에 [배송상태 = 'ING'] 조건절이 없다면 테이블은 액세스하지 않아도 된다. ④ [배송상태 = 'ING'] 조건을 만족하는 레코드 비중이 아주 낮다면, 주문일자_idx 인덱스가 주문일자와 배송상태 두 칼럼을 모두 포함하도록 구성함으로써 쿼리 성능을 향상시킬 수 있다. 이 쿼리만 고려한다면, 두 칼럼 간 순서는 자유롭게 선택해도 무방하다.
해설 :①번과 같은 인덱스 구성에서 SQL을 오른쪽과 같이 변환하면 인덱스에서 가입일자 like 조건에 해당하는 범위를 2번 스캔하게 된다. 고객등급을 테이블에서 필터링하므로 테이블 Random 액세스량도 2배 증가한다. ③번과 같은 인덱스 구성에서는 고객등급을 인덱스에서 필터링하므로 테이블 Random 액세스량은 늘지 않지만, 인덱스에서 같은 범위를 2번 스캔하므로 블록 I/O가 오히려 늘어난다.
문제 10
Q. 아래 트레이스 결과를 가장 적절히 설명한 보기를 2개 고르시오.
Call Count CPU Time Elapsed Time Disk Query Current Rows
1909 TABLE ACCESS BY INDEX ROWID TAB1 (cr=266468 pr=27830 pw=0 time=58480816 us)
266476 INDEX RANGE SCAN TAB1_X01 (cr=511 pr=300 pw=0 time=1893462 us) OF ...
① TAB1_X01 인덱스의 클러스터링 팩터는 매우 좋은 상태다. ② TAB1_X01 인덱스에 칼럼을 추가하면 성능을 높이는 데 매우 큰 도움이 된다. ③ TAB1_X01 인덱스 칼럼 순서를 조정하면 성능을 높이는 데 매우 큰 도움이 된다. ④ CPU Time과 Elapsed Time 간 39초 가량 차이가 발생한 이유는, 27,000여 개 디스크 블록을 읽기 위한 I/O Call 과정에 발생한 대기 현상 때문일 가능성이 가장 높다.
해설 :TAB1_X01 인덱스의 클러스터링 팩터는 매우 나쁜 상태다. 테이블 액세스 횟수만큼 블록 I/O가 발생한 것을 통해 이를 알 수 있다. 511개 인덱스 블록을 스캔했는데, 이것은 인덱스 스캔 과정에서 얻는 266,476개 레코드 수에 비하면 그리 큰 수치가 아니다. 비효율이 크지 않다는 뜻이다. TAB_X01 인덱스 칼럼 순서를 조정하면 블록 I/O가 약간 감소할 수 있을지 모르지만, 전체 성능에 미치는 영향은 크지 않을 것이다.
※ 인덱스 설계를 위해 고려해야 할 요소
- 쿼리 수행 빈도 - 업무상 중요도 - 클러스터링 팩터 - 데이터량 - DML 부하(= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 칼럼 포함 여부 등) - 저장 공간 - 인덱스 관리 비용 등
728x90
문제 11
Q. 다음 중 SQL에 사용된 다양한 액세스 유형을 분석해 시스템 전체적인 관점에서 전략적인 인덱스 설계를 하려고 한다. 결합 인덱스 키 칼럼을 선정하는 데 있어 가장 중요한 선택 기준 두 가지를 순서대로 나열한 것으로 가장 적절한 것은?
ㄱ. 데이터 분포 ㄴ. '=' 조건으로 자주 조회되는지 ㄷ. 조건절에 항상 또는 자주 사용되거나 ㄹ. 정렬 기준 칼럼으로 자주 사용되는지
해설 :인덱스를 설계하는 가장 중요한 선택 기준은, 조건절에 항상 또는 자주 사용되는지 여부와 '=' 조건으로 자주 조회되는지 여부이다. 인덱스 선두 칼럼이 일단 조건절에 사용돼야 해당 인덱스가 사용될 수 있으므로 전자가 특히 중요하다.
※ 결합 인덱스 구성
- 첫 번째 기준 : 조건절에 항상 사용되거나, 적어도 자주 사용되는 칼럼들을 선정한다. - 두 번째 기준 : 그렇게 선정된 칼럼 중 '=' 조건으로 자주 조회되는 칼럼을 앞쪽에 두어야 한다. - 세 번째 기준 : 소트 오퍼레이션을 생략하도록 하기 위해 칼럼을 추가한다.
문제 12
Q. 다음 중 아래 인덱스 구성과 SQL을 고려할 때, 실행 계획 맨 아래(ID=5) 주문_IDX 인덱스의 액세스 조건으로 가장 적절한 것은?
해설 : 고객_IDX가 연령+고객등급 구성이라면 순서를 바꿨을 때 엑세스량 개선이 가능하다. 한 달간 주문 건수는 평균 50만 건이므로 조인 순서 변경은 성능에 도움이 되지 않는다.
문제 14
Q. 테이블과 인덱스 구성은 아래와 같다. INDEX RANGE SCAN으로 데이터를 액세스할 수 있는 SQL로 가장 적절한 것은?
테이블명 : 고객
[칼럼 리스트]
고객 ID : NOT NULL, VARCHAR2(10)
고객명 : NOT NULL, VARCHAR2(20)
직업코드 : NULLABLE, VARCHAR2(3)
[인덱스 구성]
고객_PK : 고객ID
고객_X01 : 직업코드, 고객명
① SELECT * FROM 고객 WHERE SUBSTR(고객ID, 1, 3) = '100' ② SELECT * FROM 고객 WHERE 고객명 = '김시험' ③ SELECT * FROM 고객 WHERE 직업코드 IS NULL ④ SELECT * FROM 고객 WHERE 고객ID = 1000000009
해설 :직업코드 조건으로 인덱스를 Range Scan하려면 직업코드를 선두로 갖는 인덱스여야 한다. 고객 _X01 인덱스가 여기에 해당한다. 인덱스 구성 칼럼 중 하나라도 NOT NULL 칼럼이면 IS NULL 조회에 인덱스를 사용할 수 있다. 따라서 직업코드 IS NULL 조회에 고객_X01 인덱스를 사용할 수 있다.
문제 15
Q. Oracle에서 TAB1, TAB2 순으로 NL 조인하도록 유도하고 싶다. 다음 중 ( ㄱ ) 안에 넣을 바른 힌트 사용법을 2개 고르시오.
SELECT/* + ( ㄱ ) */ ...
FROM TAB1 A, TAB2 B
WHERE A.KEY = B.KEY
① ORDERED USE_NL(B) ② ORDERED USE_NL(TAB2) ③ LEADING(A) USE_NL(B) ④ DRIVING_SITE(A) USE_NL(B)
① 조인 연결고리에 equi-join 조건이 하나라도 있어야 한다. ② 일반적으로, 큰 집합으로 해시 테이블을 생성하고서 작은 집합을 읽으면서 이 해시 테이블을 탐색하는 게 유리하다. ③ OLTP 환경에서 수행 빈도가 아주 높은 쿼리라도 부담 없이 사용할 수 있다. ④ Build Input으로 선택된 집합의 조인 칼럼에는 중복 값이 거의 없어야 효과적이다.
해설 :해시 조인을 할 때는 작은 집합으로 해시 테이블을 생성하는 것이 유리하다. OLTP 환경에서 수행 빈도가 아주 높은 쿼리를 해시 조인으로 처리하면 CPU Usage가 높아질 수 있다.
※ Hash Join 성능을 좌우하는 키 포인트 - 한 쪽 테이블이 가용 메모리에 담길 정도로 충분히 작아야 함. - Build Input 해시 키 칼럼에 중복 값이 거의 없어야 함.
※ Hash Join 선택 기준 - 조인 칼럼에 적당한 인덱스가 없어 NL Join이 비효율적일 때 - 조인 칼럼에 인덱스가 있더라도 NL Join 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때 - Sort Merge Join 하기에는 두 테이블이 너무 커 소트 부하가 심할 때 - 수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때
문제 17
Q. 다음 중 스칼라 서브쿼리의 특징을 설명한 것으로 가장 부적절한 것은?
① 스칼라 서브쿼리를 이용한 조인은 NL 조인처럼 한 레코드씩 순차적으로 진행한다. ② 메인 쿼리의 레코드마다 스칼라 서브쿼리를 통해 하나의 레코드, 하나의 값만 리턴한다. ③ 조인에 실패할 경우, Null을 리턴한다. ④ 입력 값(=조인 컬럼)과 출력 값(=리턴 값)을 Shared Pool에 있는 Result Cache에 캐싱했다가 같은 입력 값에 대해서는 캐싱된 값을 리턴함으로써 조인 부하를 줄여준다.
- 단, 상품 테이블은 판매시작일자별 상품 수가 비슷한 것으로 가정한다. - 단, 일별매출 테이블은 기준일자로 파티셔닝되어 있다고 가정한다.
① SQL1은 조인 과정에서 과도한 I/O가 발생할 수 있다. ② SQL1은 판매시작일자 조건을 만족하는 상품 건수가 적다면, 조인 순서 변경만으로 성능이 개선될 가능성이 높다. ③ SQL2는 v_판매시작일자 변수에 오랜 과거 일자를 입력할 때보다 최근 일자를 입력할 때 비효율이 더 적다. ④ Query Transformer는 쿼리 변환을 수행하여 SQL1에 대해 SQL2와 유사한 실행 계획을 수립하기도 한다
해설 :대량 집합을 기준으로 NL 조인하면 많은 랜덤 I/O가 발생한다. SQL1에서 판매시작일자 조건을 만족하는 상품 건수가 적다면, 일별매출보다 상품 테이블을 먼저 드라이빙하는 게 유리할 수 있다. SQL2에서 v_판매시작일자 변수에 최근일자를 입력하면 판매시작일자 조건을 만족하는 상품 건수가 적어지므로 그만큼 비효율이 커진다. 기준일자 Between 조건에 해당하는 많은 일별매출 데이터를 읽고 Group By 처리까지 마쳤는데, 상품 테이블과 조인하는 과정에 많은 데이터가 필터링되기 때문이다.
문제 19
Q. 다음 중 아래와 같은 SQL에서 가능한 조인 방법을 모두 나열한 것은?
SELECT emp.ename, salgrade.grade
FROM emp t1, salgrade t2
WHERE t1.sal BETWEEN t2.losal AND t2.hisal
① Nested Loop Join ② Nested Loop Join, Sort Merge Join ③ Sort Merge Join, Hash Join ④ Nested Loop Join, Sort Merge Join, Hash Join
Q. 아래와 같이 고객 테이블과 2개의 변경이력 테이블이 있다. 두 변경이력 테이블에 있는 시작일자, 종료일자는 선분이력으로 관리된다. 즉, 시작일자에는 이력 레코드 생성일자가 입력되고, 종료일자에는 처음에 '99991231'로 입력되었다가 다음 번 새 이력 레코드가 생성되는 순간 바로 전 일자로 갱신된다. 이 세 테이블에서 이름이 '홍길동'인 고객의 1998년 5월 29일자 고객등급과 전화번호를 조회하려고 한다. 아래 ( ㄱ )에 들어갈 조건절로 가장 적절한 것은? (단, 고객번호와 변경순번을 제외한 나머지 속성은 모두 문자형)
- 예를 들어 고객별연체금액변경이력을 관리할 때, 이력의 시작 지점만을 관리하는 것을 '점 이력' 모델이라 하고, 시작 지점과 종료 시점을 함께 관리하는 것을 '선분 이력' 모델이라고 한다.
문제 21
Q. 다음 중 Local 파티션 인덱스의 특징과 거리가 먼 것은?
① 테이블 파티션과 1:1 대응 관계 ② 테이블에 파티션 ADD/DROP/SPLIT/EXCHANGE 작업 시, 인덱스 파티션도 자동 관리됨. ③ Local 파티션 인덱스의 경우, 테이블 파티션 키(=인덱스 파티션 키)가 인덱스 키 선두 컬럼에 위치해야 함. 예를 들어 테이블과 인덱스 파티션 키가 '주문일자'이면, Local 파티션 인덱스 키는 '주문일자'로 시작해야 함. ④ 테이블 파티션 키가 SQL 조건절에 없을 때, 인덱스 사용 시 비효율 발생
해설 :인덱션 파티션 키가 인덱스 선두 칼럼이어야 한다는 제약은 Global 파티션 인덱스에 해당한다. Local 파티션이든 Global 파티션이든, 인덱스 파티션 키가 조건절에 없으면 인덱스 사용 시 비효율이 발생한다. 문제를 풀 때, Local 파티션 인덱스의 경우, 테이블 파티션 키가 인덱스 파티션 키가 된다는 점에 주의해야 한다.
※ Local 파티션 인덱스
- 테이블 파티션과 1:1로 대응이 되도록 파티셔닝한 인덱스
- 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해 줌.
- SQL Server에서는 '정렬된(Aligned) 파티션 인덱스'라고 부름.
문제 22
Q. 우리가 개발하고자 하는 시스템은 업무적으로 7~9월에 매출이 집중돼 있다. 아래 월별매출집계 테이블(보관주기 3년)을 파티셔닝하기로 했고, 매출원열 기준으로 각 파티션에 데이터를 고르게 분산 저장하고자 할 때, 다음 중 사용할 파티션 전략으로 가장 적절한 것을 2개 고르시오.
해설 : List 파티셔닝과 Range 파티셔닝 모두 가능하다. Range 파티셔닝의 경우, 예를 들어, 매년 1~3, 4~6, 7, 8, 9, 10~12월 6개로 파티셔닝하면 된다. Hash 파티셔닝은 정해진 파티션 개수로 파티션 키 값에 따라 DBMS가 기계적으로 분할 저장하기 때문에 월별 매출 특성을 고려한 파티셔닝을 하기 곤란하다.
※ Oracle이 지원하는 파티션 유형
① Range 파티서닝 - 파티션 키 값의 범위(Range)로 분할 - 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 칼럼을 기준으로 함.
예) 판매 데이터를 월별로 분할
② Hash 파티셔닝 - 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매밍 - 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리 → 각 행(Row)의 저장 위치 예측 불가 - 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적 예)고객번호, 주문일련번호 등 - 병렬처리 시 성능 효과 극대화 - DML 경합 분산에 효과적
③ List 파티서닝 - 불연속적인 값의 목록을 각 파티션에 지정 - 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장 예) 판매 데이터를 지역별로 분할
④ Composite 파티셔닝 - Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성 예) Range + List 또는 List + Hash 등 - Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점
문제 23
Q. 다음 중 테이블 tab1에 다음과 같은 데이터가 존재할 경우, 올바르게 설명한 것은? (각 칼럼의 타입은 number이다.)
col1 col2 col3
=====================
10 20 NULL
15 NULL NULL
60 70 20
① select sum(col2) from tab1 의 결과는 NULL이다. ② select sum(col1 + col2 + col3) from tab1 의 결과는 185 이다 ③ select sum(col2 + col3) from tab1의 결과는 90 이다. ④ select sum(col2) + sum(col3) from tab1 의 결과는 90 이다.
① select * from emp order by deptno; ② select distinct deptno from emp; ③ select deptno, count(*) from emp group by deptno; ④ select empno, ename, sal, avg(sal) over (partition by deptno) from emp;
해설 :윈도우 함수가 사용된 SQL 실행 계획에는 WINDOW SORT 오퍼레이션이 나타난다.
※ 소트와 관련된 오퍼레이션 유형
① Sort Aggregate
- 전체 로우를 대상으로 집계를 수행할 때 나타남.
- Oracle 실행 계획에 sort라는 표현이 사용됐지만, 실제 소트가 발생하진 않음.
② Sort Order By
- 정렬된 결과 집합을 얻고자 할 때 나타남.
③ Sort Group By
- Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남.
④ Sort Unique
- 선택된 결과 집합에서 중복 레코드를 제거하고자 할 때 나타남.
- Union 연산자나 Distinct 연산자를 사용할 때가 대표적
⑤ Sort Join - Sort Merge Join을 수행할 때 나타남.
⑥ Window Sort - 윈도우 함수를 수행할 때 나타남.
문제 25
Q. 다음 중 정렬(Sort) 오퍼레이션을 포함하지 않는 SQL은?
① select * from emp order by ename ② select * from emp where deptno = 20 union all select * from emp where deptno = 30 ③ select empno, ename, rank() over (partition by deptno order by sal desc) from emp ④ select * from dept d, emp e where d.deptno = e.deptno option(force order, merge join)
해설 :Union All은 정렬 오퍼레이션을 발생시키지 않는다. ④번 option 구문은 오라클 ordered use_merge에 해당하는 MS-SQL Server 힌트이다. Sort Merge 조인이므로 정렬 오퍼레이션이 발생한다.
문제 26
Q. 다음 중 아래 ERD와 Dictionary 조회 결과를 고려할 때, 보기 중 union 대신 union all을 사용해도 가능한 것으로 가장 적절한 것은?
SQL> select column_name, num_distinct
2 from user_tab_columns
3 where table_name = 'EMP';
COLUMN_NAME NUM_DISTINCT
----------- ------------
EMPNO 14
ENAME 14
DEPTNO 3
JOB 5
MGR 6
SAL 12
① select deptno, job, mgr from emp where empno = 7499 union select deptno, job, mgr from emp where empno = 7654; ② select job, mgr from emp where deptno = 10 union select job, mgr from emp where deptno = 20; ③ select deptno, job, mgr from emp where deptno = 10 union select deptno, job, mgr from emp where deptno = 20; ④ select empno, job, mgr from emp where deptno = 10 union select empno, job, mgr from emp where deptno = 20;
Q. 모니터링 결과, 아래 쿼리의 수행 빈도가 가장 높아 시스템에 미치는 영향이 큰 것으로 조사됐다. 다음 중 고려할 튜닝 방안으로 가장 부적절한 것은?
create index 게시판_idx on 게시판(작성일시, 게시판구분);
select*
from (
select*
from 게시판
where 게시판구분 = :gubun
and 작성일시 >= trunc(sysdate) -1
orderby 작성일시, 작성자명
)
where rownum <=100;
① rownum 조건을 인라인 뷰 안에 사용한다. ② 게시판 구분의 데이터 분포(선택도)를 고려해 인덱스를 [작성일시 + 작성자명] 순으로 변경할지 검토한다. 최종 결정 시 다른 SQL에 미치는 영향도도 검토해야 한다. ③ 게시판 구분의 데이터 분포(선택도)를 고려해 인덱스를 [게시판구분 + 작성일시] 순으로 변경할지 검토한다. 최종 결정 시 다른 SQL에 미치는 영향도도 검토해야 한다. ④ 작성자명이 데이터 정렬 순서로서 의미 있는 것인지 현업 사용자에게 문의한 후, 불필요하다면 제거한다.
