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

안녕하세요. 지난번 시험을 친 실기문제에 대해서 복기를 하도록하겠습니다.

그리고 저는 이번시험으로 합격을 해서 이번 시험복기가 마지막일거 같습니다. 이번 실기 점수는 30점 중 20점을 맞았습니다.

 

 

1번문제는 상황을 주고 TO-BE의 실행계획을 주며, 실행계획처럼 나오게 쿼리를 작성하라는 문제였습니다.

2번문제는 상황을 주고 그것에 맞게 쿼리와 명령어를 작성하라는 문제였습니다.(핵심은 exchange partition)

 

1번 실행계획을 보고 실행계획처럼 나오게 쿼리를 작성하시오.

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

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

 

DDL문

CREATE TABLE YSBAE.T1
(
    DT  DATE,
    ID  VARCHAR2(10),
    CD  VARCHAR2(10),
    V1  NUMBER
)
;

CREATE UNIQUE INDEX YSBAE.T1_PK
ON YSBAE.T1 (DT,ID) 
;

ALTER TABLE YSBAE.T1
ADD CONSTRAINT T1_PK PRIMARY KEY (DT,ID);


CREATE TABLE YSBAE.T2
(
    DT  DATE,
    ID  VARCHAR2(10),
    CD  VARCHAR2(10),
    V1  NUMBER
)
;

CREATE UNIQUE INDEX YSBAE.T2_PK
ON YSBAE.T2 (DT,ID) 
;

ALTER TABLE YSBAE.T2
ADD CONSTRAINT T2_PK PRIMARY KEY (DT,ID);

CREATE TABLE YSBAE.T3
(
    ID  VARCHAR2(10),
    NM  VARCHAR2(10)
)
;

CREATE UNIQUE INDEX YSBAE.T3_PK
ON YSBAE.T3 (ID) 
;

ALTER TABLE YSBAE.T3
ADD CONSTRAINT T3_PK PRIMARY KEY (ID);


CREATE TABLE YSBAE.T4
(
    ID  VARCHAR2(10),
    NM  VARCHAR2(10)
)
;

CREATE UNIQUE INDEX YSBAE.T4_PK
ON YSBAE.T4 (ID) 
;

ALTER TABLE YSBAE.T4
ADD CONSTRAINT T4_PK PRIMARY KEY (ID);

 

AS-IS 쿼리

select a.tp,a.nm
from
(
select *
from
(
select 1 as tp, a.id,b.nm
from t1 a, t3 b
where a.id = b.id
and a.dt = sysdate
union all
select 2 as tp, a.id,b.nm
from t2 a, t3 b
where a.id = b.id
and a.dt = sysdate
)
order by tp,nm ) a
where exists (select 1 from t4 where id = a.id) 
and rownum <= 10;

 

TO-BE 실행계획

---------------------------------------------------------------------------
| Id  | Operation                   | Name  | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |        |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |      1 |    14 |     0   (0)|
|*  2 |   INDEX UNIQUE SCAN         | T3_PK |      1 |       |     0   (0)|
|*  3 |  COUNT STOPKEY              |       |        |       |            |
|   4 |   VIEW                      |       |      2 |    20 |     0   (0)|
|   5 |    UNION-ALL                |       |        |       |            |
|*  6 |     COUNT STOPKEY           |       |        |       |            | <---
|   7 |      VIEW                   |       |      1 |    10 |     0   (0)|
|   8 |       NESTED LOOPS          |       |      1 |    23 |     0   (0)|
|*  9 |        INDEX RANGE SCAN     | T1_PK |      1 |    16 |     0   (0)|
|* 10 |        INDEX UNIQUE SCAN    | T4_PK |      1 |     7 |     0   (0)|
|* 11 |     COUNT STOPKEY           |       |        |       |            | <---
|  12 |      VIEW                   |       |      1 |    10 |     0   (0)|
|  13 |       NESTED LOOPS          |       |      1 |    23 |     0   (0)|
|* 14 |        INDEX RANGE SCAN     | T2_PK |      1 |    16 |     0   (0)|
|* 15 |        INDEX UNIQUE SCAN    | T4_PK |      1 |     7 |     0   (0)|
---------------------------------------------------------------------------

 

실행계획 특징으로는 아래와 같습니다.

1) 기존 쿼리의 조인(t1,t3 와 t2,t3) 에서 -> (t1,t4와 t2,t4)로 변경

2) 각 VIEW마다 COUNT STOPKEY가 있고 UNION-ALL 후에 COUNT STOPKEY가 있음. 총 3번의 COUNT STOPKEY

3) T3는 마지막에 보임 -> (스칼라서브쿼리로 처리)

 

내가 생각한 답

select a.tp,(select nm from t3 where id = a.id) as nm 
from
(
select *
from
(
select 1 as tp,a.id
from t1 a,t4 b
where a.id = b.id
and a.dt = sysdate
order by tp,id
)
where rownum <= 10
union all
select *
from
(
select 2 as tp,a.id
from t2 a,t4 b
where a.id = b.id
and a.dt = sysdate
order by tp,id
)
where rownum <= 10
) a
where rownum <= 10
;

 

