SQLP 49-1

[실기1] 나이가 20살인 고객중 최근 일주일 이내 주문 이력이 있는 고객을 고객번호순으로 10건 출력하는 sql을 작성하고 아래 실행계획으로 유도하는 힌트를 적으시오.

 

--오브젝트 정보
create table 고객 (
고객번호 VARCHAR(5),
고객명 VARCHAR(120),
출생연월 VARCHAR(4),
등급번호 VARCHAR(5)
);

create table 고객등급 (
등급번호 VARCHAR(5),
등급명 VARCHAR(100)
);

create table 주문 (
	주문일시 DATE,
	고객번호 VARCHAR(5)
)
PARTITION BY RANGE (주문일시) 
(
	PARTITION P_20230907 VALUES LESS THAN (TO_DATE('20230908','YYYYMMDD')),
	PARTITION P_20230908 VALUES LESS THAN (TO_DATE('20230909','YYYYMMDD')),
	PARTITION P_20230909 VALUES LESS THAN (TO_DATE('20230910','YYYYMMDD')),
	PARTITION P_20230910 VALUES LESS THAN (TO_DATE('20230911','YYYYMMDD')),
	PARTITION P_20230911 VALUES LESS THAN (TO_DATE('20230912','YYYYMMDD')),
	PARTITION P_20230912 VALUES LESS THAN (TO_DATE('20230913','YYYYMMDD')),
	PARTITION P_20230913 VALUES LESS THAN (TO_DATE('20230914','YYYYMMDD')),
	PARTITION P_20230914 VALUES LESS THAN (MAXVALUE)
);

CREATE INDEX 고객_X1 ON 고객(출생연월, 고객번호);
CREATE INDEX 주문_X1 ON 주문(고객번호, 주문일시);

--실행결과
/*
고객번호 고객명 등급명
*/

--실행계획
/*
----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |        |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS FULL             | 고객등급|      0 |      1 |      0 |00:00:00.01 |
|*  2 |  COUNT STOPKEY                 |       |      1 |        |      0 |00:00:00.01 |
|   3 |   VIEW                         |       |      1 |      1 |      0 |00:00:00.01 |
|   4 |    NESTED LOOPS SEMI           |       |      1 |      1 |      0 |00:00:00.01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| 고객  |      1 |      1 |      0 |00:00:00.01 |
|*  6 |      INDEX RANGE SCAN          | 고객_X1|      0 |      1 |      0 |00:00:00.01 |
|   7 |     PARTITION RANGE ITERATOR   |       |      0 |      1 |      0 |00:00:00.01 |
|*  8 |      TABLE ACCESS FULL         | 주문  |      0 |      1 |      0 |00:00:00.01 |
----------------------------------------------------------------------------------------
*/

 

 

49회 실기문제는 모두 SQL을 작성하고 주어진 실행계획으로 유도하는 힌트를 적는 문제였습니다.

튜닝을하는데 실행계획을 읽고 어떻게 동작했을지 상상하는 능력도 중요하기때문인것 같습니다.

실행계획은 제가 임의로 오브젝트를 생성하고 작성한 쿼리를 적은거라 ROW수나 수행시간은 의미가 없고,

기억에 따르면 COUNT STOPKEY, NESTED LOOPS SEMI, PARTITON RANGE ITERATOR가 있어 해당 Operation이 나올수 있도록 유도하는 힌트 작성이 포인트였습니다.

 

아래는 제가 생각하는 점수 기준입니다.

1. NESTED LOOPS SEMI 실행계획 유도 힌트를 작성하였는가(UNNEST NL_SJ)

2. COUNT STOPKEY가 동작할수 있도록 쿼리를 작성하였는가 (ROWNUM <= 10)

3. 등급명을 구하는 스칼라서브쿼리를 페이징처리 바깥에 기술하였는가

4. PARTITION RANGE INTERATOR가 동작할수있도록 주문일시컬럼을 가공하지 않고 일주일이내 주문건을 추출하는 쿼리를 작성하였는가

5. 고객테이블 인덱스 스캔이 가능하도록 출생연월컬럼을 가공하지 않고 20살 고객을 추출하였는가

 

========================================================================================

 

주관식

• INDEX(FUNCTION)이 주어지고, 실행계획에 해당 인덱스를 사용한것이 보이고, A-ROWS는 100 , Q. 함수의 수행 횟수?
• 실행계획에 CONCATENATION이 보였었나?? SQL에 IN ( ) 절이 보였는데 이를 방지하는 힌트를 적으시오? ==> NO_EXPAND
• 오라클 아키텍쳐에서 SGA영역에서 사용자가 수행한 SQL문과 실행계획, 저장 프로시저를 저장해 두는 캐시 영역은? ==> 라이브러리 캐시
• 인덱스 스캔해서 테이블로 접근할 때 사용하는 방식이었나? 이 방식을 줄여야 효율화를 달성한다고 했었나? 기억아 잘안남 ==> 랜덤 I/O(랜덤 엑세스가 답 일수도 있음)

​

실기1

고객 주문 고객등급 테이블과 아래 실행계획을 이용하여 주문일시가 7일 이전이고 출생년월이 20살인 고객정보(고객번호, 고객명, 고객등급)가 나오는 를 작성하시오.(출생년월은 현재연도 - 출생연월 = 20 힌트 줌)

실행계획

1) COUNT STOPKEY 존재

2) 실행계획상 고객과 주문 NL조인 위에 고객등급코드 TABLE, INDEX 존재 함.

3) 고객 - 주문 조인순서 확인

4) Nested Loop SEMI JOIN 존재

5) 사용 인덱스 존재.

​

​

데이터 전문가 포럼에서 정확히 복기함

 

출처 : https://cafe.naver.com/sqlpd/60099

[출처] SQLP 49회 문제 복기|작성자 꿀렁이