SQLP 실기 문제 복원

1. 51회

https://bae9086.tistory.com/563

SQLP 51회 실기 1번,2번문제 복기

 

2. 50회

 

실기1

1번

풀스캔이였고 버퍼 100k 였음

​

답

인덱스 스캔을 유도하기 위한 신규 인덱스 생성

​

2번

실행계획 순서

인덱스스캔 t1_x1 (A-row 10000)

->테이블액세스 t1 (A-row 1000)

​

답

인덱스 컬럼 추가

​

3번

조건(인덱스는 단편화X)

버퍼1000건, 테이블 액세스후 버퍼 1010건

​

답

인덱스 컬럼 순서 변경

​

4번

인덱스스캔->테이블액세스 방향이었음

버퍼10 테이블액세스후 버퍼1000

​

답

테이블 재구성(클러스터링 팩터 초기화)

​

5번

실행계획 순서 (로컬인덱스 사용 -> 테이블 액세스 -> partirion range all 범위(1~1000) )

partirion range all 범위(1~1000)

테이블 액세스

로컬 인덱스 t1_x1 사용

​

답

로컬인덱스 엑세스 했으나 전체 파티션을 엑세스하므로, 조건절에는 파티션키가 없음을 유추할 수 있습니다.

(조건절에 파티션키가 있었다면 파티션 레인지 싱글 혹은 이터레이터가 뜹니다.)

​

인덱스에 파티션키 추가보단 논파티션인덱스가 맞지 않나 싶습니다.

​

​

Execution Plan

--------------------------------------------------------------------------------

0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=11 Card=22 Bytes=304K)

1 0 UNION-ALL

2 1 PARTITION RANGE (ITERATOR) (Cost=4 Card=11 Bytes=506)

3 2 TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF T1 (TABLE) (Cost=4 Card=11 Bytes=506)

4 3 INDEX (RANGE SCAN) OF IDX1 (INDEX) (Cost=1 Card=1K)

​

5 1 PARTITION RANGE (ALL) (Cost=7 Card=11 Bytes=506)

6 5 TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF T1 (TABLE) (Cost=7 Card=11 Bytes=506)

7 6 INDEX (RANGE SCAN) OF IDX1 (INDEX) (Cost=7 Card=6K)

​

​

위가 파티션키가 있을 때, 아래가 파티션키가 없을 때 이며 둘 다 인덱스에 파티션키 미포함입니다.

조건절에 파티션키가 있다면 인덱스에 파티션키 포함여부와 관계없이 플랜에 파티션 레인지 올이 뜨지 않습니다.

위에 플랜 확인하면 답 나오고요.

프리픽스나 논프리픽스 관계없이, 해당 플랜의 쿼리는 조건절에 파티션키가 없음을 유추할 수 잇고,

그래서 인덱스를 논파티션으로 바꿔야한다는겁니다.

​

​

실기2 (1~3번 모두 로깅을 최소화하는 문제)

​

1번

insert into t1 select * from t2

​

답

alter table t1 nologging;

insert문에 APPEND 삽입

해설

append 힌트는 테이블이 nologging일 때만 작동합니다.

​

2번

조건(c1,c2 는 둘다 not null)

update t1 set c2 = case when c1 < trunc(add_months(sysdate,2)) then 'Y' else c2 end

​

답

update t1 set c2 = 'Y'

where c1 < trunc(add_months(sysdate,2))

and c2 <> 'Y' ;

​

해설

CASE 조건절 WHERE절로 변경하여 C2 <> 'Y'인 행은 갱신안되도록 변경

​

3번

아래쿼리의 조건은 t1 테이블의 95퍼가량 데이터 삭제하는 쿼리이다.

delete from t1 partition(20402) where c2='Y'

​

답

​

답안1

CTAS 임시테이블생성 -> TRUNCATE -> INSERT로 변경

​

create table tmp nologging

as select * from t where 1=2;

​

insert /*+ append */ into tmp

select * from t partition(p202402) where c1 <>'Y';

​

alter table t truncate partition p202402;

​

alter table t nologging;

​

insert /*+ append */ into t

select * from tmp;

​

alter table t logging;

​

drop table tmp;

​

​

* 임시 테이블 생성시 nologging로 생성

* 임시테이블에서 t1으로 인서트 전에도 테이블 노로깅이 추가되야 한다고 봅니다.

​

답안2

alter table t truncate partition p202402

 

3. 44회

※ 실제 시험에는 ERD와 ROW SOURCE GENERATION 정보가 같이 나왔음.

[ 단답식 ]

