SQL REMIND 실습 준비

1. 삭제 대상 조회 및 DROP 문 생성

다음 SQL은 조회만 수행합니다. drop_sql 컬럼에 스키마를 포함한 DROP 문을 생성합니다.

-- Name-based filter: exclude names ending in _pk or _fk + two digits.
-- This does not classify indexes by constraint ownership.
SELECT schemaname AS schema_name
       ,tablename AS table_name
       ,indexname AS index_name
       ,indexdef AS index_definition
       ,format('DROP INDEX CONCURRENTLY %I.%I;', schemaname, indexname) AS drop_sql
FROM pg_catalog.pg_indexes
WHERE schemaname = 'startdbpg'
  AND tablename IN ('tr_ord_big', 'ms_mbr_big', 'ms_shop_big')
  AND indexname !~* '(_pk|_fk[0-9]{2})$'
ORDER BY tablename, indexname;

SQL

복사

2. 변경 전 실제 조회 결과

총 3건입니다. 회원·매장 테이블에는 조건에 해당하는 인덱스가 없습니다.

테이블	인덱스명	기존 정의	DROP SQL
tr_ord_big	tr_ord_big_x31	CREATE INDEX tr_ord_big_x31 ON startdbpg.tr_ord_big USING btree (mbr_id)	DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x31;
tr_ord_big	tr_ord_big_x32	CREATE INDEX tr_ord_big_x32 ON startdbpg.tr_ord_big USING btree (ord_dtm, mbr_id)	DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x32;
tr_ord_big	tr_ord_big_x33	CREATE INDEX tr_ord_big_x33 ON startdbpg.tr_ord_big USING btree (shop_id, ord_dtm, mbr_id)	DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x33;

3. 기존 보조 인덱스 삭제

위 결과의 세 인덱스를 다음 SQL로 삭제하여 GOOD SQL 검증용 기본 인덱스 구성을 정리합니다. 각 테이블의 기존 PK 인덱스는 유지합니다.

DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x31;
DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x32;
DROP INDEX CONCURRENTLY startdbpg.tr_ord_big_x33;

SQL

복사

4. 40번대 기본 인덱스 생성

기존 보조 인덱스를 삭제한 다음 아래 주문 테이블의 기본 B-tree 인덱스 7개와 회원 테이블의 인덱스 1개를 생성합니다. 번호는 테이블마다 40부터 시작하며, 컬럼은 모두 기본 ASC 순서입니다. INCLUDE나 부분 조건은 추가하지 않습니다. 매장 테이블은 기존 PK만 유지합니다.

CREATE INDEX CONCURRENTLY tr_ord_big_x40 ON startdbpg.tr_ord_big (ord_dtm);
CREATE INDEX CONCURRENTLY tr_ord_big_x41 ON startdbpg.tr_ord_big (mbr_id, ord_dtm);
CREATE INDEX CONCURRENTLY tr_ord_big_x42 ON startdbpg.tr_ord_big (shop_id, ord_dtm);
CREATE INDEX CONCURRENTLY tr_ord_big_x43 ON startdbpg.tr_ord_big (mbr_id, ord_no);
CREATE INDEX CONCURRENTLY tr_ord_big_x44 ON startdbpg.tr_ord_big (pkup_dtm);
CREATE INDEX CONCURRENTLY tr_ord_big_x45 ON startdbpg.tr_ord_big (ord_ymd);
CREATE INDEX CONCURRENTLY tr_ord_big_x46 ON startdbpg.tr_ord_big (ord_st, pkup_dtm);

CREATE INDEX CONCURRENTLY ms_mbr_big_x40 ON startdbpg.ms_mbr_big (nick_nm);

SQL

복사

5. MAX + 1 방식의 채번 실습 테이블 생성

-- BAD: MAX + 1 방식의 채번
CREATE TABLE startdbpg.tr_ord_refund_max (
    refund_req_no BIGINT NOT NULL
    ,ord_no        INTEGER NOT NULL
    ,refund_amt    NUMERIC(18,3) NOT NULL
    ,refund_reason VARCHAR(200) NOT NULL
    ,req_st        VARCHAR(20) NOT NULL
    ,req_dtm       TIMESTAMP NOT NULL
);

ALTER TABLE startdbpg.tr_ord_refund_max ADD CONSTRAINT tr_ord_refund_max_pk PRIMARY KEY(refund_req_no);

-- GOOD: 시퀀스를 이용한 자동 채번
CREATE TABLE startdbpg.tr_ord_refund_seq (
    refund_req_no BIGSERIAL NOT NULL
    ,ord_no        INTEGER NOT NULL
    ,refund_amt    NUMERIC(18,3) NOT NULL
    ,refund_reason VARCHAR(200) NOT NULL
    ,req_st        VARCHAR(20) NOT NULL
    ,req_dtm       TIMESTAMP NOT NULL
);

ALTER TABLE startdbpg.tr_ord_refund_seq ADD CONSTRAINT tr_ord_refund_seq_pk PRIMARY KEY(refund_req_no);

SQL

복사

6. 복합키 조인 실습 테이블

테이블 ERD

flowchart LR
    P["tr_mbr_fee_pay<br/>────────────<br/>mbr_id (PK)<br/>pay_ym (PK)<br/>tot_amt<br/>unpaid_amt"]
    D["tr_mbr_fee_pay_det<br/>────────────<br/>mbr_id (PK, FK)<br/>pay_ym (PK, FK)<br/>pay_seq (PK)<br/>pay_amt<br/>pay_dt"]
    P -->|"1:N · FK(mbr_id, pay_ym)"| D

Mermaid

복사

테이블 생성 스크립트 보기