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

지난주 주말에 시험을 본 52회 SQLP의 실기문제에 대해서 정리해보도록 하겠습니다.

 

1번,2번 문제는 동일한 유형이었으며, 테이블 ddl과 기존 SQL을 보여주고 수정후의 실행계획만을 보여준 후 수정후의 실행계획이 나오도록 쿼리를 수정하도록 하는 문제였습니다.

 

1번 문제

수정후의 실행계획을 보고 실행계획처럼 나오도록 쿼리를 수정하기.

 - 인덱스 수정이 필요하면 정확한 구문을 작성하기(drop index ,create index)

- 힌트를 사용하여 정확하게 의도한대로 표현되도록 하기

##1. 주문
create table 주문(
주문번호 number not null,
고객번호 varchar2(11) not null,
주문일시 date,
배송번호 number,
constraint 주문_pk primary key (주문번호)
);
insert into 주문
select round(dbms_random.value(10000000000,99999999999),0) as 주문번호,dbms_random.string('U',11) as 고객번호,(sysdate - level/3600) as 주문일시,round(dbms_random.value(10000000000,99999999999),0) 배송번호  from dual connect by level <= 1000;
commit;

##2.상품이력
create table 상품이력 (
상품번호 number not null,
시작일시 date not null,
종료일시 date not null,
이벤트명 varchar2(10) not null,
상품금액 number,
constraint 상품이력_pk primary key (상품번호)
);


insert into 상품이력
select round(dbms_random.value(10000000000,99999999999),0) as 상품번호, 주문일시 as 시작일시,  주문일시 + 1 as 종료일시 ,'SQLP' 이벤트명,round(dbms_random.value(1000,1000000),0) as 상품금액
from 주문;
commit;

##3.주문상품
create table 주문상품(
주문번호 number not null,
상품번호 number not null,
주문수량 number,
constraint 주문상품_pk primary key (주문번호,상품번호)
);

insert into 주문상품
with tmp_상품이력 as(
select 상품번호,row_number() over(order by 상품번호) as rn
from 상품
where 상품번호 is not null
),
tmp_주문 as(
select 주문번호,row_number() over(order by 주문번호) as rn
from 주문
where 주문번호 is not null
)
select a.주문번호,b.상품번호,round(dbms_random.value(1,100),0) as 주문수량
from tmp_주문 a,tmp_상품이력 b
where a.rn = b.rn;

commit;

##4.배송
create table 배송 (
배송번호 number not null,
배송상태코드 varchar2(5),
constraint 배송_pk primary key (배송번호)
);

insert into 배송
select 배송번호,dbms_random.string('U',5) as 배송상태코드
from 주문;

commit;

##인덱스
create index 주문_x1 on 주문(고객번호,주문일시);
create index 상품이력_x1 on 상품이력(상품번호,시작일시);

 

수정전 SQL

select a.주문번호, a.주문일시,a.주문금액,a.배송상태코드
from
(
select a.주문번호,a.주문일시,(b.주문수량 * c.상품금액) as 주문금액, 
(select 배송상태코드 from 배송 where 배송번호 = a.배송번호) as 배송상태코드
,dense_rank() over (order by a.주문일시 desc) rnum
from 주문 a,주문상품 b, 상품이력 c
where a.고객번호 ='00000000042'
and b.주문번호 = a.주문번호
and c.상품번호(+) = b.상품번호
and a.주문일시 between c.시작일시(+) and c.종료일시(+)
and c.이벤트명(+) = 'SQLP'
) a
where rnum <= 10;

 

