Let's have a cup of coffee.

문제. 아래 실행결과를 반환하기 위해 최근 7일 이내에 주문한 내역이 있는 나이가 20세인 고객을 고객번호가 빠른 순서로 10건 조회하는 SQL을 작성하고, 아래 실행 계획으로 수행될 수 있도록 필요한 힌트를 기술하시오. (단, 20세는 현재 일자에서 20년을 뺀 것이라고 가정)

[오브젝트]
CREATE TABLE 고객(
 고객번호 NUMBER
,고객명 VARCHAR2(100)
,출생연도 VARCHAR2(4)
,등급코드 VARCHAR2(1)
,CONSTRAINT 고객_PK PRIMARY KEY(고객번호)
);

CREATE TABLE 주문(
 주문번호 NUMBER
,주문일시 DATE
,고객번호 NUMBER
,CONSTRAINT 주문_PK PRIMARY KEY(주문번호)
);

CREATE TABLE 고객등급(
 등급코드 VARCHAR2(1)
,등급명 VARCHAR2(100)
,CONSTRAINT 고객등급_PK PRIMARY KEY(등급코드)
);

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

[실행결과]
고객번호  고객명  등급명
------- -------- -------
...
10 행이 선택되었습니다.

[실행계획]
-----------------------------------------------------
| Id | Operation                       | Name       |
-----------------------------------------------------
|  0 | SELECT STATEMENT                |            |
|  1 |  TABLE ACCESS BY INDEX ROWID    | 고객등급   |
|* 2 |    INDEX UNIQUE SCAN            | 고객등급_PK|
|* 3 |  COUNT STOPKEY                  |            |
|  4 |    VIEW                         |            |
|  5 |     NESTED LOOPS SEMI           |            |
|  6 |      TABLE ACCESS BY INDEX ROWID| 고객       |
|* 7 |       INDEX RANGE SCAN          | 고객_X1    |
|* 8 |      INDEX RANGE SCAN           | 주문_X1    |
-----------------------------------------------------

문제. 아래 쿼리를 튜닝하시오.

1. 쿼리를 튜닝하시오. 힌트로만 가능하면 힌트만 작성하시오. (성능 효과가 있는 핵심 부분만 힌트로 제어하시오. 부적절한 힌트사용은 감점있음)
2. 인덱스를 적절하게 구성하시오.(성능효과가 미비한 인덱스 구성시 감점있음)

select b.*, c.*
from b16 b, c16 c
where b.no = c.no
and b.type = 'zz123'
and exists(select 'x'
             from d16 d
            where d.cls = b.cls
              and d.type = 'df100'
              and d.order_dt is null)
Rows	Row Source Operation
------- -----------------------------------------------------------
    50  FILTER
  7777	  NESTED LOOPS
  6666		  TABLE ACCESS BY INDEX ROWID B16
  6666         INDEX RANGE SCAN IDX_B_01
  7777      TABLE ACCESS BY INDEX ROWID C16
  7777         INDEX RANGE SCAN IDX_C_01
114161    TABLE ACCESS BY INDEX ROWID D16
114161      INDEX RANGE SCAN IDX_D_01 
------- -----------------------------------------------------------

문제. 고객별로 당월, 전월, 전전월 주문 건수를 집계하는 아래 SQL의 성능을 최적화 하시오.

• 튜닝 후에 실행계획이 변경되지 않도록 힌트를 기술
• 전체고객수는 200만명
• 주문상태 코드값이 03 (주문완료) 인 주문건수는 500만건
• 병렬 사용 금지

[인덱스]

[튜닝 전]

SELECT A.고객번호, A.고객명
      , COUNT(DISTINCT B.주문번호) AS 당월주문건수 
      , COUNT(DISTINCT C.주문번호) AS 전월주문건수 
      , COUNT(DISTINCT D.주문번호) AS 전전월주문건수 
FROM 고객 A 
     ,주문 B 
     ,주문 C  
     ,주문 D 
WHERE B.고객번호(+) = A.고객번호 
  AND B.주문일자(+) LIKE '202107%'
  AND B.주문상태코드(+) = '03' 
  AND C.고객번호(+) = A.고객번호
  AND C.주문일자(+) LIKE '202106%'
  AND C.주문상태코드(+) = '03' 
  AND D.고객번호(+) = A.고객번호
  AND D.주문일자(+) LIKE '202105%'
  AND D.주문상태코드(+) = '03' 
GROUP BY A.고객번호, A.고객명 
 HAVING COUNT(DISTINCT B.주문번호) + COUNT(DISTINCT C.주문번호) 
+ COUNT(DISTINCT D.주문번호) > 0
 ;

--------------------------------------------- 
[실행계획]
---------------------------------------------
ID | OPERATION                        | NAME
---------------------------------------------
0  |SELECT STATEMENT                  |
1  | FILTER                           |
2  |  SORT GROUP BY                   | 
3  |   NESTED LOOPS OUTER             |
4  |    NESTED LOOPS OUTER            |  
5  |     NESTED LOOPS OUTER           |    
6  |      TABLE ACCESS FULL           | 고객  
7  |      TABLE ACCESS BY INDEX ROWID | 주문 
8  |       INDEX RANGE SCAN           | 주문_X01 
9  |     TABLE ACCESS BY INDEX ROWID  | 주문
10 |      INDEX RANGE SCAN            | 주문_X01 
11 |    TABLE ACCESS BY INDEX ROWID   | 주문 
12 |     INDEX RAGNE SCAN             | 주문_X01 
----------------------------------------------