| https://bae9086.tistory.com/563 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 |
||||||||||
| SQL_ID g0znpsd9m3kkn, child number 2 ------------------------------------- Plan hash value: 2518725882 ---------------------------------------------------------------------------------------------------------------------------------------- | 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 |
||||||||||
| SQL_ID dgrkftr8z4p7y, child number 3 Plan hash value: 3073072593 ------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------- | 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 상품번호 |
||||||||||
| SQL_ID 7wvtm6m6bwttk, child number 0 ------------------------------------- Plan hash value: 4134734447 ------------------------------------------------------------------------------------------------------------------------------------------------------- | 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 | &nb '02. SQLP 스터디 > 03. 기출' 카테고리의 다른 글
|
02. SQLP 스터디/03. 기출