1번 문제.
테이블 ,인덱스를 참고하여 실행계획이 똑같이 나오게 쿼리를 작성하시오. (힌트는 실행계획이 똑같이 되게)
주문일시가 1시간 이내의 최근주문 1000건 중 2번이상 주문된 상품명과 주문합계수량,주문일시를 쿼리로 합계주문수량 내림차순으로 정렬.
테이블 정보
1)
CREATE TABLE YSBAE."상품"
(
"상품번호" NUMBER,
"상품명" VARCHAR2(10),
"고객번호" NUMBER
)
TABLESPACE USERS
NOCOMPRESS;
ALTER TABLE YSBAE."상품"
ADD PRIMARY KEY ("상품번호");
2)
CREATE TABLE YSBAE."주문1"
(
"주문번호" NUMBER,
"주문일시" DATE
)
TABLESPACE USERS
NOCOMPRESS;
ALTER TABLE YSBAE."주문1"
ADD PRIMARY KEY ("주문번호");
create index 주문_x01 on 주문1(주문일시);
3)
CREATE TABLE YSBAE."주문상품"
(
"주문번호" NUMBER,
"상품번호" NUMBER
)
TABLESPACE USERS
STORAGE
(
INITIAL 64K
NEXT 1M
)
NOCOMPRESS;
ALTER TABLE YSBAE."주문상품"
ADD PRIMARY KEY ("주문번호");
테스트 데이터 1000건씩 생성
insert into 상품
select abs(dbms_random.random) as 상품번호,dbms_random.string('A',10),mod(level,10) from dual connect by level <= 1000;
commit;
insert into 주문1
select abs(dbms_random.random) as 주문번호,(sysdate - level/3600) as tt from dual connect by level <= 1000;
commit;
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 주문1
where 주문번호 is not null
)
select a.주문번호,b.상품번호
from tmp_주문 a,tmp_상품 b
where a.rn = b.rn;
commit;
실행계획
이부분은 실제문제부분이 기억나지 않지만 복기중에 최대한 비슷하다고 생각되게 만들었습니다.
--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | 상품 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0081814 |
| 3 | SORT ORDER BY | |
| 4 | HASH GROUP BY | |
| 5 | NESTED LOOPS | |
| 6 | NESTED LOOPS | |
| 7 | VIEW | |
| 8 | HASH GROUP BY | |
|* 9 | VIEW | |
|* 10 | WINDOW SORT PUSHED RANK | |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| 주문1 |
|* 12 | INDEX RANGE SCAN | 주문_X01 |
|* 13 | INDEX RANGE SCAN | 주문상품_X1 |
| 14 | TABLE ACCESS BY INDEX ROWID | 주문상품 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("상품번호"=:B1)
9 - filter("RN"<=1000)
10 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("주문일시") DESC )<=1000)
12 - access("주문일시">=SYSDATE@!-.0416666666666666666666666666666666666667)
13 - access("A"."주문번호"="B"."주문번호")
작성한 답변
select sum(c.주문수량) as 주문합계수량,c.주문일시, (select 상품명 from 상품 where c.상품번호 = 상품번호) 상품명
from
(
select /*+ INDEX(a) INDEX(b) leading(a) use_nl(b) */
b.상품번호,a.주문일시 ,count(b.주문번호) as "주문수량"
from 주문1 a,주문상품 b
where a.주문번호 = b.주문번호
and a.주문일시 >= sysdate - 1/24
group by b.상품번호,a.주문일시 having count(*) >=2
order by 주문수량 desc) c
where rownum <= 1000
group by c.주문일시,c.상품번호
order by 주문합계수량 desc
실행계획
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1512 (100)| | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | 상품 | 1 | 18 | 2 (0)| 00:00:01 | | | |
|* 2 | INDEX UNIQUE SCAN | SYS_C0081814 | 1 | | 1 (0)| 00:00:01 | | | |
| 3 | SORT ORDER BY | | 354 | 9912 | 1512 (1)| 00:00:01 | 4096 | 4096 | 4096 (0)|
| 4 | HASH GROUP BY | | 354 | 9912 | 1512 (1)| 00:00:01 | 1071K| 1071K| 1399K (0)|
|* 5 | COUNT STOPKEY | | | | | | | | |
| 6 | VIEW | | 500 | 14000 | 1009 (1)| 00:00:01 | | | |
|* 7 | SORT ORDER BY STOPKEY | | 500 | 14500 | 1009 (1)| 00:00:01 | 4096 | 4096 | 4096 (0)|
| 8 | HASH GROUP BY | | 500 | 14500 | 1009 (1)| 00:00:01 | 1246K| 1246K| 1404K (0)|
| 9 | NESTED LOOPS | | 500 | 14500 | 1007 (0)| 00:00:01 | | | |
| 10 | NESTED LOOPS | | 1000 | 14500 | 1007 (0)| 00:00:01 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| 주문1 | 1 | 15 | 3 (0)| 00:00:01 | | | |
|* 12 | INDEX RANGE SCAN | 주문_X01 | 1 | | 2 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | 주문상품_X1 | 1000 | | 4 (0)| 00:00:01 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | 주문상품 | 1000 | 14000 | 1004 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("상품번호"=:B1)
5 - filter(ROWNUM<=1000)
7 - filter(ROWNUM<=1000)
12 - access("A"."주문일시">=SYSDATE@!-.0416666666666666666666666666666666666667)
13 - access("A"."주문번호"="B"."주문번호")
생각한 1번 문제의 답
select /*+ leading(a) index(a) index(b) use_nl(b) */ sum(a.주문수량) 주문합계수량,a.주문일시,(select 상품명 from 상품 where b.상품번호 = 상품번호) 상품명
from
(
select 주문번호,주문일시,count(주문번호) as 주문수량
from
(
select 주문번호,주문일시,row_number() over (order by 주문일시 desc) as rn
from 주문1
where 주문일시 >= sysdate - 1/24
)
where rn <= 1000
group by 주문번호,주문일시 having count(*) >=2
)a, 주문상품 b
where a.주문번호 = b.주문번호
group by a.주문일시,b.상품번호
order by 주문합계수량 desc
실행계획
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2012 (100)| | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | 상품 | 1 | 18 | 2 (0)| 00:00:01 | | | |
|* 2 | INDEX UNIQUE SCAN | SYS_C0081814 | 1 | | 1 (0)| 00:00:01 | | | |
| 3 | SORT ORDER BY | | 708 | 29736 | 2012 (1)| 00:00:01 | 4096 | 4096 | 4096 (0)|
| 4 | HASH GROUP BY | | 708 | 29736 | 2012 (1)| 00:00:01 | 1071K| 1071K| 1399K (0)|
| 5 | NESTED LOOPS | | 1000 | 42000 | 1009 (1)| 00:00:01 | | | |
| 6 | NESTED LOOPS | | 1000 | 42000 | 1009 (1)| 00:00:01 | | | |
| 7 | VIEW | | 1 | 28 | 5 (40)| 00:00:01 | | | |
| 8 | HASH GROUP BY | | 1 | 15 | 5 (40)| 00:00:01 | 1246K| 1246K| 1405K (0)|
|* 9 | VIEW | | 1000 | 15000 | 4 (25)| 00:00:01 | | | |
|* 10 | WINDOW SORT PUSHED RANK | | 1 | 15 | 4 (25)| 00:00:01 | 9216 | 9216 | 8192 (0)|
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| 주문1 | 1 | 15 | 3 (0)| 00:00:01 | | | |
|* 12 | INDEX RANGE SCAN | 주문_X01 | 1 | | 2 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | 주문상품_X1 | 1000 | | 4 (0)| 00:00:01 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | 주문상품 | 1000 | 14000 | 1004 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("상품번호"=:B1)
9 - filter("RN"<=1000)
10 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("주문일시") DESC )<=1000)
12 - access("주문일시">=SYSDATE@!-.0416666666666666666666666666666666666667)
13 - access("A"."주문번호"="B"."주문번호")
작성한 답변은 1000건을 추출할때 rownum <=을 사용했습니다. 결과는 같을 수 있지만 rownum을 사용시 COUNT STOPKEY operation이 발생하게되는데 이건 실기문제에 없었습니다. 따라서 rownum을 사용하는게 아니고 row_number()를 사용해서 1000건을 추출하는 방식으로 처리되게 하는게 맞다고 생각하였습니다.
2번문제.
테이블 ,인덱스를 참고하여 실행계획이 똑같이 나오게 쿼리를 작성하시오. (힌트는 실행계획이 똑같이 되게)
성별,주문일자,상품번호별 주문 수량을 구하여 주문통계정보 insert하기
- 주문상세의 주문수량은 문제에서 요구하는 주문수량과 관계없음
테이블정보
create table 주문
(주문번호 varchar2(20),
순번 number,
주문일시 date
)
partition by range(주문번호)
(
partition 주문_p1 values less than ('20240631'),
partition 주문_p2 values less than ('20240730'),
partition 주문_p3 values less than ('20240831'),
partition 주문_p4 values less than ('20240930'),
partition 주문_max values less than (maxvalue)
);
create index 주문_pk on 주문(주문번호) local;
create table 주문상세
(주문번호 varchar2(20),
주문수량 number, --## 이건 사용 x
상품번호 number,
고객번호 number,
주문일시 date
)
partition by range (주문번호)
(
partition 주문상세_p1 values less than ('20240631'),
partition 주문상세_p2 values less than ('20240730'),
partition 주문상세_p3 values less than ('20240831'),
partition 주문상세_p4 values less than ('20240930'),
partition 주문상세_max values less than (maxvalue)
);
create index 주문상세_pk on 주문상세(주문번호) local;
create table 고객
(고객번호 number,
성별 varchar2(10)
);
create table 주문통계정보
(성별 varchar2(10),
주문일자 varchar2(8),
상품번호 number,
합계주문수량 varchar2(10)
);
각 테이블변 1000건씩 데이터 생성
insert into 주문
select to_char(sysdate - 2/level,'YYYYMMDDHH24MISS')||level,level as 순번,sysdate - level/24 from dual connect by level <= 1000;
insert into 주문상세
select 주문번호,round(dbms_random.value(0,10)) 주문수량,abs(dbms_random.random) as 상품번호,abs(dbms_random.random) as 고객번호,주문일시 from 주문;
commit;
insert into 고객
select 고객번호,substr((case when mod(고객번호,2) = 0 then '남' else '여' end),0,1) from 주문상세;
commit;
실행계획
이 부분도 정확히 기억나지는 않습니다. 다만 핵심은 join filter create(bloom filter)가 실행계획에 들어가 있었고 그것을 어떻게 표현할것인가가 점수를 가르는 부분이었다고 생각합니다.
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
|* 2 | FILTER | |
|* 3 | HASH JOIN | |
| 4 | PART JOIN FILTER CREATE | :BF0000 |
| 5 | PARTITION RANGE ALL | |
|* 6 | TABLE ACCESS FULL | 주문 |
|* 7 | HASH JOIN | |
| 8 | TABLE ACCESS FULL | 고객 |
| 9 | PARTITION RANGE JOIN-FILTER| |
|* 10 | TABLE ACCESS FULL | 주문상세|
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('20240930')>=TO_DATE('20240901'))
3 - access("A"."주문번호"="B"."주문번호")
6 - filter(("A"."주문일시">='20240901' AND "A"."주문일시"<='20240930'))
7 - access("B"."고객번호"="D"."고객번호")
10 - filter(("B"."주문일시">='20240901' AND "B"."주문일시"<='20240930'))
작성한 답변
insert into 주문통계정보
select /*+ no_swap_join_inputs(d) use_hash(d) */d.성별,c.주문일자,c.상품번호,c.주문수량
from
(
select /*+ no_merge swap_join_inputs(a) use_hash(b) index(a) index(b) */
to_char(a.주문일시,'YYYYMMDD') 주문일자,b.고객번호,b.상품번호,count(*) as 주문수량
from 주문 a, 주문상세 b
where a.주문번호 = b.주문번호
and a.주문일시 >= '20240901' and a.주문일시 <= '20240930'
and b.주문일시 >= '20240901' and b.주문일시 <= '20240930'
group by a.주문일시,b.고객번호,b.상품번호
) c, 고객 d
where c.고객번호 = d.고객번호
order by 상품번호
실행계획
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4142 (100)| | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | 주문통 | | | | | | | | | |
|* 2 | HASH JOIN | | 590 | 33040 | 4142 (1)| 00:00:01 | | | 1281K| 1281K| 1407K (0)|
| 3 | VIEW | | 590 | 26550 | 4139 (1)| 00:00:01 | | | | | |
| 4 | SORT GROUP BY | | 590 | 38940 | 4139 (1)| 00:00:01 | | | 73728 | 73728 | |
|* 5 | FILTER | | | | | | | | | | |
| 6 | PARTITION RANGE ALL | | 590 | 38940 | 4138 (1)| 00:00:01 | 1 | 5 | | | |
|* 7 | HASH JOIN | | 590 | 38940 | 4138 (1)| 00:00:01 | | | 1152K| 1152K| 1663K (0)|
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| 주문 | 590 | 15340 | 10 (0)| 00:00:01 | 1 | 5 | | | |
| 9 | INDEX FULL SCAN | 주문_PK| 1000 | | 5 (0)| 00:00:01 | 1 | 5 | | | |
|* 10 | TABLE ACCESS FULL | 주문상 | 590 | 23600 | 4128 (1)| 00:00:01 | 1 | 5 | | | |
| 11 | TABLE ACCESS FULL | 고객 | 1000 | 11000 | 3 (0)| 00:00:01 | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."고객번호"="D"."고객번호")
5 - filter(TO_DATE('20240930')>=TO_DATE('20240901'))
7 - access("A"."주문번호"="B"."주문번호")
8 - filter(("A"."주문일시">='20240901' AND "A"."주문일시"<='20240930'))
10 - filter(("B"."주문일시">='20240901' AND "B"."주문일시"<='20240930'))
생각한 답안쿼리
insert into 주문통계정보
select /*+ px_join_filter(c) swap_join_inputs(c) use_hash(d) */d.성별,c.주문일자,c.상품번호,c.주문수량
from
(
select /*+ leading(a) use_hash(b) index(a 주문_pk) index(b 주문상세_pk) */
to_char(a.주문일시,'YYYYMMDD') 주문일자,b.고객번호,b.상품번호,count(*) as 주문수량
from 주문 a, 주문상세 b
where a.주문번호 = b.주문번호
and a.주문일시 >= '20240901' and a.주문일시 <= '20240930'
and b.주문일시 >= '20240901' and b.주문일시 <= '20240930'
group by a.주문일시,b.고객번호,b.상품번호
) c, 고객 d
where c.고객번호 = d.고객번호
실행계획
SQL_ID 9npubdaxxr5c2, child number 0
-------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4142 (100)| | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | 주문통 | | | | | | | | | |
|* 2 | HASH JOIN | | 590 | 33040 | 4142 (1)| 00:00:01 | | | 1281K| 1281K| 1341K (0)|
| 3 | VIEW | | 590 | 26550 | 4139 (1)| 00:00:01 | | | | | |
| 4 | SORT GROUP BY | | 590 | 38940 | 4139 (1)| 00:00:01 | | | 73728 | 73728 | |
|* 5 | FILTER | | | | | | | | | | |
| 6 | PARTITION RANGE ALL | | 590 | 38940 | 4138 (1)| 00:00:01 | 1 | 5 | | | |
|* 7 | HASH JOIN | | 590 | 38940 | 4138 (1)| 00:00:01 | | | 1152K| 1152K| 1655K (0)|
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| 주문 | 590 | 15340 | 10 (0)| 00:00:01 | 1 | 5 | | | |
| 9 | INDEX FULL SCAN | 주문_PK| 1000 | | 5 (0)| 00:00:01 | 1 | 5 | | | |
|* 10 | TABLE ACCESS FULL | 주문상 | 590 | 23600 | 4128 (1)| 00:00:01 | 1 | 5 | | | |
| 11 | TABLE ACCESS FULL | 고객 | 1000 | 11000 | 3 (0)| 00:00:01 | | | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
insert 부분제외하고 select만 수행했을때는 아래와 같이 나옵니다
SQL_ID atukpmcrm99bd, child number 0
-------------------------------------
Plan hash value: 2249493250
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4406 (100)| | | | | | |
| 1 | HASH GROUP BY | | 590 | 45430 | 4406 (1)| 00:00:01 | | | 865K| 865K| |
|* 2 | FILTER | | | | | | | | | | |
|* 3 | HASH JOIN | | 590 | 45430 | 4405 (1)| 00:00:01 | | | 1316K| 1316K| 1616K (0)|
| 4 | PART JOIN FILTER CREATE | :BF0000 | 590 | 15340 | 274 (0)| 00:00:01 | | | | | |
| 5 | PARTITION RANGE ALL | | 590 | 15340 | 274 (0)| 00:00:01 | 1 | 5 | | | |
|* 6 | TABLE ACCESS FULL | 주문 | 590 | 15340 | 274 (0)| 00:00:01 | 1 | 5 | | | |
|* 7 | HASH JOIN | | 590 | 30090 | 4131 (1)| 00:00:01 | | | 1376K| 1376K| 1658K (0)|
| 8 | TABLE ACCESS FULL | 고객 | 1000 | 11000 | 3 (0)| 00:00:01 | | | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 590 | 23600 | 4128 (1)| 00:00:01 |:BF0000|:BF0000| | | |
|* 10 | TABLE ACCESS FULL | 주문상세| 590 | 23600 | 4128 (1)| 00:00:01 |:BF0000|:BF0000| | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE('20240930')>=TO_DATE('20240901'))
3 - access("A"."주문번호"="B"."주문번호")
6 - filter(("A"."주문일시">='20240901' AND "A"."주문일시"<='20240930'))
7 - access("B"."고객번호"="D"."고객번호")
10 - filter(("B"."주문일시">='20240901' AND "B"."주문일시"<='20240930'))
이 문제는 복기할때는 parallel join filter(bloom filter)를 제어하는 힌트를 적느냐 마느냐가 핵심중 하나였다고 생각하는데요. 실제로 그 힌트를 넣어서 작성하고 실행계획을 확인해봐도 insert의 실행계획시에는 전혀다르게 풀리더라구요. select까지만 넣었을때는 정상적으로 작동하는것으로 보았습니다.
parallel join filter(bloom filter)를 제어하는 힌트는px_join_filter / no_px_join_filter 입니다.
51회 소감은 실기가 꽤 어렵게 나왔다. 그대신 점수는 좀 후하게 쳐준거 같다입니다.
그럼 준비를 잘해서 다음회차로...
그리고 객관식중 제가 잘 몰랐던 내용들로 나온 문제들 몇개의 개념을 정리하였습니다.
1) index 관련 용어 물어보는 문제가 나왔었습니다. 두개의 개념이 헷갈렸습니다.
index skew 한쪽으로 치우치는 현상
index sparse 밀도가 떨어지는 현상
2) 정규식 3문제 중 한문제
아래 쿼리의 값을 물어보는 문제였습니다. 해당 정규식은 문자열의 위치를 찾는 정규식입니다.
regexp_instr(문자열,표현식,시작위치) 형식으로 되어있습니다.
풀이하면 5번째시작위치부터 123이 나오는 위치를 찾는것입니다.
문자열은 123이 5번반복되는 문자(123123123123123)이고, 5번째인 12312 다음부터 123이 나오는 문자열이 답입니다.
*부분이 123이 처음 나오는 부분이고 위치는 7번째입니다. 답은 7입니다.
12312|
3*123123
select regexp_instr('123123123123','123',5) from dual;
3) coalesce 함수사용하는 문제
coalesce는 null이 아닌 첫번째 인자값을 반환합니다. 데이터는 아래와 같고 쿼리는 아래와 같을때 값을 구하는 문제였습니다.
50,3000,50 이 정답입니다.
c1 c2
50 60
null 60
null null
select coalesce(c1,c2*50,50) from t;
4) NVL2 문제
이문제도 NVL2의 2번째 3번째 값 두개가 헷갈렸었네요.
NVL(값,NULL일경우 지정값)
NVL2(값,not null일경우 지정값 ,null일경우 지정값 )
[출처] https://bae9086.tistory.com/563