수정전 실행계획

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=52)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '배송' (TABLE) (Cost=2 Card=1 Bytes=17)
   2    1     INDEX (UNIQUE SCAN) OF '배송_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   3    0   VIEW (Cost=6 Card=1 Bytes=52)
   4    3     WINDOW (NOSORT STOPKEY) (Cost=6 Card=1 Bytes=132)
   5    4       NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=132)
   6    5         NESTED LOOPS (Cost=3 Card=1 Bytes=81)
   7    6           TABLE ACCESS (BY INDEX ROWID) OF '주문' (TABLE) (Cost=1 Card=1 Bytes=42)
   8    7             INDEX (RANGE SCAN DESCENDING) OF '주문_X1' (INDEX) (Cost=1 Card=1)
   9    6           TABLE ACCESS (BY INDEX ROWID) OF '주문상품' (TABLE) (Cost=2 Card=1 Bytes=39)
  10    9             INDEX (RANGE SCAN) OF '주문상품_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  11    5         TABLE ACCESS (BY INDEX ROWID) OF '상품이력' (TABLE) (Cost=1 Card=1 Bytes=51)
  12   11           INDEX (UNIQUE SCAN) OF '상품이력_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - access("배송번호"=:B1)
   3 - filter("RNUM"<=10)
   4 - filter(DENSE_RANK() OVER ( ORDER BY INTERNAL_FUNCTION("A"."주문일시") DESC )<=10)
   8 - access("A"."고객번호"='00000000042')
  10 - access("B"."주문번호"="A"."주문번호")
  11 - filter("C"."이벤트명"(+)='SQLP' AND "A"."주문일시">="C"."시작일시"(+) AND "A"."주문일시"<="C"."종료일시"(+))
  12 - access("C"."상품번호"(+)="B"."상품번호")
-----------------------------------------------------------

 

수정 후 실행계획

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=125)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF '배송' (TABLE) (Cost=2 Card=1 Bytes=17)
   2    1     INDEX (UNIQUE SCAN) OF '배송_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   3    0   NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=125)
   4    3     NESTED LOOPS (Cost=3 Card=1 Bytes=74)
   5    4       VIEW (Cost=1 Card=1 Bytes=35)
   6    5         COUNT (STOPKEY)
   7    6           VIEW (Cost=1 Card=1 Bytes=35)
   8    7             TABLE ACCESS (BY INDEX ROWID) OF '주문' (TABLE) (Cost=1 Card=1 Bytes=42)
   9    8               INDEX (RANGE SCAN DESCENDING) OF '주문_X1' (INDEX) (Cost=1 Card=1)
  10    4       TABLE ACCESS (BY INDEX ROWID BATCHED) OF '주문상품' (TABLE) (Cost=2 Card=1 Bytes=39)
  11   10         INDEX (RANGE SCAN) OF '주문상품_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  12    3     TABLE ACCESS (BY INDEX ROWID BATCHED) OF '상품이력' (TABLE) (Cost=2 Card=1 Bytes=51)
  13   12       INDEX (RANGE SCAN) OF '상품이력_X2' (INDEX) (Cost=1 Card=1)
-----------------------------------------------------------


Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - access("배송번호"=:B1)
   6 - filter(ROWNUM<=10)
   9 - access("고객번호"='00000000042')
  11 - access("A"."주문번호"="B"."주문번호")
  12 - filter("C"."이벤트명"(+)='SQLP')
  13 - access("B"."상품번호"="C"."상품번호"(+) AND "A"."주문일시"<="C"."종료일시"(+) AND "A"."주문일시">="C"."시작일시"(+))
  13 - filter("A"."주문일시"<="C"."종료일시"(+))
-----------------------------------------------------------

 

이부분에서 제가 포인트라고 생각했던 부분입니다.

- 10건 추출 시 count(STOPKEY)로 부분범위처리로 처리

- view로 주문테이블을 따로 먼저 처리한 후 차례대로 조인

- 주문_X1 인덱스를 사용해서 부분범위처리로 수행(order by 절이 있음에도 SORT order by 연산 제거하기)

- 새로운 인덱스 상품이력_X2 인덱스가 보임.

 

제가 생각한 답

##인덱스 수정

drop index 주문_x1;
create index 주문_x1 on 주문(고객번호,주문일시);

create index 상품이력_x2 on 상품이력(상품번호,시작일시,종료일시);

 

##쿼리 수정