이와 같은 유형의 문제가 직전부터 계속해서 나오고 있는거 같습니다. 

명확한 정답을 주고 그거에 맞게 쿼리를 작성하는 문제라 최근에 많이 나오는것 같습니다.

 

2번문제 실행계획을 보고 실행계획처럼 나오게 쿼리를 작성하시오.

배치가 update 90% delete 10% update 10% 수행되는데, 성능향상을 위해 파티션 exchange 문을 사용해서 작성하라.

t1_p202501로 테이블명 작성

 

DDL

t2,t3,t4는 1번문제의 DDL과 동일하여 t1만 파티션테이블로 전환하는 스크립트로 작성합니다.

drop table t1;
create table t1
(
    DT  DATE,
    ID  VARCHAR2(10),
    CD  VARCHAR2(10),
    V1  NUMBER
)
partition by range(dt)
(
partition p202501 values less than (to_date('2025/02/01 00:00:00','YYYY/MM/DD HH24:MI:SS')),
partition p202502 values less than (to_date('2025/03/01 00:00:00','YYYY/MM/DD HH24:MI:SS')),
partition p202503 values less than (to_date('2025/04/01 00:00:00','YYYY/MM/DD HH24:MI:SS'))
);


create unique index t1_pk on t1(dt,id) local;

 

AS-IS 쿼리

merge into t1 a
using t2 b
on (a.id = b.id and a.dt = b.dt)
when matched then
update a.v1 = a.v1 + b.v1
delete cd <= 100
when not matches then
insert into (dt,id,v1)
values (b.dt,b.id,b.v1)
;

 

AS-IS 쿼리의 특징으로는 아래와 같습니다.

a.id = b.id and a.dt = b.dt를 조회하여 같은값이면 update 후에 delete를 하고, 다르면 insert를 하는 merge 문입니다.

 

 

 

To-BE 실행계획

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |            |        |       |     2 (100)|          |       |       |
|   1 |  LOAD TABLE CONVENTIONAL      | T1_P202501 |        |       |            |          |       |       |
|   2 |   NESTED LOOPS OUTER          |            |      1 |    65 |     2   (0)| 00:00:01 |       |       |
|   3 |    PARTITION RANGE ALL        |            |      1 |    36 |     2   (0)| 00:00:01 |     1 |     3 |
|*  4 |     TABLE ACCESS FULL         | T1         |      1 |    36 |     2   (0)| 00:00:01 |     1 |     3 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2         |      1 |    29 |     0   (0)|          |       |       |
|*  6 |     INDEX UNIQUE SCAN         | T2_PK      |      1 |       |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------------

 

실행계획의 특징으로는 아래와 같습니다.

이 실행계획이 실제 문제와 정확히 기억이 나지 않지만, OUTER JOIN으로 t1,t2를 조회하고 있었습니다.

그리고 merge 문에서는 delete,update,insert가 모두 발생했었지만, 실행계획에서는 insert만 발생하고 있었습니다.

그리고 문제는 테이블 생성 -> 쿼리 작성하여 insert -> partition exchange -> drop table 까지의 과정을 모두 적어야 했습니다.

 

내가 생각한 답

 

1) t1_p202501 생성

create table t1_p202501 
(
    DT  DATE,
    ID  VARCHAR2(10),
    CD  VARCHAR2(10),
    V1  NUMBER
);

create unique index t1_p202501_pk on t1_p202501(dt,id) ;

 

2)insert

insert into t1_p202501
select
(case when a.dt is not null and a.id is not null then a.dt else b.dt end) dt,
(case when a.dt is not null and a.id is not null then a.id else b.id end) id,
(case when a.dt is not null and a.id is not null then a.v1 + b.v1 else b.v1 end) v1
from t1 a,t2 b
where a.dt = b.dt(+)
and a.id = b.id(+)
and a.cd > 100;

 

commit;

 

3)partition exchange

alter table t1 exchange partition p202501 with table t1_p202501 including indexes without validation;

 

4)drop table

drop table t1_p202501;

 

- exchange partition의 구문을 틀리게 적었었습니다.

아래명령어만 적어도 exchange partition은 동작합니다. 뒤의 including indexes 와 without validation은 옵션인데 답에서는 이부분까지는 없어도 무방하지 않을까 생각합니다..

alter table t1 exchange partition p202501 with table t1_p202501; 

 

-insert 쿼리자체를 t1과 t2의 outer조인으로 하여 처리를 하게 했습니다. 그래서 select 부분에 case when부분으로 기존의 확인부분이었던 dt와 id의 값의 null여부에 따라 그냥 t1값을 넣을지 update나 insert시에 넣어야 되는 값을 넣을지 작성하였습니다.

(이렇게 접근하는것이 맞는지를 잘 모르겠네요. 어느부분에서 틀렸는지 알수가 없어서..)

 

2번문제는..맞추기가 어려웠던 문제였습니다. 일반적으로 쿼리를 작성하는 것뿐만아니라 실제 DBA가 자주 쓰는 명령어인 exchange partition 명령어까지 알고 있어야 풀 수 있기 때문입니다.