(0) 실기
47번 (162 page)
[인덱스 재구성]
주문상품_X1 : 할인유형코드 + 주문일시
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
FROM 주문상품 A
WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND 할인유형코드 = 'K890'
GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드
48번 (163 page)
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
FROM 주문상품 A
WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND 할인유형코드 = 'K890'
GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드
49번 (164 page)
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
FROM 주문상품 A
WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND 할인유형코드 = 'K890'
GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY 총 주문금액 DESC , 상품코드
⭐️ 50번 ⭐️ (165 page , order by 2번 !!!)
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
FROM 주문상품 A
WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND 할인유형코드 = 'K890'
GROUP BY 상품코드
ORDER BY 총 주문금액 DESC , 상품코드) O , 상품 P
WHERE O.상품코드 = P.상품코드
AND ROWNUM <=100
ORDER BY 총 주문금액 DESC , 상품코드
⭐️ 51번 ⭐️ (p.166 , 대부분의 상품이 스캔이 됨)
- ⭐️ 속성이 많아도 부분범위 처리 하므로 많이 안읽는다. ⭐️
[인덱스 추가]
상품_X1 :등록일시
[인덱스 재구성]
주문상품_X1 :할인유형코드 + 주문일시
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액
FROM 주문상품 A
WHERE 주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND 할인유형코드 = 'K890'
GROUP BY 상품코드 ) O , 상품 P
WHERE O.상품코드 = P.상품코드
ORDER BY P.등록일시 DESC
⭐️ 52번 ⭐️ (p.167 , no_merge 안 썼음)
- ⭐️ 서브쿼리 너무 많으면 의심해바야한다. 이건 no_merge 안 썼음 ⭐️
[인덱스 재구성]
주문상품_X1 : 할인유형코드 + 주문일시
[인덱스 추가]
상품_X1 : 등록일시 + 상품코드
SELECT
P.상품코드 , P.상품명 , P.상품가격 , O.총주문수량 , O.총주문금액
FROM ( SELECT 상품코드 ,SUM(O.주문수량) 총주문수량 , SUM(O.주문금액) 총주문금액 , MIN(B.등록일시) 등록일시
FROM 주문상품 A , 상품 B
WHERE A.주문일시 >= ADD_MONTHS(SYSDATE,-1)
AND A.할인유형코드 = 'K890'
AND B.상품코드 = A.상품코드
GROUP BY A.상품코드
ORDER BY 등록일시 DESC) O , 상품 P
WHERE ROWNUM <= 100
AND O.상품코드 = P.상품코드
53번 (아우터조인 , p.168)
- ⭐️ 일자 비교 (최근 한달) ⭐️ : 일자 >= trunc(add_months(sysdate,-1))
- NVL2 함수는 NULL이 아닌 경우 지정값1을 출력하고, NULL인 경우 지정값2를 출력한다.
- 함수 : NVL2("값", "지정값1", "지정값2") // NVL2("값", "NOT NULL", "NULL")
[인덱스 추가]
작업지시_X1 : 작업자ID + 실제 방문일자
select *
from (SELECT
A.작업일련번호 , A.실제방문일자 ,
NVL2(A.개통접수번호,'개통','장애') 접수구분,
NVL2(A.개통접수번호,B.고객번호,C.고객번호) 고객번호 ,
NVL2(A.개통접수번호,B.주소,C.주소) 주소
FROM 작업지시 A , 개통접수 B , 장애접수 C
WHERE 작업자ID = 'Z123456'
AND B.개통접수번호(+) = 개통접수번호
AND C.장애접수번호(+) = 장애접수번호
AND A.작업일자 >= TRUNC(ADD_MONTHS(SYSDATE , -1))
ORDER BY 실제방문일자 DESC )
WHERE ROWNUM <= 10;
54번
- 적절한 인덱스가 있어 ⭐️ UNION ALL 처리 ⭐️
SELECT 작업일련번호 , 작업자ID , '개통' 작업구분, 고객번호 , 주소
FROM 작업지시 A , 개통접수 B
WHERE A.작업구분코드 = 'A'
AND 방문예정일자 = TO_CHAR(sysdate , 'YYYYMMDD')
AND A.접수번호 = B.개통접수번호
UNION ALL
SELECT 작업일련번호 , 작업자ID , '장애' 작업구분, 고객번호 , 주소
FROM 작업지시 A , 장애접수 C
WHERE A.작업구분코드 = 'B'
AND 방문예정일자 = TO_CHAR(sysdate , 'YYYYMMDD')
AND A.접수번호 = C.장애접수번호
55번
- 적절한 인덱스가 없어 필터로 넘기면서 ⭐️ 한꺼번에 조인 처리 ⭐️
SELECT 작업일련번호 , 작업자ID ,
DECODE(작업구분코드,'A','개통','B','장애') 작업구분 ,
DECODE(작업구분코드,'A',B.고객번호,'B',C.고객번호) 고객번호 ,
DECODE(작업구분코드,'A',B.주소,'B',C.주소) 주소
FROM 작업지시 A , 개통접수 B , 장애접수 C
WHERE A.방문예정일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
AND B.개통접수번호(+) = DECODE(작업구분코드,'A',A.접수번호)
AND C.장애접수번호(+) = DECODE(작업구분코드,'B',A.접수번호);
56번
- 날짜형 일시 비교(오늘) : 일시 >= TRUNC(sysdate) and 일시 TRUNC (sysdate+1)
- 인덱스에 작업 구분 코드 추가할 것 !
- union all 처리
[인덱스 생성]
작업지시_X1 : 접수구분코드 + 접수번호
개통접수_X1 : 접수일자 + 접수번호
장애접수_X1 : 접수일자 + 접수번호
SELECT A.작업일련번호 , A.작업자ID , '개통' AS 작업구분, B.고객번호 , B.주소
FROM 작업지시 A , 개통접수 B
WHERE A.작업구분코드 = 'A'
AND B.개통접수일시 >= TRUNC(SYSDATE)
AND B.개통접수일시 < TRUNC(SYSDATE+1)
AND A.접수번호 = B.개통접수번호
UNION ALL
SELECT A.작업일련번호 , A.작업자ID , '장애' AS 작업구분, B.고객번호 , B.주소
FROM 작업지시 A , 장애접수 B
WHERE A.작업구분코드 = 'B'
AND B.장애접수일시 >= TRUNC(SYSDATE)
AND B.장애접수일시 < TRUNC(SYSDATE+1)
AND A.접수번호 = B.장애접수번호
57번
SELECT P.장비번호 , P.장비명 , A.상태코드 AS 최종상태코드
, A.변경일자 AS 최종변경일자 , A.변경순번 AS 최종변경순번
FROM 장비 P , 상태변경이력 A
WHERE P.장비구분코드 = 'A001'
AND A.장비번호 = P.장비번호
AND (P.변경일자 , P.변경순번) = (
SELECT 변경일자 , 변경순번
FROM (
SELECT 변경일자 , 변경순번
FROM 상태변경이력
WHERE 장비번호 = A.장비번호
ORDER BY 변경일자 DESC , 변경순번 DESC
)
WHERE ROWNUM <=1 )
ORDER BY P.장비번호;
58번
SELECT P.장비번호 , P.장비명 , P.최종상태코드 , A.상태코드 AS 직전상태코드 ,
A.변경일자 AS 직전변경일자 , A.변경순번 AS 직전변경순번
FROM 장비 P , 상태변경이력 A
WHERE P.장비구분코드 = 'A001'
AND A.장비번호 = P.장비번호
AND (A.변경일자 , A.변경순번) =
(SELECT 변경일자 , 변경순번
FROM (
SELECT 변경일자 , 변경순번
FROM 상태변경이력
WHERE 변경일자 < P.최종상태변경일자
AND 장비번호 = P.장비번호
ORDER BY 변경일자 DESC , 변경순번 DESC)
WHERE ROWNUM <= 1)
ORDER BY P.장비번호
59번
SELECT P.장비번호 , P.장비명 , A.상태코드 AS 최종상태코드
, TO_CHAR(A.유효시작일시,'YYYYMMDD') AS 최종상태변경일자
FROM 장비 P , 상태변경이력 A
WHERE P.장비구분코드 = 'A001'
AND A.장비번호 = P.장비번호
AND P.유효시작일자 <= SYSDATE
AND P.유효종료일자 > SYSDATE
ORDER BY P.장비번호;
60번
SELECT P.장비번호 , P.장비명 , P.최종상태코드 , H.상태코드 AS 직전상태코드
, TO_CHAR(P.최종상태변경일시,'YYYYMMDD') AS 최종상태변경일자
, TO_CHAR(H.유효시작일시,'YYYYMMDD') AS 직전변경일자
FROM 장비 P , 상태변경이력 H
WHERE P.장비구분코드 = 'A001'
AND H.장비번호 = P.장비번호
AND H.유효시작일시 < P.최종상태변경일시
AND H.유효종료일시 >= P.최종상태변경일시 - 1/(60*60*24)
ORDER BY P.장비번호
(1) NL조인
(2) 소트 머지 조인
(3) 해시 조인
32번
- ⭐️ swap_join_inputs 힌트 중요 !!
(4) 스칼라 서브 쿼리
(5) 고급 조인 기법