select /*+ leading(a) use_nl(b) use_nl(c) index(c 상품이력_x2) index(b 주문상품_pk) */
a.주문번호,a.주문일시,b.주문수량 * c.상품금액 as 주문금액, 
(select /*+ no_unnest */배송상태코드 from 배송 where 배송번호 = a.배송번호) as 배송상태코드
from
(
select *
from
(
select /*+ no_merge index_desc(a 주문_x1) */
 주문번호,주문일시,배송번호
from 주문 
where 고객번호 ='00000000042'
order by 주문일시 desc
)
where rownum <= 10
) a,주문상품 b, 상품이력 c
where a.주문번호 = b.주문번호
and b.상품번호 = c.상품번호 (+)
and a.주문일시 >= c.시작일시 (+)
and a.주문일시 <= c.종료일시 (+)
and c.이벤트명(+) = 'SQLP'
;

 

 

2번

수정후의 실행계획을 보고 실행계획처럼 나오도록 쿼리를 수정하기.

 - from 절의 테이블순서를 a,b,c,d,e,f 로 유지하기.

- 힌트를 사용하여 정확하게 의도한대로 표현되도록 하기

 

개인적으로 from 절의 순서를 고정시키라는 문장이 헷갈렸습니다. 분명 수정후의 실행계획을 보면 from절에 그대로 6개 테이블을 작성하면 안나올거 같았거든요..

 

##기존 생성 테이블 삭제
drop table 주문;
drop table 주문상세;
drop table 고객;
drop table 상품;
drop table 코드상세;
drop table 주문통계;

##1.주문
create table 주문(
주문번호 varchar2(16) not null,
고객번호 varchar2(11) not null,
주문일시 date,
constraint 주문_pk primary key (주문번호)
)
partition by range (주문번호)
(
partition p202501 values less than ('202502'),
partition p202502 values less than ('202503'),
partition p202503 values less than ('202504'),
partition p202504 values less than ('202505'),
partition p202505 values less than ('202506'),
partition p202506 values less than ('202507'),
partition p202507 values less than ('202508'),
partition p202508 values less than ('202509'),
partition p202509 values less than ('202510'),
partition p202510 values less than ('202511'),
partition p202511 values less than ('202512'),
partition p202512 values less than ('202601')
);

insert into 주문 
select '2025'||lpad(to_char(mod(level,12)+1),2,'0')||to_char(round(dbms_random.value(0000000000,9999999999),0)) as 주문번호,to_char(round(dbms_random.value(10000000000,99999999999),0)) as 고객번호,(sysdate - level/3600) as 주문일시
from dual connect by level <= 1000;
commit;

##2.주문상세
create table 주문상세(
주문번호 varchar2(16) not null,
상품번호 varchar2(10) not null,
constraint 주문상셰_pk primary key (주문번호)
)
partition by range (주문번호)
(
partition p202501 values less than ('202502'),
partition p202502 values less than ('202503'),
partition p202503 values less than ('202504'),
partition p202504 values less than ('202505'),
partition p202505 values less than ('202506'),
partition p202506 values less than ('202507'),
partition p202507 values less than ('202508'),
partition p202508 values less than ('202509'),
partition p202509 values less than ('202510'),
partition p202510 values less than ('202511'),
partition p202511 values less than ('202512'),
partition p202512 values less than ('202601')
);

insert into 주문상세
with tmp_상품 as(
select 상품번호,row_number() over(order by 상품번호) as rn
from 상품
where 상품번호 is not null
),
tmp_주문 as(
select 주문번호,row_number() over(order by 주문번호) as rn
from 주문
where 주문번호 is not null
)
select a.주문번호,b.상품번호
from tmp_주문 a,tmp_상품 b
where a.rn = b.rn;
commit;


##3.고객
create table 고객 (
고객번호 varchar2(11) not null,
고객명 varchar2(20) not null,
고객유형 varchar2(5) ,
constraint 고객_pk primary key (고객번호)
);

insert into 고객
select 고객번호,dbms_random.string('U',20) as 고객명,(case when mod(고객번호,2) = 1 then 'AAAAA' end) as 고객유형
from 주문;

select * from 고객;

##4.상품
create table 상품 (
상품번호 varchar2(10) not null,
상품유형 varchar2(5),
constraint 상품_pk primary key (상품번호)
);

