PART-032

같은 한 달 조회가 ORD_LIST에서는 1,980블록인데 ORD_LIST_HASH에서는 9,039블록이다

중급DDL버거407 / 700

상황

버거 본사 분석 DB에는 같은 주문 100만 행이 ORD_LIST와 ORD_LIST_HASH 두 모양으로 있다. 분석 화면은 대부분 ORD_LIST_HASH를 쓴다. 분석 DB 공간이 Free의 12GB 한도에 거의 차서, 이번에 둘 중 하나를 정리하고 남길 테이블의 모양을 정하기로 했다. ORD_LIST_HASH의 해시 8조각은 누가 왜 나눴는지 기록이 없다. 이관 스크립트에는 빈 테이블을 먼저 만들고 INSERT /*+ APPEND PARALLEL(ORL 4) */로 채운 흔적만 남아 있다.

테이블과 인덱스

ORD_LIST 주문(교재 14화): PARTITION BY LIST (ORD_YM) 25칸(P201201 ~ P201312과 빈 P_DEFAULT) · 세그먼트 24개 · 384MB. 칸 하나 FULL ≈ 1,980블록(데이터 약 971블록과 빈 블록 약 1,006블록)이다.
ORD_LIST_HASH 주문(교재 14화): PARTITION BY LIST (ORD_YM) 25칸 × SUBPARTITION BY HASH (ORD_NO) SUBPARTITIONS 8 · 세그먼트 192개 · 3,072MB. 서브파티션 하나는 데이터 약 121블록에 빈 블록 약 1,006블록이다.
두 테이블 모두 빈 테이블에 APPEND로 채웠다. 이 버전은 파티션과 서브파티션의 첫 익스텐트를 8MB(1,024블록)로 잡는다.
분석 DB에서 이 테이블을 쓰는 SQL은 셋이다. ① 「월 매장 실적」 ORD_YM 한 달 전체(하루 수십 번) ② 「매장 월 주문」 ORD_YM과 SHOP_NO(하루 수백 번) ③ 「영수증 조회」 ORD_NO와 ORD_YM(하루 수십 번)
주문상품 쪽 해시 복사본(ORD_ITEM_LIST_HASH)은 공간이 모자라 지웠다. 분석 DB는 밤에 한 번 적재한다.
LOCAL 인덱스 ORD_LIST_HASH_PK (ORD_NO, ORD_YM)가 있다. 통계는 최신이다.

원본 SQL

sql

-- ① 「월 매장 실적」 (:v_ym = '201206')
SELECT SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_LIST_HASH
 WHERE ORD_YM = :v_ym
 GROUP BY SHOP_NO;

실행계획 실측 · 교재 14화

xplan

[① · ORD_LIST_HASH]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |    200 | 00:00:00.12 |    9039 |        |       |
|   1 |  HASH GROUP BY          |               |      1 |    200 |    200 | 00:00:00.12 |    9039 |        |       |
|   2 |   PARTITION LIST SINGLE |               |      1 |  41040 |  41040 | 00:00:00.10 |    9039 |    KEY |   KEY |
|   3 |    PARTITION HASH ALL   |               |      1 |  41040 |  41040 | 00:00:00.10 |    9039 |      1 |     8 |
|   4 |     TABLE ACCESS FULL   | ORD_LIST_HASH |      8 |  41040 |  41040 | 00:00:00.09 |    9039 |    KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

[① · ORD_LIST (같은 조건)]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    200 | 00:00:00.05 |    1980 |        |       |
|   1 |  HASH GROUP BY          |          |      1 |    200 |    200 | 00:00:00.05 |    1980 |        |       |
|   2 |   PARTITION LIST SINGLE |          |      1 |  41040 |  41040 | 00:00:00.03 |    1980 |    KEY |   KEY |
|   3 |    TABLE ACCESS FULL    | ORD_LIST |      1 |  41040 |  41040 | 00:00:00.02 |    1980 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------

[③ 「영수증 조회」 · ORD_LIST_HASH · :ord_no = 777777, :ord_ym = '201307']
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  PARTITION LIST SINGLE               |                  |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   2 |   PARTITION HASH SINGLE              |                  |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID | ORD_LIST_HASH    |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  4 |     INDEX UNIQUE SCAN                | ORD_LIST_HASH_PK |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------

   4 - access("ORD_NO"=:ORD_NO AND "ORD_YM"=:ORD_YM)

[세그먼트 · USER_SEGMENTS]
  ORD_LIST          24개      384 MB
  ORD_LIST_HASH    192개    3,072 MB

문제

①이 ORD_LIST_HASH에서 4.6배 읽는 까닭을 실행계획의 두 PARTITION 줄과 세그먼트 수로 설명하시오.
해시 서브파티션이 ① ~ ③ 가운데 어느 SQL에 도움이 되는지 판단하고, 이 분석 DB에 맞게 남길 테이블을 다시 짓는 DDL을 쓰시오.
다시 지은 뒤 ①의 블록 수와 공간을 어림하고, 해시 서브파티션을 다시 고려해야 할 조건을 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 해시 서브파티션을 빼고 월 한 단계로 · CTAS 로 채운다 (첫 익스텐트를 비워 두지 않는다)
CREATE TABLE ORD_LIST_M
PARTITION BY LIST (ORD_YM)
( PARTITION P201201 VALUES ('201201'),
  PARTITION P201202 VALUES ('201202'),
  -- … 월마다 한 줄
  PARTITION P201312 VALUES ('201312'),
  PARTITION P_DEFAULT VALUES (DEFAULT) )
AS SELECT * FROM ORD_LIST_HASH;