단1] 쪼갤수 없는 의미 ?속성
단2] 컬럼정보 주고 주식별자로 적절한 속성쓰시오직원번호
단3] GROUP BY로 풀어쓴 쿼리 주고 ROLLUP으로 변환ROLLUP(A.상품번호, A.상품금액)
단4] 계층형 쿼리 결과쓰기
단5] 버퍼캐시는 ___알고리즘LRU
단6] 인덱스 구성 변경 시 A_ROWS 맞추기
단7] 파티셔닝 인덱스 생성하기 문법CREATE INDEX TMP_X01 ON TAB(COL1,COL2) __;LOCAL
단8] 테이블, 결과주고 빈칸채우기 (ROWS vs. RANGE)SELECT 상품코드, sum(금액) over(order by 금액 ___ between 0 preceding and 50 following) from A;rows
단9] alter table tab1 ____ ;nologging
단10] 쿼리레벨 옵티마이저 모드 설정/+ FIRST_ROWS /

 

1번

실1) 최근 등록일자 5 rows 를 뽑는 쿼리 튜닝하기
체크포인트
1. select-list에 clob 형이 있음.(맨 끝 SELECT로 빼야될지? -> 어차피 SORT 생략으로 굳이 필요없었음.)
2. SORT ORDER BY 생략하기 -> 인덱스 생성

(실기1) 아래와 같이 테이블구조가 있는데 기존SQL를 개선하고, 필요시 인덱스를 설계변경 할 것
불필요하게 인덱스 변경시 감점

[테이블 정보]
* BOARD : #BRD_ID, BRD_NM, ...
* ARTICLE : #ART_SN, ART_TIL, ART_CONT, MBR_NO, BRD_ID, REG_DT,  ...
* MEMBER : #MBR_NO, MBR_NM, ...

[인덱스]
BOARD_PK : BRD_ID
ARTICLE_PK : ART_SN
MEMBER_PK : MBR_NO
ARTICLE_X01 : BRD_ID
ARTICLE_X02 : MBR_NO

[정보]
BOARD 테이블 : 10건
최근 3일 또는 5일전 게시글 : 850여 건
MEMBER 테이블 : 5000건(?)

[기존SQL]
SELECT
*
FROM
(SELECT
B.BRD_ID
, B.BRD_NM
, A.ART_SN
, A.ART_TIL
, GET_TEXT(A.ART_CONT,100) AS ART_CONT
, A.MBR_NO
, M.MBR_NM
, A.REG_DT
FROM BOARD B, ARTICLE A, MEMBER M
WHERE B.BRD_ID = :BRD_ID
AND A.BRD_ID = B.BRD_ID
AND A.MBR_NO = M.MBR_NO
AND A.REG_DT >= TRUNC(SYSDATE,-3)
ORDER BY A.REG_DT DESC
)
WHERE ROWNUM <= 5;

제출답안

· 인덱스 재구성 : ARTICLE_X01 : BRD_ID, REG_DTM

SELECT *
FROM (SELECT /*+ LEADING(B A M) USE_NL(A M) INDEX_DESC(A ARTICLE_X01) */B.BRD_ID, B.BRD_NM, A.ART_SN, A.ART_TIL, GET_TEXT(A.ART_CONT,100) AS ART_CONT, A.MBR_NO, M.MBR_NM,A.REG_DT
		FROM BOARD B, ARTICLE A, MEMBER M
	   WHERE B.BRD_ID = :BRD_ID
		AND A.BRD_ID = B.BRD_ID
		AND A.MBR_NO = M.MBR_NO
		AND A.REG_DT >=TRUNC(SYSDATE,-3)
	ORDER BY A.REG_DT DESC
)
WHERE ROWNUM<=5;

해설(내생각)

• 문제점
  기존 ARTICLE_X01(BRD_ID) 구성으로는 BRD_ID(게시판번호)에 들어있는 게시글을 전부 읽고 5ROW를 뽑는다.
• 튜닝포인트
  1) ORDER BY 하나의 테이블에 한 컬럼으로 정렬하는 간단한 구성으로, 인덱스 생성해 충분히 sort order by를 제거 할 수 있다.
  2) B.BRD_ID 가 PK이기때문에 B->A 순서로 접근해도 SORT ORDER BY 가 생략된다.
  (A 먼저보면 불필요하게 B를 5번 스캔함.)
• 추가 개선 여지
  1) CLOB형 컬럼이 있는데 개선포인트?
  딱히 찾지못했음. 어차피 5ROW 읽어야되고 sort order by 제거하면 크게 문제되지 않을것으로 생각됨.
  2) SELECT-LIST에 참여하지 않거나 ERD상으로 불필요 테이블이 있는지?
  불필요 테이블이 있는지 확인했는데 모두 필요한 테이블들.
  3) 조건 추가 할만한게 있는지?
  A 먼저 봐야됐다면 A.BRD_ID = :BRD_ID를 추가해야겠지만, B.BRD_ID가 PK이기에 불필요.

---

---

2번

실2) 대용량 파티셔닝 테이블 조인 쿼리 튜닝하기
체크포인트
1. not in 절을 in 절로 바꿔서 INDEX RANGE SCAN 유도
2. 불필요 조인 테이블 있으면 제거 (1:M 에서 1쪽이고, SELECT-LIST에서 참조안해 제거가능)
3. 파티션 인덱스 설계(LOCAL/GLOBAL, PREFIXED/NON-PREFIXED, NON-PARTITION)

