출처 : cafe.naver.com/sqlpd/5323
------------------------------------------------------------------------------------------------
1. 아래의 테이블 정보와 요구조건을 만족하는 SQL 을 작성하시오
단, 조회/다음 버튼과 전체 버튼(파일 내려받기) 에 대한 SQL 은 각각 작성하고 필요한 경우
INDEX 를 생성하시오
------------------------------------------------------------------------------------------------
1) TABLE 정보
고객 : 100,000 건
--------------
#고객번호 (PK)
*고객명
*등록일자
*고객상태코드 ('AC' 건수 : 10,000 건)
*연락처
고객접속이력
- 10,000,000건
- 접속일자로 월별 파티션
--------------
#접속일자 (PK1)
#고객번호 (PK2)
*기타1
2) 전제조건
- 조회 정보 : 고객번호, 고객명 , 연락처, 등록일자, 최근접속일자
- 조회 요건
+ 최근접속일자 : 한 달 이내 접속일자 중 가장 최근접속일자
-> TRUNC(ADD_MONTHS(SYSDATE, -1)
+ 조회/다음 버튼 시 한 페이지 출력 건수 : 20건
+ 전체 버튼 시 출력 건수 : 모든 데이터
+ 정렬 : 등록일자, 고객번호 순으로 정렬하여 출력
+ 조회/다음 버튼 시 빠른 응답속도 중요
- 기타 조건
+ 인덱스가 삭제되어도 결과 집합에 영향이 없어야 함
+ VIEW MERGE, UNNEST, JOIN PUSH PREDICATE 쿼리변환이 불가능한 DBMS 버전임
**** 예시 답안 ****
1. 문제 의도
- 페이지 처리를 통한 TOP N 알고리즘을 이용한 SQL 작성
- 인덱스 삭제 시 결과 정합성 유지가 전제조건이므로 INDEX 힌트를 통한 ROWNUM=1 은 원천 차단
- 고객상태코드가 전체 데이터의 10% 정도로 단일 조건 인덱스 스캔과 FULL SCAN 의 경계는 모호함
- TOP N 알고리즘을 이용하기 위한 인덱스 생성
- 빠른 응답속도를 요구한 것으로 Nested loop join 을 통한 부분범위 처리를 기대
- 전체 버튼은 전체 데이터 처리이므로 Hash Join 을 통한 전체 처리를 기대
- 전체 조회 시 고객접속이력이 월별 파티션으로 되어있어 접속일자를 통한 인덱스 처리가 아닌 Full Scan 처리 필요
2. TOP N 알고리즘 --> 맨 뒤 Test 에서 설명
3. 예시 답안
3.1 조회/다음 버튼 SQL
1) 다음 버튼이 빈번히 사용되지 않을 경우
- INDEX 생성
CREATE INDEX IX_고객_01 ON 고객(고객상태코드, 등록일자, 고객번호);
CREATE INDEX IX_고객접속이력_01 ON 고객접속이력(고객번호,접속일자);
- 조회 버튼 클릭 시 next_no 는 1 을 던져주고, 이후 다음 버튼 클릭시 이전 값에 +1 을 넣어 던져줌
SELECT /*+ USE_NL(B) */
A.고객번호, A.고객명, A.연락처, A.등록일자,
MAX(B.접속일자) AS 최근접속일자
FROM
( SELECT ROWNUM RN, X.*
FROM (
SELECT 고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
ORDER BY 등록일자, 고객번호
) X
WHERE ROWNUM <= :next_no * 20
) A,
고객접속이력 B
WHERE 1=1
AND A.고객번호 = B.고객번호(+)
AND B.접속일자(+) >= TRUNC(ADD_MONTHS(SYSDATE,-1))
AND A.RN BETWEEN (:next_no-1)*20 +1 AND :next_no*20
GROUP BY A.고객번호, A.고객명, A.연락처, A.등록일자
ORDER BY A.등록일자, A.고객번호;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.005 0 0 0 0
Fetch 2 0.016 0.004 10 47 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.010 10 47 0 20
Misses in library cache during parse : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : LSS (ID=91)
Rows Row Source Operation
------- -----------------------------------------------------------------------
20 SORT GROUP BY (cr=47 pr=10 pw=0 time=0 us cost=24000 size=11164980 card=105330)
200 FILTER (cr=47 pr=10 pw=0 time=199 us)
200 NESTED LOOPS OUTER (cr=47 pr=10 pw=0 time=199 us cost=21464 size=11164980 card=105330)
20 VIEW (cr=4 pr=0 pw=0 time=266 us cost=173 size=968149 card=10639)
20 COUNT STOPKEY (cr=4 pr=0 pw=0 time=0 us)
20 VIEW (cr=4 pr=0 pw=0 time=0 us cost=173 size=829842 card=10639)
20 TABLE ACCESS BY INDEX ROWID 같같 (cr=4 pr=0 pw=0 time=0 us cost=173 size=861759 card=10639)
20 INDEX RANGE SCAN IX_같같_01 (cr=3 pr=0 pw=0 time=38 us cost=46 size=0 card=10639)
200 INDEX RANGE SCAN IX_같같좋세응렁_01 (cr=43 pr=10 pw=0 time=42 us cost=2 size=150 card=10)
2) 다음 버튼이 빈번히 사용될 경우
- INDEX 생성
CREATE INDEX IX_고객_01 ON 고객(고객상태코드, 등록일자, 고객번호);
CREATE INDEX IX_고객접속이력_01 ON 고객접속이력(고객번호,접속일자);
- 조회 버튼 클릭 시 이전등록일자와 이전고객번호는 NULL 값을 던져주고, 다음버튼 클릭 시 조회 버튼 클릭간 나온
가장 나중 값을 파라미터로 던져줌
SELECT /*+ LEADING(A) USE_NL(B) */
A.고객번호, A.고객명, A.연락처, A.등록일자,
MAX(B.접속일자) AS 최근접속일자
FROM
( SELECT ROWNUM RN, X.*
FROM (
SELECT 고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
AND 등록일자 >= NVL(TO_DATE(:이전등록일자,'YYYYMMDD HH24:MI:SS'), 등록일자-1)
AND 고객번호 > NVL(:이전고객번호, ' ') -- 스페이스 한칸 (모든 조건 만족)
ORDER BY 등록일자, 고객번호
) X
WHERE ROWNUM <= 20
) A,
고객접속이력 B
WHERE 1=1
AND A.고객번호 = B.고객번호(+)
AND B.접속일자(+) >= TRUNC(ADD_MONTHS(SYSDATE,-1))
GROUP BY A.고객번호, A.고객명, A.연락처, A.등록일자
ORDER BY A.등록일자, A.고객번호;
** 등록일자의 경우 이전등록일자와 동일한 날짜일수도 있으므로 같다로 표시하고
대신 고객번호는 항상 크다로 인식하여 맨 마지막 줄이 중복출력되는 것을 방지함
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.016 0.008 0 0 0 0
Fetch 2 0.000 0.001 0 47 0 20
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.016 0.009 0 47 0 20
Misses in library cache during parse : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : LSS (ID=91)
Rows Row Source Operation
------- -----------------------------------------------------------------------
20 SORT GROUP BY (cr=47 pr=0 pw=0 time=0 us cost=46 size=18414 card=198)
200 NESTED LOOPS OUTER (cr=47 pr=0 pw=0 time=199 us cost=45 size=18414 card=198)
20 VIEW (cr=4 pr=0 pw=0 time=133 us cost=5 size=1560 card=20)
20 COUNT STOPKEY (cr=4 pr=0 pw=0 time=95 us)
20 VIEW (cr=4 pr=0 pw=0 time=0 us cost=5 size=1638 card=21)
20 TABLE ACCESS BY INDEX ROWID 같같 (cr=4 pr=0 pw=0 time=0 us cost=5 size=1701 card=21)
20 INDEX RANGE SCAN IX_같같_01 (cr=3 pr=0 pw=0 time=38 us cost=4 size=0 card=505)
200 INDEX RANGE SCAN IX_같같좋세응렁_01 (cr=43 pr=0 pw=0 time=94 us cost=2 size=150 card=10)
3.2 전체 버튼 SQL
- 월별 파티션으로 FULL SCAN 이 유리함(인덱스 보다)
SELECT /*+ LEADING(A) USE_HASH(B) FULL(B) */
A.고객번호, A.고객명, A.연락처, A.등록일자,
MAX(B.접속일자) AS 최근접속일자
FROM 고객 A,
고객접속이력 B
WHERE 1=1
AND A.고객번호 = B.고객번호(+)
AND A.고객상태코드 = 'AC'
AND B.접속일자(+) >= TRUNC(ADD_MONTHS(SYSDATE,-1))
GROUP BY A.고객번호, A.고객명, A.연락처, A.등록일자
ORDER BY A.등록일자, A.고객번호;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.003 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 101 0.406 0.402 4057 5033 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 103 0.406 0.405 4057 5033 0 10000
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : LSS (ID=91)
Rows Row Source Operation
------- -----------------------------------------------------------------------
10000 SORT GROUP BY (cr=5033 pr=4057 pw=0 time=3589 us cost=14642 size=10111584 card=105329)
100000 HASH JOIN OUTER (cr=5033 pr=4057 pw=0 time=4483842 us cost=12324 size=10111584 card=105329)
10000 TABLE ACCESS BY INDEX ROWID 같같 (cr=164 pr=0 pw=0 time=6075 us cost=173 size=861759 card=10639)
10000 INDEX RANGE SCAN IX_같같_01 (cr=43 pr=0 pw=0 time=2531 us cost=46 size=0 card=10639)
1000000 PARTITION RANGE ITERATOR PARTITION: KEY 12 (cr=4869 pr=4057 pw=0 time=1642389 us cost=12143 size=14864505 card=990967)
1000000 TABLE ACCESS FULL 같같좋세응렁 PARTITION: KEY 12 (cr=4869 pr=4057 pw=0 time=94457 us cost=12143 size=14864505 card=990967)
------------------------------------------------------------------------------------------------
2. 아래의 SQL 를 보고 최적의 성능을 내기 위해 비어있는 Hint 절에 알맞은 Hint 를 적으시오
필요 시 인덱스를 구성하고, 인덱스와 Hint 를 통해 만들어진 최적의 실행계획을 보기를 참고하여
작성하시오
------------------------------------------------------------------------------------------------
1) SQL
SELECT /*+ ( ) */ A.고객번호, A.고객명,A.연락처, A.등록일자
FROM 고객 A
WHERE A.고객상태코드 = 'AC'
AND EXISTS (SELECT /*+ ( ) */ 'X'
FROM 고객상담이력 B
WHERE B.고객번호 = A.고객번호
AND B.접속일자 <= TRUNC(ADD_MONTHS(SYSDATE, -1))
)
AND 등록일자 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
ORDER BY A.등록일자;
2) TABLE 정보
고객 : 100,000 건
--------------
#고객번호 (PK)
*고객명
*등록일자 (등록일자가 한 달이내인 사람은 고객상태코드가 'AC' 인 경우 보통 50% 정도임)
*고객상태코드 ('AC' 건수 : 10,000 건, 그 중 50%가 한 달이내 접속)
*연락처
고객상담이력
- 10,000,000건
--------------
#고객번호 (PK1)
#접속일자 (PK2)
*상담내용
3) 전제조건
- 조회 정보 : 고객번호, 고객명 , 연락처, 등록일자
- 조회 요건
+ 빠른 응답속도 중요
+ ?
4) SQL OPERATION 보기
SORT (ORDER BY)
NESTED LOOPS (SEMI)
HASH JOIN (SEMI)
FILTER
INDEX (UNIQUE SCAN) OF (index_name) (INDEX (UNIQUE))
INDEX (RANGE SCAN) OF (index_name) (INDEX)
INDEX (FULL SCAN) OF (index_name) (INDEX)
TABLE ACCESS (BY INDEX ROWID) OF (table_name) (TABLE)
TABLE ACCESS (FULL) OF (table_name) (TABLE)
**** 예시 답안 ****
1. 문제 의도
- 'AC' 코드로 도출되는 데이터는 10,000건으로 전체 데이터의 10% 선으로 INDEX 를 통한 SCAN 과
FULL SCAN 의 경계가 모호하나 'AC' 인 고객의 경우 등록일자가 한달 이내인 사람은 50% 정도로
10,000건의 50% 인 5,000건 정도임
* 전체 5% 정도로 인덱스 스캔이 유리하므로 인덱스 사용 힌트가 나와야 함
- EXSITS 에 대한 FILTER 처리를 하느냐, SEMI NESTED LOOPS JOIN 또는 SEMI HASH JOIN 을 결정해야 함
* 일반적으로 FILTER 처리 보다는 SEMI HASH JOIN 의 성능이 좋으며 건수가 적을 경우는 SEMI NESTED LOOPS JOIN 이 나음
- 서브쿼리 내에 사용되는 고객상담이력에 대한 적절한 인덱스 구성을 하였는가?
* 서브쿼리가 확인자 역할을 해야 하기 때문에 적절한 인덱스가 필요함 (SEMI JOIN 경우등)
- 등록일자로 ORDER BY 를 하고 있으나 INDEX 구성에 고객상담코드 + 등록일자로 구성할 경우는 SORT ORDER BY 가 나타나지 않음
2. 예시 답안
1) 인덱스 예시 답안
고객 테이블 인덱스 생성
- IX_고객_01 : 고객상태코드 + 등록일자
고객상담이력 테이블 기존 인덱스 사용 (없을 경우 생성)
- PK_고객상담이력 (고객번호 , 접속일자) 사용
2) Hint 예시 답안
SELECT /*+ INDEX(A IX_고객_01) */ A.고객번호, A.고객명,A.연락처, A.등록일자
FROM 고객 A
WHERE A.고객상태코드 = 'AC'
AND EXISTS (SELECT /*+ UNNEST NL_SJ INDEX(B PK_고객상담이력) */ 'X'
FROM 고객상담이력 B
WHERE B.고객번호 = A.고객번호
AND B.접속일자 <= TRUNC(ADD_MONTHS(SYSDATE, -1))
)
AND A.등록일자 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
ORDER BY A.등록일자;
*** UNNEST 는 반드시는 아님. 일반적으로 NL_SJ 등과 같이 쓰이며, FILTER 로 풀고 싶을 때
NO_UNNEST 사용 가능함
3) PLAN 예시 답안
SORT (ORDER BY) -> 고객상태코드 + 등록일자라 나타나지 않을 수 있음(실제로 Oracle 에서 안 나타남)
NESTED LOOPS (SEMI)
TABLE ACCESS (BY INDEX ROWID) OF 고객 (TABLE)
INDEX (RANGE SCAN) OF IX_고객_01 (INDEX)
INDEX (RANGE SCAN) OF PK_고객상담이력 (INDEX)
*** 실제 플랜
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18K Card=9K Bytes=817K)
1 0 NESTED LOOPS (SEMI) (Cost=18K Card=9K Bytes=817K)
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=132 Card=9K Bytes=689K)
3 2 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=28 Card=9K)
4 1 INDEX (RANGE SCAN) OF 'PK_고객상담이력' (INDEX) (Cost=2 Card=9M Bytes=128M)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
3 - access("A"."고객상태코드"='AC' AND "A"."등록일자">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1))) AND "A"."등록일자" IS NOT NULL)
4 - access("B"."고객번호"="A"."고객번호" AND "B"."접속일자"<=TRUNC(ADD_MONTHS(SYSDATE@!,(-1))))
-----------------------------------------------------------
-- TRACE
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 26 0.031 0.016 0 10153 0 2499
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 28 0.031 0.018 0 10153 0 2499
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : LSS (ID=91)
Rows Row Source Operation
------- -----------------------------------------------------------------------
2499 NESTED LOOPS SEMI (cr=10153 pr=0 pw=0 time=7231 us cost=17562 size=836160 card=8710)
5000 TABLE ACCESS BY INDEX ROWID 고객 (cr=127 pr=0 pw=0 time=4874 us cost=132 size=705510 card=8710)
5000 INDEX RANGE SCAN IX_고객_01 (cr=42 pr=0 pw=0 time=2249 us cost=28 size=0 card=8710)
2499 INDEX RANGE SCAN PK_고객상담이력 (cr=10026 pr=0 pw=0 time=0 us cost=2 size=134189775 card=8945985)
* Top N 알고리즘 Test 자료
1. TOP N 알고리즘
- COUNT STOPKEY 를 통해 전체를 읽지 않고 INDEX 를 통한 SORT 후 필요부분 까지 STOP 함
- ORDER BY 바같의 SELECT 절에서 ROWNUM 을 구하고 조건절로 ROWNUM <= :STOPCNT 를 사용할 경우 TOP N 알고리즘이 작동함 (COUNT(STOPKEY)) (예제1)
- ORDER BY 안쪽의 SELECT 절에서 ROWNUM 를 구하고 해당 ROWNUM 의 ALIAS 를 통해 조건을 사용 할 경우 STOPKEY 가 되지 못함 (예제 2)
- ORDER BY 안쪽의 SELECT 절에서 ROWNUM 을 구하고 해당 ROWNUM 의 ALIAS 를 사용하지 않고 ROWNUM <= :STOPCNT 를 사용할 경우 TOP N 알고리즘 작동 (예제3)
- ORDER BY 대신 WINDOW FUNCTION [ROW_NUMBER] 를 사용하여 해당 ALIAS 를 통해 상수 조건을 사용할 경우 TOP N 알고리즘이 작동함 (예제4)
- ORDER BY 대신 WINDOW FUNCTION [ROW_NUMBER] 를 사용하여 해당 ALIAS 를 통해 바인드 조건을 사용할 경우 TOP N 알고리즘이 작동하지 않음 (예제5)
- ORDER BY 대신 WINDOW FUNCTION [ROW_NUMBER] 를 사용하고 ROWNUM <= :STOPCNT 바인드 조건을 사용할 경우 TOP N 알고리즘이 작동하나 ORDER BY 가 없으므로 정렬 안됨
* TOP N 알고리즘 작동시 Buffer Get : 6
TOP N 알고리즘 미작동시 Buffer Get : 166
* 예제 테스트 전 INDEX 생성
CREATE INDEX IX_고객_01 ON 고객(고객상태코드, 등록일자, 고객번호);
예제1)
SELECT ROWNUM RN, X.*
FROM (
SELECT 고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
ORDER BY 등록일자, 고객번호
) X
WHERE ROWNUM <= :next_no * 20;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=173 Card=11K Bytes=499K)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=173 Card=11K Bytes=499K)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=173 Card=11K Bytes=842K)
4 3 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=46 Card=11K)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(:NEXT_NO)*20)
4 - access("고객상태코드"='AC')
-----------------------------------------------------------
예제 2)
SELECT X.*
FROM (
SELECT ROWNUM RN,
고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
ORDER BY 등록일자, 고객번호
) X
WHERE RN <= :next_no * 20;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=173 Card=11K Bytes=634K)
1 0 VIEW (Cost=173 Card=11K Bytes=634K)
2 1 COUNT
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=173 Card=11K Bytes=842K)
4 3 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=46 Card=11K)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - filter("RN"<=TO_NUMBER(:NEXT_NO)*20)
4 - access("고객상태코드"='AC')
-----------------------------------------------------------
예제 3)
SELECT X.*
FROM (
SELECT ROWNUM RN,
고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
ORDER BY 등록일자, 고객번호
) X
WHERE ROWNUM <= :next_no * 20;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=173 Card=11K Bytes=634K)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=173 Card=11K Bytes=634K)
3 2 COUNT
4 3 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=173 Card=11K Bytes=842K)
5 4 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=46 Card=11K)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(:NEXT_NO)*20)
5 - access("고객상태코드"='AC')
-----------------------------------------------------------
예제4)
SELECT X.*
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY 등록일자,고객번호) RN,
고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
) X
WHERE RN <= 20;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=378 Card=11K Bytes=634K)
1 0 VIEW (Cost=378 Card=11K Bytes=634K)
2 1 WINDOW (NOSORT STOPKEY) (Cost=378 Card=11K Bytes=842K)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=173 Card=11K Bytes=842K)
4 3 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=46 Card=11K)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - filter("RN"<=20)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "등록일자","고객번호")<=20)
4 - access("고객상태코드"='AC')
-----------------------------------------------------------
예제 5)
SELECT X.*
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY 등록일자,고객번호) RN,
고객번호, 고객명, 연락처, 등록일자
FROM 고객
WHERE 1=1
AND 고객상태코드 = 'AC'
) X
WHERE RN <= :next_no * 20;
Execution Plan
-----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=378 Card=11K Bytes=634K)
1 0 VIEW (Cost=378 Card=11K Bytes=634K)
2 1 WINDOW (NOSORT) (Cost=378 Card=11K Bytes=842K)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=173 Card=11K Bytes=842K)
4 3 INDEX (RANGE SCAN) OF 'IX_고객_01' (INDEX) (Cost=46 Card=11K)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
1 - filter("RN"<=TO_NUMBER(:NEXT_NO)*20)
4 - access("고객상태코드"='AC')
-----------------------------------------------------------
**** 환경 구성 (문제1)*****
DROP TABLE 고객;
CREATE TABLE 고객
( 고객번호 VARCHAR2(6)
, 고객명 VARCHAR2(50)
, 고객상태코드 VARCHAR2(2)
, 등록일자 DATE
, 연락처 VARCHAR2(11)
);
CREATE INDEX PK_고객 ON 고객(고객번호);
ALTER TABLE 고객 ADD CONSTRAINT PK_고객 PRIMARY KEY (고객번호);
INSERT /*+ APPEND */ INTO 고객
SELECT LPAD(TO_CHAR(ROWNUM), 6, '0') CUST_NO
, LPAD(TO_CHAR(ROWNUM), 50, '0') CUST_NM
, CASE WHEN ROWNUM <= 10000 THEN 'AC' ELSE '99' END STAT_CD
, CASE WHEN ROWNUM <= 5000 THEN TRUNC(SYSDATE -1) ELSE TRUNC(ADD_MONTHS(SYSDATE, -3)) END INPUT_DATE
, '01012341234' PHONE_NO
FROM DUAL
CONNECT BY LEVEL <= 100000;
COMMIT;
DROP TABLE 고객접속이력;
CREATE TABLE 고객접속이력
( 접속일자 DATE
, 고객번호 VARCHAR2(6)
, 기타 VARCHAR2(50)
)
PARTITION BY RANGE (접속일자)
(
PARTITION P201507 VALUES LESS THAN (TO_DATE('20150801', 'YYYYMMDD'))
, PARTITION P201508 VALUES LESS THAN (TO_DATE('20150901', 'YYYYMMDD'))
, PARTITION P201509 VALUES LESS THAN (TO_DATE('20151001', 'YYYYMMDD'))
, PARTITION P201510 VALUES LESS THAN (TO_DATE('20151101', 'YYYYMMDD'))
, PARTITION P201511 VALUES LESS THAN (TO_DATE('20151201', 'YYYYMMDD'))
, PARTITION P201512 VALUES LESS THAN (TO_DATE('20160101', 'YYYYMMDD'))
, PARTITION P201601 VALUES LESS THAN (TO_DATE('20160201', 'YYYYMMDD'))
, PARTITION P201602 VALUES LESS THAN (TO_DATE('20160301', 'YYYYMMDD'))
, PARTITION P201603 VALUES LESS THAN (TO_DATE('20160401', 'YYYYMMDD'))
, PARTITION P201604 VALUES LESS THAN (TO_DATE('20160501', 'YYYYMMDD'))
, PARTITION P201605 VALUES LESS THAN (TO_DATE('20160601', 'YYYYMMDD'))
, PARTITION P201606 VALUES LESS THAN (TO_DATE('20160701', 'YYYYMMDD'))
)
;
CREATE INDEX PK_고객접속이력 ON 고객접속이력(접속일자, 고객번호) LOCAL;
ALTER TABLE 고객접속이력 ADD CONSTRAINT PK_고객접속이력 PRIMARY KEY (접속일자, 고객번호);
INSERT /*+ APPEND */ INTO 고객접속이력
SELECT TRUNC(SYSDATE-R_NUM*3) CONTACT_DATE
, A.고객번호
, '테스트'||'_'||R_NUM AS 기타
FROM 고객 A, (SELECT ROWNUM R_NUM FROM DUAL CONNECT BY LEVEL <= 100) B;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','고객');
EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','고객접속이력',DEGREE=>4);
**** 환경 구성 (문제2)*****
** 문제 1의 테이블 활용
CREATE TABLE 고객상담이력
( 고객번호 VARCHAR2(6)
, 접속일자 DATE
, 상담내용 VARCHAR2(50)
)
;
CREATE UNIQUE INDEX PK_고객상담이력 ON 고객상담이력(고객번호, 접속일자) ;
ALTER TABLE 고객상담이력 ADD CONSTRAINT PK_고객상담이력 PRIMARY KEY (고객번호,접속일자);
INSERT /*+ APPEND */ INTO 고객상담이력
SELECT A.고객번호
, TRUNC(SYSDATE-R_NUM*3)
, '상담내용_메세지_테스트:'||'_'||R_NUM AS 기타
FROM 고객 A, (SELECT ROWNUM R_NUM FROM DUAL CONNECT BY LEVEL <= 100) B
;
-- 217.35
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('LSS','고객상담이력',DEGREE=>4);
-- 데이터 보정 : AC 인 데이터 중 50%는 등록일자가 한달 이후임
UPDATE 고객 A
SET 등록일자 = 등록일자 - 40
WHERE 고객상태코드 = 'AC'
AND ROWNUM < 5001;
COMMIT;
UPDATE 고객상담이력
SET 접속일자 = SYSDATE + 1000 - DBMS_RANDOM.VALUE(1,1000)
WHERE ROWID IN (
SELECT RN
FROM (
SELECT DENSE_RANK() OVER(ORDER BY 고객번호) R, B.ROWID RN
FROM 고객상담이력 B
WHERE 고객번호 IN (SELECT 고객번호 FROM 고객 WHERE 고객상태코드 = 'AC' AND 등록일자 >= TRUNC(ADD_MONTHS(SYSDATE, -1)) )
)
WHERE R >=2500)
;
COMMIT;
'자격증 > SQLP - 기출복원' 카테고리의 다른 글
| SQLP 11회 (0) | 2020.11.11 |
|---|---|
| SQLP 20회 (0) | 2020.11.11 |
| SQLP 37회 (0) | 2020.11.11 |
| SQLP 스터디 자료 (0) | 2020.11.11 |
| SQLP 기출문제 (0) | 2020.11.11 |