SQLP 튜닝 연습장

테마

튜닝 연습파티션
PART-018

주문 테이블을 연도별 칸으로 나누고 PK를 만들려니 ORA-14039가 난다

기초DDL이커머스393 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황 ​

이커머스 고객사는 개인정보 보관 기한(5년)이 지난 주문을 해마다 한꺼번에 지우려고 orders를 연도별 파티션 테이블 orders_p로 옮긴다. 칸째 들어내면 DELETE 없이 정리할 수 있다는 것이 이유다. 테이블은 만들어졌는데 PK를 만드는 문장에서 에러가 났다. 개발자는 에러 문구를 보고 order_date를 PK에 더한 문장을 다시 돌렸고, 이번에는 성공했다. 이관 검토를 맡은 강도현이 두 문장을 들고 왔다.

테이블과 인덱스 ​

  • orders 주문: 34,908행 · 193블록 · order_id NUMBER(PK, 시퀀스로 매기고 바뀌지 않는다) · customer_id · order_date DATE(시분초 있음, 2020 ~ 2024) · status · total_amount. 연도마다 약 7,000건이다.
  • orders_p(이관 대상, 문제 설정): orders를 PARTITION BY RANGE (order_date) 연도별 5칸(P2020 ~ P2024)과 빈 PMAX로 나눠 CTAS로 지었다. 칸마다 인덱스 높이는 2다.
  • order_items, payments, shipping은 order_id로 주문을 찾는다. 주문 상세 화면은 주문번호만 넘긴다.

원본 SQL ​

sql
CREATE TABLE orders_p
PARTITION BY RANGE (order_date)
( PARTITION P2020 VALUES LESS THAN (DATE '2021-01-01')
, PARTITION P2021 VALUES LESS THAN (DATE '2022-01-01')
, PARTITION P2022 VALUES LESS THAN (DATE '2023-01-01')
, PARTITION P2023 VALUES LESS THAN (DATE '2024-01-01')
, PARTITION P2024 VALUES LESS THAN (DATE '2025-01-01')
, PARTITION PMAX  VALUES LESS THAN (MAXVALUE) )
AS SELECT * FROM orders;

ALTER TABLE orders_p ADD CONSTRAINT pk_orders_p PRIMARY KEY (order_id)
  USING INDEX LOCAL;

실행계획 학습용 예시 · 행 수는 schemas 그대로 · 연도별 건수는 문제에 적은 가정 ​

xplan
[실행 결과]
  CREATE TABLE orders_p ...                                  Table created.
  ALTER TABLE orders_p ADD CONSTRAINT pk_orders_p PRIMARY KEY (order_id) USING INDEX LOCAL
  ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

[개발자가 다음에 돌린 문장 · 성공]
  ALTER TABLE orders_p ADD CONSTRAINT pk_orders_p PRIMARY KEY (order_id, order_date)
    USING INDEX LOCAL;
  Table altered.

[그 PK로 주문 상세 조회 · WHERE order_id = :id]
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |      1 |        |      1 | 00:00:00.01 |      11 |        |       |
|   1 |  PARTITION RANGE ALL                |             |      1 |      1 |      1 | 00:00:00.01 |      11 |      1 |     6 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | ORDERS_P    |      5 |      1 |      1 | 00:00:00.01 |      11 |      1 |     6 |
|*  3 |    INDEX RANGE SCAN                 | PK_ORDERS_P |      5 |      1 |      1 | 00:00:00.01 |      10 |      1 |     6 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORDER_ID"=:ID)

문제 ​

  1. ORA-14039가 나는 까닭을 LOCAL 유니크 인덱스가 중복을 검사하는 방식으로 설명하시오.
  2. 개발자의 두 번째 문장이 왜 답이 아닌지 쓰고, order_id의 유일성을 지키는 PK를 다시 작성하시오. 그 PK가 연도 칸을 지울 때 치르는 대가도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자