(실기2) 아래와 같이 테이블 구조가 있는데, 기존SQL를 개선하고, 필요시 인덱스를 생성할 것
(생성시 인덱스 및 파티셔닝이 필요하면 local partition 또는 global partition 등 작성, PK는 변경할 수 없음)

[테이블 정보]
* COMPANY : #CMP_ID, CMP_NM, ...
* ORDER : #ORD_NO, ORD_DT, CMP_ID, STAT_CD
* ORD_MATERIAL : #ORD_NO, #MTR_ID, ORD_DT, NUM_QTY, AMT_QTY, DC_QTY
* MATERIAL : #MTR_ID, MTR_NM, ...

[인덱스]
COMPANY_PK : CMP_ID
ORDER_PK : ORD_NO
ORDER_X01 : STAT_CD, ORD_DT
ORD_MATERIAL_PK : ORD_NO, MTR_ID
MATERIAL_PK : MTR_ID

[정보]
ORDER : 약 150여 만건 → STAT_CD 01(5%), 02(5%), 03(40%), 04(50%) 이외의 값은 존재하지 않음
ORD_MATERIAL : 연간 1500여 만건 / ORD_DT가 ORDER 테이블을 반정규화 하여 사용하고 있음 / 연단위로 ORD_DT로 파티셔닝 되어 있음
COMPANY : 5천건
MATERIAL : 1만건
주로 최근 1년을 자주 조회함

[기존SQL]
SELECT
A.ORD_NO
, C.CMP_ID
, C.CMP_NM
, SUM(B.NUM_QTY) AS NUM_QTY
, SUM(B.AMT_QTY) AS AMT_QTY
, SUM(B.DC_QTY) AS DC_QTY
FROM ORDER A, ORD_MATERIAL B, COMPANY C, MATERIAL D
WHERE
A.STAT_CD NOT IN ('03','04')
AND A.ORD_DT BETWEEN TO_DATE(:START_DT,'YYYY-MM-DD') AND TO_DATE(:END_DT,'YYYY-MM-DD')
AND A.ORD_NO = B.ORD_NO
AND A.CMP_ID = C.CMP_ID
AND A.MTR_ID = D.MTR_ID
GROUP BY A.ORD_NO, C.CMP_ID, C.CMP_NM;

제출답안

· 신규 인덱스 생성 : ORD_MATERIAL_X01 : ORD_NO, ORD_DT (LOCAL NON-PREFIXED)

SELECT /*+ LEADING(A B C) USE_NL(B C) INDEX(B ORD_MATERIAL_X01) INDEX(A ORDER_X01) */ A.ORD_NO, C.CMP_ID, C.CMP_NM, SUM(B.NUM_QTY) AS NUM_QTY, SUM(B.AMT_QTY) AS AMT_QTY, SUM(B.DC_QTY) AS DC_QTY
FROM ORDER A, ORD_MATERIAL B, COMPANY C
WHERE A.STAT_CD IN ('01','02')
AND A.ORD_DT BETWEEN TO_DATE(:START_DT,'YYYY-MM-DD') AND TO_DATE(:END_DT,'YYYY-MM-DD')
AND A.ORD_NO = B.ORD_NO
AND A.CMP_ID = C.CMP_ID
AND A.ORD_DT = B.ORD_DT
GROUP BY A.ORD_NO, C.CMP_ID, C.CMP_NM;

해설(내생각)

• 문제점
  1) STAT_CD 에 '01', '02' 는 전체 중 10% 뿐이나 NOT IN 으로 인해 풀스캔중임.
  -> 너무 IN으로 바꾸고 싶음.
  2) NL JOIN(ORD_NO + ORD_DT) VS HASH JOIN(ORD_DT)
  STAT_CD 10% 라는 부분과 최근 1년 조회가 많다는 걸 보고 전자를 택함
  IF) ORD_DT를 선두로한다면 다른 모든 주문을 불필요하게 읽어야함.
• 튜닝포인트
  1) STAT_CD NOT_IN을 IN으로 변경해 INDEX SCAN 유도
  ORD_NO이 150만건 테이블이고 최대기간 검색 시 10%인 15만건 정도면 INDEX 스캔해도 괜찮아보임.
  2) ORD_MATERIAL 테이블 인덱스 신규생성 및 A.ORD_DT = B.ORD_DT 조건추가
  ORD_NO + ORD_DT LOCAL로 구성하면 ORD_NO으로 접근 후 파티션 PRNUING가능
  자주 들어오는 연단위조건이 들어올 시 한개의 파티션만 보면됨.
  3) 불필요 테이블 제거하기 : MATERIAL 조인 제거
  SELECT-LIST에 참조하지도 않고, ERD상 ORD_MATERIAL은 반드시 하나의 MATERIAL을 포함하기 때문에 제거가능(EXISTS도 불필요.)
• 추가 개선여지
  1) A,B 조인 후 GROUP BY 하고나서 COMPANY C와 조인