IOT 구조와 성능: Primary Key Leaf·Overflow·Secondary Index

Heap Table + Primary Key Index

Primary Key Index Leaf
  → Key + Physical ROWID

Physical ROWID
  → Heap Table Block
  → Row Data

Index-Organized Table

Primary Key B-tree Leaf
  → Primary Key
  → Non-Key Row Data
  → 필요 시 Overflow 위치 정보

IOT
  = Primary Key B-tree
  + Table Row Data

Primary Key Index Leaf
(account_id=100, ROWID=AA...)

        ↓

Heap Table Block
(account_id=100,
 balance=50000,
 updated_at=...,
 memo=...)

INDEX UNIQUE SCAN ACCOUNT_BALANCE_PK
  → Physical ROWID

TABLE ACCESS BY INDEX ROWID ACCOUNT_BALANCE
  → Row Data

IOT Primary Key Leaf
(account_id=100,
 balance=50000,
 updated_at=...,
 memo=...)

Primary Key 탐색
  → Leaf Entry 발견
  → Leaf에서 Row Data 반환

SELECT table_name,
       iot_type,
       iot_name,
       num_rows,
       blocks,
       avg_row_len,
       last_analyzed
FROM   user_tables
WHERE  table_name LIKE 'ACCOUNT%';

IOT_TYPE = IOT
  → IOT Top Object

IOT_TYPE = IOT_OVERFLOW
  → Overflow Segment에 대응하는 내부 Table Object

IOT_TYPE = IOT_MAPPING
  → Bitmap Secondary Index용 Mapping Table

SELECT index_name,
       index_type,
       uniqueness,
       blevel,
       leaf_blocks,
       clustering_factor,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ACCOUNT_BALANCE';

INDEX_TYPE = IOT - TOP

Primary Key
  = Row의 논리적 식별자
  + B-tree 정렬 Key
  + Leaf에서 Row를 찾는 Access Key
  + Secondary Index Logical ROWID의 기반

CREATE TABLE account_balance (
    account_id NUMBER        NOT NULL,
    balance    NUMBER        NOT NULL,
    updated_at TIMESTAMP     NOT NULL,
    memo       VARCHAR2(500),
    CONSTRAINT account_balance_pk
        PRIMARY KEY (account_id)
)
ORGANIZATION INDEX;

Heap Table
  Table Row Segment 존재
  PK Index는 선택적 별도 Segment

IOT
  Table Row가 PK B-tree Leaf에 존재
  PK 구조가 Table의 핵심 저장 구조

SELECT balance,
       updated_at
FROM   account_balance
WHERE  account_id = :account_id;

INDEX UNIQUE SCAN
→ TABLE ACCESS BY INDEX ROWID

INDEX UNIQUE SCAN SYS_IOT_TOP_...
→ Leaf에서 balance·updated_at 반환

Heap
  PK Index Block
  + Heap Table Block

IOT
  IOT Primary Key Leaf Block

CREATE TABLE account_txn (
    account_id NUMBER       NOT NULL,
    txn_seq    NUMBER       NOT NULL,
    txn_time   TIMESTAMP    NOT NULL,
    amount     NUMBER       NOT NULL,
    txn_type   VARCHAR2(10) NOT NULL,
    memo       VARCHAR2(500),
    CONSTRAINT account_txn_pk
        PRIMARY KEY (account_id, txn_seq)
)
ORGANIZATION INDEX;

(account_id=100, txn_seq=1)
(account_id=100, txn_seq=2)
(account_id=100, txn_seq=3)
(account_id=101, txn_seq=1)
...

SELECT txn_seq,
       txn_time,
       amount,
       txn_type
FROM   account_txn
WHERE  account_id = :account_id
AND    txn_seq >= :from_seq
AND    txn_seq <  :to_seq;

WHERE txn_seq = :txn_seq

IOT
  ≠ Primary Key 어느 Column이든 동일 효율

Leading Prefix
  → 여전히 중요

TABLE ACCESS FULL

INDEX FULL SCAN SYS_IOT_TOP_...
INDEX FAST FULL SCAN SYS_IOT_TOP_...

IOT Top Fast Full Scan
  + Overflow Access

넓은 IOT Row
  → Leaf Block당 Row 수 감소
  → Leaf Blocks 증가
  → BLEVEL 증가 가능
  → Range Scan Block 증가
  → Buffer Cache 점유 증가
  → Split·Redo·Undo 증가

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key,
       pct_direct_access,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ACCOUNT_TXN';

IOT Top Index Entry
  Primary Key Columns
  + Index 영역에 남은 Non-Key Columns
  + Overflow Row Piece의 Physical 위치 정보

Overflow Segment
  나머지 Non-Key Columns

Head Piece
  → Primary Key와 Threshold 안에 들어오는 Non-Key Column
  → IOT Index Leaf에 저장

Tail Piece
  → 나머지 Non-Key Column
  → Overflow Segment에 저장

PCTTHRESHOLD 20
  → Row Head Piece가 Index Block Size의 20% 이내에 들어가도록 시도

CREATE TABLE document_index (
    token           VARCHAR2(50)   NOT NULL,
    document_id     NUMBER         NOT NULL,
    token_frequency NUMBER,
    token_offsets   VARCHAR2(2000),
    CONSTRAINT document_index_pk
        PRIMARY KEY (token, document_id)
)
ORGANIZATION INDEX
PCTTHRESHOLD 20
OVERFLOW;

