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
TO_DATE('20100101','YYYYMMDD') AND TO_DATE('20101231','YYYYMMDD')
-------------------------------------------------------------------------------------------
① TO_DATE(SYSDATE,’YYYYMMDD’)
② ROWNUM
③ EMPNO
④ ROWID
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ②
뷰(View) 안에 rownum을 사용하면 뷰 머징(View Merging)을 방지하는 효과가 나타난다.
------------------------------------------------------------------------
|
다음 중각 SQL 실행 결과를 가장 올바르게 설명한 것을 2개 고르시오. ① SELECT COL1, COL2 FROM TAB1 WHERE 1 = 2 ; → 실행 시 에러가 발생한다. ② SELECT NVL(COL1,'X') FROM TAB1 WHERE 1 = 2 ; → 실행 결과로 'X'를 반환한다. ③ SELECT NVL(MIN(COL1), 'X') FROM TAB1 WHERE 1 = 2; → 실행 결과로 'X'를 반환한다. ④ SELECT COL1, COL2 FROM TAB1 WHERE 1 = 2 ; → 실행 결과가 없다.(공집합) |
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ③ , ④
조건절에 해당하는 결과집합이 없다고 에러가 발생하지는 않는다. 그리고 공집합에 NVL 함수를 사용한다고 값이 얻을 수 있는
것은 아니다.
------------------------------------------------------------------------
|
다음 중 Syntax 오류가 있는 SQL문 2개를 고르시오.
① SELECT 부서번호
|
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ①, ④
① GROUP BY 절 없이 HAVING을 사용할 수 없다.
④ USING 조건절을 이용한 EQUI JOIN에서도
NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
(부서.부서번호 → 부서번호)
------------------------------------------------------------------------
총 건수가 1,000만 건인 연도별지역별상품매출 테이블에 [출시연도 + 크기], [색상 + 출시연도] 순으로 구성된 두 개의
B*Tree인덱스가 있었다. 다음 중 이 두 인덱스를 제거하고 아래와 같이 세 개의 비트맵(Bitmap) 인덱스를 생성했을 때, 개별 쿼리의
블록 I/O 측면에서 개선 효과가 가장 미미한 것은?
---------------------------- 아 래 ----------------------------------------
create bitmap index 연도별지역별상품매출_bx1 on 연도별지역별상품매출(크기);
create bitmap index
연도별지역별상품매출_bx2 on 연도별지역별상품매출(색상);
create bitmap index 연도별지역별상품매출_bx3 on
연도별지역별상품매출(출시연도);
■ Distinct Value
크기 = { NULL, SMALL, MEDIUM, BIG
}
색상 = { NULL, RED, GREEN, BLUE, BLACK, WHITE }
출시연도 = { NULL, 2001,
2002, 2003, 2004, 2005 2006, 2007, 2008, 2009, 2010 }
■ 데이터 분포는 모두
균일
① select count(*) from 연도별지역별상품매출 where 색상 is null;
② select count(*) from 연도별지역별상품매출
where (크기 = ‘SMALL’ or 크기 is null)
and 색상 = ‘GREEN’
and 출시연도 = ‘2010’;
③ select 색상, count(*) from 연도별지역별상품매출 group by 색상;
④ select sum(판매량), sum(판매금액)
from 연도별지역별상품매출
where 색상 = ‘BLUE’;
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ④
1번과 3번은 기존에 Full Table Scan으로 처리됐을 것이므로 비트맵 인덱스를 생성하고 나면 블록 I/O가 크게 감소한다.
3번의 경우 만약 ‘색상is not null’ 조건을 추가하면 인덱스만 읽고 처리할 수 있지만 그렇더라도 B*Tree 인덱스는 비트맵 인덱스에
비해 블록 I/O는 더 많이 발생한다.
2번은 Bitmap Conversion이 발생하지 않는 한, 기존에 두 B*Tree 인덱스 중
어느 하나만 사용되고, 나머지 필터 조건을 처리하기 위해 테이블 랜덤 액세스가 불가피하므로 성능이 매우 안 좋았을 것이다. 반면, 비트맵
인덱스를 생성하고 나면 두 개의 비트맵 인덱스를 동시에 사용할 수 있고, 테이블을 랜덤 액세스도 생략되므로 성능 개선 효과가 클 것이다.
4번은 색상 = ‘BLUE’에 해당하는 건수만큼 대량의 테이블 액세스가 불가피하다. 비트맵 인덱스를 생성하고 나면 인덱스 스캔 단계에서
다소 블록 I/O가 감소하겠지만 테이블 랜덤 I/O는 줄지 않으므로 성능 개선 효과가 미미하게 나타난다.
------------------------------------------------------------------------
|
다음 중 아래 테이블 정의와 인덱스 현황을 참고하여, 인덱스를 효율적(또는 정상적)으로 액세스할 수 없는 검색조건을 2개 고르시오. (단, Oracle의 Index Unique Scan, Index Range Scan 또는 SQL Server의 Index Seek 이외의
액세스 방식은 모두 비효율적이라고 가정한다.) ---------------------------- 아 래 ---------------------------------------- create table 주문 (
① where 주문번호 between 1 and 10 |
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ② , ④
2번은 LIKE 검색 문자열 앞뒤에 모두 ‘%’ 기호를 붙였으므로 정상적인 Index Range Scan이 불가능하다. 4번은 내부적 형변환이 발생하므로 Index Range Scan이 불가능하다.
------------------------------------------------------------------------
아래 테이블은 어느 회사의 사원들과 이들이 부양하는 가족에 대한 것으로 밑줄 친 칼럼은 기본키(Primary Key)를 표시한 것이다.
다음 중 ‘현재 부양하는 가족들이 없는 사원들의 이름을 구하라’는 질의에 대해 아래 SQL 문장의 ㉠, ㉡ 에 들어 갈 내용으로 가장 적절한
것은?
---------------------------- 아 래 ----------------------------------------
[테이블]
사원 (사번, 이름, 나이)
가족 (이름, 나이, 부양사번)
※ 가족 테이블의 부양사번은 사원 테이블의 사번을 참조하는 외래키(Foreign Key)이다.
[SQL 문장]
SELECT 이름
FROM 사원
WHERE ㉠ (SELECT * FROM 가족 WHERE ㉡ )
------------------------------------------------------------
① ㉠ : EXISTS ㉡ : 사번 = 부양사번
② ㉠ : EXISTS ㉡ : 사번 <> 부양사번
③ ㉠ : NOT EXISTS ㉡ : 사번 = 부양사번
④ ㉠ : NOT EXISTS ㉡ : 사번 <> 부양사번
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ③
‘가족들이 없는’조건 : NOT EXISTS
‘현재 부양하는 가족들’조건 : 사번 = 부양사번
------------------------------------------------------------------------
다음 중 아래 스키마를 보고 평점이 3.0 이상인 학생들의 이름을 보여주는 SQL을 표현한 것으로 가장 적절한 것은?
--------------------------------------------------------------------------------
아 래
<스키마>
※ 밑줄친 속성은 기본키(Primary Key)이며 ENROLL의 학번은 STUDENT의 학번을 참조하는 외래키(Foreign Key)이고, ENROLL의 강좌번호는 CLASS의 강좌번호를 참조하는 외래키이다.
STUDENT(학번, 학과, 이름)
CLASS(강좌번호, 시간, 강좌이름)
ENROLL(학번, 강좌번호, 학점)
--------------------------------------------------------------------------------
①
SELECT 학번, MIN(이름)
FROM STUDENT S, ENROLL E
GROUP BY E.학번
HAVING AVG(E.학점) >= 3.0
②
SELECT S.학번, MIN(이름)
FROM STUDENT S, ENROLL E
WHERE S.학번=E.학번
HAVING AVG(E.학??글>
③
SELECT S.학번, MIN(이름)
FROM STUDENT S, ENROLL E
WHERE S.학번=E.학번
GROUP BY S.학번
HAVING AVG(E.학점) >= 3.0
④
SELECT 학번, MIN(이름)
FROM STUDENT S, ENROLL E
WHERE S.학번=E.학번
GROUP BY S.학번
HAVING AVG(E.학점) > 3.0
-----------------------보기 수정되었습니다-------------------------
------------------------------------------------------------------------
* 정답 및 해설 *
정답 ③
GROUP BY 절 이후에 조건을 적용하는 경우는 HAVING 절이 사용된 경우이다.
그룹핑된 값에 조건을 적용하는 경우 HAVING 절을 사용한다.
JOIN 절에 ALIAS를 사용한 경우, 2개 이상의 테이블에 공통적으로 사용되고 SELECT 절에 사용되는 칼럼에는 ALIAS 접두사를 붙여야 한다.
------------------------------------------------------------------------
다음 중 아래 SQL과 트레이스 결과를 분석하고, 보기에서 제시한 튜닝 방안 중 가장 적절한 것은?
------------------------------------ 아 래--------------------------------
SQL> SELECT ...
2 FROM (SELECT DEII_DT, NEII_VAL, NACT_VAL, NSTD_VAL
3 , NVL(ROUND(AVG(NACT_VAL/NSTD_VAL*100) OVER(), 2), 0.00) AVG_VAL
4 , NVL(ROUND(STDDEV(NACT_VAL/NSTD_VAL*100) OVER(), 2), 0.00) STDDEV_VAL
5 , ROWNUM RN
6 FROM EII_SUMMARY
7 WHERE TO_CHAR(DEII_DT, 'YYYYMMDDHH24MISS') BETWEEN :B3 AND :B4
8 ORDER BY DEII_DT) MR
9 LEFT OUTER JOIN EII_TARGET ET
10 ON ET.DEII_DT BETWEEN TRUNC(MR.DEII_DT, 'MM')
11 AND TRUNC(LAST_DAY(MR.DEII_DT)+1)-1/60/24/24
12 AND ET.NCODE_NO IN ( :B1, :B2 )
13 GROUP BY FLOOR((MR.RN-1)/:B5 ), MR.AVG_VAL, MR.STDDEV_VAL
14 ORDER BY FLOOR((MR.RN-1)/:B5 ) ;
call count cpu elapsed disk query current rows
------- ------ ------- -------- ------ ------- -------- -------
Parse 446 0.00 0.00 0 0 0 0
Execute 7578 0.03 0.12 22 564 2 8
Fetch 13522 128.03 129.16 22 6676902 0 10442
------- ------ ------- -------- ------ ------- -------- -------
total 21546 128.06 129.30 44 6677466 2 10450
Rows Row Source Operation
---- ---------------------------------------------------
2 SORT GROUP BY (cr=7340 pr=7 pw=0 time=221191 us)
240 NESTED LOOPS OUTER (cr=7340 pr=7 pw=0 time=221460 us)
120 VIEW (cr=20 pr=7 pw=0 time=90776 us)
120 FILTER (cr=20 pr=7 pw=0 time=90410 us)
120 WINDOW SORT (cr=20 pr=7 pw=0 time=89447 us)
120 COUNT (cr=20 pr=7 pw=0 time=79490 us)
120 TABLE ACCESS (FULL) OF 'EII_SUMMARY' (TABLE) (cr=20 pr=7 pw=0 time=79250 us)
240 VIEW (cr=7320 pr=0 pw=0 time=74760 us)
240 TABLE ACCESS FULL EII_TARGET (cr=7320 pr=0 pw=0 time=74414 us)
[인덱스 구성]
EII_SUMMARY_X01 : DEII_DT
EII_TARGET_X01 : DEII_DT
------------------------------------------------------------------------------------------
① 7번 라인을 아래와 같이 수정한다. WHERE DEII_DT BETWEEN TO_DATE(:B3, 'YYYYMMDDHH24MISS')AND TO_DATE(:B4, 'YYYYMMDDHH24MISS')
② 9번 라인의 'LEFT OUTER JOIN'을 'INNER JOIN'으로 변경함으로써 EII_TARGET 테이블이 먼저 드라이빙 될 수 있게 한다. Row Source를 분석해 보면, Outer 집합에서 Inner 집합으로 조인 시도한 건수만큼 모두 성공하므로 Outer Join은 불필요하다.
③ 10~12번 라인을 위해 EII_TARGET_X01 인덱스를 [NCODE_NO + DEII_DT] 순으로 구성한다.
④ 14번 라인의 ORDER BY는 불필요하므로 제거한다.
------------------------------------------------------------------------
* 정답 및 해설 *
정답 : ③
7번 라인에 대한 Row Source를 보면, 20개 블록을 읽어서 120개 로우를 반환하므로 굳이 인덱스를 사용하도록 튜닝하지 않아도 된다.
9번 라인에 대한 Row Source만 보고 Left Outer Join이 불필요하다고 판단할 수 없다.
10~11번 라인 조인 칼럼에 인덱스가 있는데도 옵티마이저가 이를 사용하지 않고 Full Table Scan으로 처리한 이유는, NCODE_NO 필터링을 위해 다량의 테이블 랜덤 액세스가 발생하기 때문이다. 인덱스 뒤에 NCODE_NO만 추가해도 성능이 많이 개선되겠지만, 순서까지 바꿔 [NCODE_NO + DEII_DT] 순으로 구성하는 것이 최적이다.
14번 라인의 ORDER BY를 제거하면 결과집합의 출력순서가 달라질 수 있다.
------------------------------------------------------------------------