insert into 상품
select to_char(round(dbms_random.value(0000000000,9999999999),0)) as 상품번호, (case when mod(level,2) = 0 then 'SSSSS' end) as 상품유형
from dual
connect by level <= 1000;
commit;

##5.코드상세
create table 코드상세 (
코드상세유형 varchar(5) not null,
코드구분 varchar2(5) not null
);

insert into 코드상세
select  (case when mod(level,2) = 0 then 'SSSSS' else 'AAAAA' end) 코드상세유형, (case when mod(level,2) = 0 then 'A01' else 'A02' end) as 코드상세
from dual connect by level <= 1000;

commit;

##6.주문통계
create table 주문통계 (
주문일시 date,
고객유형 varchar2(5),
고객유형명 varchar2(5),
상품유형 varchar2(5),
상품유형명 varchar2(5),
주문수량 number
);

 

수정전  SQL

insert into 주문통계
select trunc(a.주문일시) as 주문일시,b.고객유형,e.코드상세유형 as 고객유형명,d.상품유형,f.코드상세유형 as 상품유형명 ,count(*) 주문수량
from 주문 a,고객 b,주문상세 c, 상품 d,코드상세 e,코드상세 f
where substr(a.주문번호,1,6) in ('202501','202502')
and b.고객번호 = a.고객번호
and c.주문번호 = a.주문번호
and d.상품번호 = c.상품번호
and e.코드구분(+) = 'A01'
and e.코드상세유형(+) = b.고객유형
and f.코드구분(+) = 'A02'
and f.코드상세유형(+) = d.상품유형
group by trunc(a.주문일시),b.고객유형,e.코드상세유형,d.상품유형,f.코드상세유형
;

Execution Plan
-----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=ALL_ROWS (Cost=3K Card=1 Bytes=81)
   1    0   LOAD TABLE CONVENTIONAL OF '주문통계'
   2    1     HASH (GROUP BY) (Cost=3K Card=1 Bytes=81)
   3    2       HASH JOIN (OUTER) (Cost=3K Card=1 Bytes=81)
   4    3         HASH JOIN (OUTER) (Cost=3K Card=1 Bytes=73)
   5    4           NESTED LOOPS (Cost=3K Card=1 Bytes=65)
   6    5             NESTED LOOPS (Cost=3K Card=1 Bytes=65)
   7    6               NESTED LOOPS (Cost=3K Card=1 Bytes=54)
   8    7                 NESTED LOOPS (Cost=3K Card=1 Bytes=43)
   9    8                   PARTITION RANGE (ALL) (Cost=3K Card=1 Bytes=26)
  10    9                     TABLE ACCESS (FULL) OF '주문' (TABLE) (Cost=3K Card=1 Bytes=26)
  11    8                   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF '주문상세' (TABLE) (Cost=1 Card=1 Bytes=17)
  12   11                     INDEX (UNIQUE SCAN) OF '주문상셰_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  13    7                 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1 Card=1 Bytes=11)
  14   13                   INDEX (UNIQUE SCAN) OF '상품_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  15    6               INDEX (UNIQUE SCAN) OF '고객_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  16    5             TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=1 Card=1 Bytes=11)
  17    4           TABLE ACCESS (FULL) OF '코드상세' (TABLE) (Cost=3 Card=1 Bytes=8)
  18    3         TABLE ACCESS (FULL) OF '코드상세' (TABLE) (Cost=3 Card=1 Bytes=8)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   3 - access("F"."코드상세유형"(+)="D"."상품유형")
   4 - access("E"."코드상세유형"(+)="B"."고객유형")
  10 - filter(SUBSTR("A"."주문번호",1,6)='202501' OR SUBSTR("A"."주문번호",1,6)='202502')
  12 - access("C"."주문번호"="A"."주문번호")
  14 - access("D"."상품번호"="C"."상품번호")
  15 - access("B"."고객번호"="A"."고객번호")
  17 - filter("E"."코드구분"(+)='A01')
  18 - filter("F"."코드구분"(+)='A02')
-----------------------------------------------------------

 