CREATE TABLE document_index2 (
    token           VARCHAR2(50)   NOT NULL,
    document_id     NUMBER         NOT NULL,
    token_frequency NUMBER,
    token_offsets   VARCHAR2(2000),
    CONSTRAINT document_index2_pk
        PRIMARY KEY (token, document_id)
)
ORGANIZATION INDEX
PCTTHRESHOLD 20
INCLUDING token_frequency
OVERFLOW;

Top Leaf
  token
  document_id
  token_frequency
  Overflow Pointer

Overflow
  token_offsets

INCLUDING
  → 해당 Column까지 Top에 유지하려는 논리 경계

PCTTHRESHOLD
  → 실제 Block Size 기반 최대 Row Head 크기

INCLUDING Column까지 저장하면 Threshold 초과
  → Threshold 기준으로 더 앞 Column에서 Overflow 가능

CREATE TABLE iot_example (
    a NUMBER,
    b NUMBER,
    c NUMBER,
    d NUMBER,
    CONSTRAINT iot_example_pk PRIMARY KEY (c, b)
)
ORGANIZATION INDEX;

c, b, a, d

Overflow
  = 큰 Column 비용 제거
  X

Overflow
  = 큰 Column 비용을 필요한 Query에만 지연
  O

Heap Table Secondary Index
  Secondary Key
  + Physical ROWID

IOT Secondary Index
  Secondary Key
  + Primary Key 기반 Logical ROWID
  + 선택적 Physical Guess

Logical ROWID
  → Primary Key Value
  → Control Information
  → Optional Physical Guess

긴 Primary Key
  → Secondary Index Entry 증가
  → Leaf Blocks·Cache·DML 비용 증가 가능

Secondary Index Scan
  → Physical Guess Block 직접 확인

Secondary Index Scan
  → Guess가 가리키는 IOT Leaf Block
  → Row 확인

Secondary Index Scan
  → 오래된 Guess Block 확인
  → Row 없음
  → Logical ROWID의 Primary Key로 IOT 재탐색

SELECT index_name,
       index_type,
       blevel,
       leaf_blocks,
       pct_direct_access,
       last_analyzed
FROM   user_indexes
WHERE  table_name = 'ACCOUNT_TXN'
ORDER BY index_name;

PCT_DIRECT_ACCESS = 100
  → 수집 시점에 Guess가 모두 유효한 방향

낮은 PCT_DIRECT_ACCESS
  → 많은 Entry에서 PK 재탐색 가능성

PCT_DIRECT_ACCESS 낮음
  ≠ 즉시 Rebuild

Runtime 비용 증가 확인
  + 업무 중요도
  + Rebuild 비용
  → 판단

CREATE TABLE sales_iot (
    sale_id       NUMBER,
    status_code   VARCHAR2(10),
    customer_id   NUMBER,
    amount        NUMBER,
    CONSTRAINT sales_iot_pk PRIMARY KEY (sale_id)
)
ORGANIZATION INDEX
MAPPING TABLE;

Bitmap Index
  → Bit Position을 Physical ROWID로 변환

Physical ROWID
  → Heap-Organized Mapping Table Row

Mapping Table Row
  → IOT Logical ROWID

Logical ROWID
  → IOT Row Access

Bitmap Index on IOT
  = Bitmap Index
  + Mapping Table
  + Logical ROWID Access

긴 PK
  → IOT Leaf Entry 증가
  → Secondary Index Entry 증가
  → Cache·Scan·DML 비용 증가

PK 변경
  → 기존 Key 위치에서 삭제
  → 새 Key 위치에 삽입
  → Secondary Index Logical ROWID 유지 정보 변경
  → Split·Redo·Undo 가능

순차 증가 PK
  → 오른쪽 Leaf 집중
  → Hot Block·Split 가능

무작위 PK
  → 여러 Leaf에 분산 Insert
  → 기존 Block Split·Cache 변화 가능

계좌별 현재 잔액
계좌별 순번 거래
문서 Token·Document Key
Configuration·Lookup
Primary Key 중심 Key-Value
다대다 관계의 정렬된 교차 Table

IOT 선택
  = PK 조회가 있음
  X

IOT 선택
  = PK 중심 Access 이득
  > Leaf·Overflow·Secondary·DML 총비용

SELECT /*+ GATHER_PLAN_STATISTICS */
       balance,
       updated_at
FROM   account_balance
WHERE  account_id = :account_id;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

Index Leaf A-Rows
Index Buffers·Reads
Sort 제거 여부
Top-N Stopkey
Overflow Access 여부

CREATE INDEX account_txn_time_ix
ON account_txn(txn_time);

Top Leaf만 필요한 Query
vs
Overflow Column까지 필요한 Query

1. Query 비중을 PK·Prefix·Secondary·Full Scan으로 분류한다.
2. Primary Key 길이·변경 빈도·Insert 분포를 확인한다.
3. Row 폭과 자주 읽는 Column을 구분한다.
4. Top Leaf와 Overflow 경계를 PCTTHRESHOLD·INCLUDING으로 설계한다.
5. Secondary Index 수와 Logical ROWID Segment 비용을 계산한다.
6. Bitmap Index가 필요하면 Mapping Table 비용을 포함한다.
7. Heap PK Index→Table Access와 IOT Top Access를 실측한다.
8. Overflow·Secondary Query의 Buffers·Reads·PCT_DIRECT_ACCESS를 측정한다.
9. DML·Redo·Undo·Split·Space·Backup을 비교한다.
10. Data 증가 후 동일 Workload로 재검증하고 Rollback 계획을 준비한다.