ALTER TABLE ORD_LIST_M ADD CONSTRAINT ORD_LIST_M_PK
  PRIMARY KEY (ORD_NO, ORD_YM) USING INDEX LOCAL;

-- 건수와 칸마다 행 수를 확인한 뒤 바꿔 끼운다
DROP TABLE ORD_LIST_HASH PURGE;
DROP TABLE ORD_LIST PURGE;
ALTER TABLE ORD_LIST_M RENAME TO ORD_LIST;

개선 후 실행계획 실측 · 교재 14화

xplan

[① · 다시 지은 ORD_LIST (CTAS)]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |    200 | 00:00:00.03 |     972 |        |       |
|   1 |  HASH GROUP BY          |          |      1 |    200 |    200 | 00:00:00.03 |     972 |        |       |
|   2 |   PARTITION LIST SINGLE |          |      1 |  41040 |  41040 | 00:00:00.02 |     972 |    KEY |   KEY |
|   3 |    TABLE ACCESS FULL    | ORD_LIST |      1 |  41040 |  41040 | 00:00:00.02 |     972 |    KEY |   KEY |
----------------------------------------------------------------------------------------------------------------

[③ 「영수증 조회」]  PARTITION LIST SINGLE · INDEX UNIQUE SCAN ORD_LIST_M_PK · 3블록 그대로

[세그먼트]
  다시 지은 ORD_LIST    24개    약 192 MB (데이터 칸 하나가 첫 익스텐트 8MB 에 거의 들어맞는다)
  지운 두 테이블       216개    3,456 MB
  (참고 · 교재 14화 실측) 25 × 8 설계를 CTAS 로 다시 지으면 1,536 MB · 6월 982블록

모범 답안 서술

ORD_LIST_HASH의 ①은 2번 줄 PARTITION LIST SINGLE로 6월 칸 하나를 고른 다음, 3번 줄 PARTITION HASH ALL(1 ~ 8)로 그 칸의 해시 서브파티션 8개를 모두 연다(4번 줄 Starts 8). 서브파티션은 저마다 세그먼트다. 빈 테이블에 APPEND로 채우면 세그먼트마다 첫 익스텐트 8MB의 남은 블록(약 1,006개)을 비워 둔 채 고수위선 아래에 남기는데, 세그먼트가 8개이니 빈 블록도 8배다. 데이터는 약 121블록 × 8 = 약 970블록으로 ORD_LIST와 같지만, 빈 블록 약 8,050개를 함께 읽어 9,039블록이 되었다. ORD_LIST는 칸마다 세그먼트가 하나라 빈 블록도 한 벌(1,006)이고 1,980블록이다. 공간도 같은 이유로 세그먼트 192개에 3,072MB다. 해시 서브파티션은 해시 키 ORD_NO로 찾을 때만 조각 하나를 고른다. ①과 ②는 ORD_NO를 주지 않으므로 늘 8조각을 모두 연다. ③은 ORD_NO와 ORD_YM을 주므로 조각 하나를 고르지만, 월 한 단계 테이블에서도 LOCAL PK (ORD_NO, ORD_YM)로 칸 하나에서 3블록이다. 세 SQL 어디에도 해시 서브파티션이 주는 이득이 없다. 해시 서브파티션이 값을 하는 곳은 같은 키로 나눈 주문상품과 조각끼리 조인할 때(파티션 와이즈 조인)나 동시 입력이 한 세그먼트에 몰릴 때인데, 주문상품 해시 복사본은 지웠고 분석 DB는 밤에 한 번 적재한다. 그래서 해시 서브파티션을 빼고 월 한 단계(PARTITION BY LIST (ORD_YM))로 CTAS를 써서 다시 짓는다. CTAS는 첫 익스텐트부터 채우므로 빈 블록이 생기지 않는다. LOCAL PK를 다시 만들고, 건수를 확인한 뒤 두 테이블을 지우고 이름을 바꿔 끼운다. 다시 지은 뒤 ①은 칸 하나에서 9,039 → 약 972블록이다(ORD_LIST의 1,980보다도 적다). ③은 3블록 그대로다. 세그먼트는 192개 → 24개이고, 데이터 칸 하나가 첫 익스텐트 8MB에 거의 들어맞아 공간은 3,072MB → 약 192MB가 된다. 지운 ORD_LIST(384MB)까지 합치면 약 3.2GB를 돌려받는다.

해설

교재 14화 실측: 같은 25 × 8 설계를 CTAS로 다시 지으면 1,536MB, 6월 982블록이었고, INITIAL 64K까지 주면 379MB, 998블록이었다. 빈 블록만 없애도 읽기 손해는 세그먼트 머리 몇 블록으로 준다. 그래도 근거 없는 세그먼트 192개를 남길 까닭은 없다.
해시 서브파티션이 값을 하는 곳은 둘이다. 같은 해시 키로 나눈 테이블과 조각끼리 조인할 때(파티션 와이즈 조인, 대량 처리 문제에서 다룬다)와, 동시 입력이 한 세그먼트에 몰릴 때(PART-036)다. 둘 다 없으면 서브파티션은 세그먼트 수만 늘린다.
이관 스크립트의 PARALLEL(ORL 4)는 Free에서는 처음부터 직렬로 돈다(교재 14화 §9). 힌트가 있다고 그 일이 일어나는 것은 아니다.
②는 칸 하나(약 972블록)를 FULL로 읽어 매장 하나의 205건을 찾는다. 하루 수백 번이 부담이면 LOCAL 인덱스 (SHOP_NO, …)를 더한다(PART-039).
"파티션을 쓰고 있다"는 사실은 아무것도 보증하지 않는다. 설계서에는 칸과 서브파티션마다 무엇을 위해 나눴는지 적어 둔다(교재 14화 §8.5 처방).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 14화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 14화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황