수정 후 실행계획

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1K Card=1 Bytes=43)
   1    0   HASH JOIN (RIGHT OUTER) (Cost=1K Card=1 Bytes=43)
   2    1     TABLE ACCESS (FULL) OF '코드상세' (TABLE) (Cost=3 Card=1 Bytes=8)
   3    1     HASH JOIN (RIGHT OUTER) (Cost=1K Card=1 Bytes=35)
   4    3       TABLE ACCESS (FULL) OF '코드상세' (TABLE) (Cost=3 Card=1 Bytes=8)
   5    3       VIEW (Cost=1K Card=1 Bytes=27)
   6    5         HASH (GROUP BY) (Cost=1K Card=1 Bytes=65)
   7    6           HASH JOIN (Cost=1K Card=1 Bytes=65)
   8    7             TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=5 Card=1000 Bytes=11K)
   9    7             HASH JOIN (Cost=1K Card=1 Bytes=54)
  10    9               TABLE ACCESS (FULL) OF '상품' (TABLE) (Cost=3 Card=1000 Bytes=11K)
  11    9               PARTITION RANGE (ITERATOR) (Cost=1K Card=1 Bytes=43)
  12   11                 HASH JOIN (Cost=1K Card=1 Bytes=43)
  13   12                   TABLE ACCESS (FULL) OF '주문' (TABLE) (Cost=548 Card=1 Bytes=26)
  14   12                   TABLE ACCESS (FULL) OF '주문상세' (TABLE) (Cost=548 Card=1 Bytes=17)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   1 - access("A"."상품유형"="F"."코드상세유형"(+))
   2 - filter("F"."코드구분"(+)='A02')
   3 - access("A"."고객유형"="E"."코드상세유형"(+))
   4 - filter("E"."코드구분"(+)='A01')
   7 - access("A"."고객번호"="B"."고객번호")
   9 - access("C"."상품번호"="D"."상품번호")
  12 - access("A"."주문번호"="C"."주문번호")
  13 - filter("A"."주문번호">='202501')
  14 - filter("C"."주문번호">='202501')
-----------------------------------------------------------

 

이 부분에서 제가 생각했던 포인트입니다.

- partition range all -> partition range (iterator) --> substr(a.주문번호,1,6) in ('202501','202502') 조건절 수정.

- VIEW 와 Hash(GROUP BY) 로 보아 아래 4개의 테이블을 view로 미리 처리하고 코드상세 테이블과 조인할 것.

- 테이블이 모두 FULL TABLE SCAN 인것과 테이블끼리 조인은 모두 HASH JOIN인 것

 

제가 생각한 답

insert into 주문통계
select /*+ leading(A) use_hash(e) use_hash(f) full(e) full(f) swap_join_inputs(e) swap_join_inputs(f) */
 A.주문일시,A.고객유형,e.코드상세유형 as 고객유형명,A.상품유형,f.코드상세유형 as 상품유형명,A.주문수량
from
(
select /*+ no_merge leading(a c d b) use_hash(c) use_hash(d) use_hash(b) swap_join_inputs(d) swap_join_inputs(b) full(a) full(b) full(c) full(d) */
trunc(a.주문일시) as 주문일시, b.고객유형, d.상품유형, count(*) 주문수량
from 주문 a,고객 b,주문상세 c, 상품 d
where a.주문번호 >= '202501' and a.주문번호 < '202503'
and a.고객번호 = b.고객번호
and a.주문번호 = c.주문번호
and c.상품번호 = d.상품번호
group by trunc(a.주문일시), b.고객유형,d.상품유형

) A, 코드상세 e,코드상세 f
where 1=1
and e.코드구분(+) = 'A01'
and A.고객유형 = e.코드상세유형(+)
and f.코드구분(+) = 'A02'
and A.상품유형 = f.코드상세유형(+)
;

이상입니다.

 

제가 실제 답변에서는 2번에서 swap_join_inputs으로 순서를 조정하는 부분을 힌트로 강제하지 못했네요.. 복기해보고 보니 해당 힌트가 필요하다는 것을 알았습니다.