오라클 컬럼의 저장순서

오라클 컬럼의 저장 순서

{{틀:개요 |내용= Oracle row에서 실제 저장된 컬럼의 순서는 매우 중요한 개념이므로 정확히 이해 해야 합니다.

Oracle Row 컬럼 저장 순서 규칙

기본 원칙: 테이블 정의 순서 유지

• 예시 테이블 생성

-- 테이블 정의 순서
CREATE TABLE row_order_test (
    col1 NUMBER,        -- 1번째 컬럼
    col2 VARCHAR2(10),  -- 2번째 컬럼  
    col3 NUMBER,        -- 3번째 컬럼
    col4 DATE,          -- 4번째 컬럼
    col5 CHAR(1),       -- 5번째 컬럼
    col6 VARCHAR2(20)   -- 6번째 컬럼
);

-- 데이터 삽입 (일부 NULL)
INSERT INTO row_order_test VALUES (100, 'ABC', NULL, SYSDATE, 'Y', NULL);

실제 저장 구조 분석

테이블 컬럼 정의 순서

Column Position:	1	2	3	4	5	6
Column Name:	col1	col2	col3	col4	col5	col6
Values:	100	'ABC'	NULL	SYSDATE	'Y'	NULL
Storage:	YES	YES	NO	YES	YES	NO

1. 1. 1. 1. **B. Row Format에서의 저장:**

``` Row Header: [0x2C] [0x04] [0x00]

           ↑      ↑      ↑
          Flag   Col#=4  Reserved

Column Length Array: [0x03] [0x03] [0x07] [0x01]

                    ↑      ↑      ↑      ↑
                  col1(3) col2(3) col4(7) col5(1)
                  

Actual Data: [100] ['ABC'] [SYSDATE_BYTES] ['Y'] ```

• • 핵심 포인트**: **테이블 정의 순서대로 저장되되, NULL 컬럼만 건너뜀!**

1. 1. 1. 3. **컬럼 접근 시 Oracle의 내부 동작**

1. 1. 1. 1. **A. col4(DATE) 접근 시:**

``` 1. 테이블 메타데이터에서 col4는 4번째 컬럼임을 확인 2. Row Header에서 저장된 컬럼 수 확인 (4개) 3. Column Length Array를 순서대로 스캔:

  - 1번째 위치 (col1) → Length = 3
  - 2번째 위치 (col2) → Length = 3  
  - 3번째 위치는 NULL이므로 건너뜀
  - 4번째 위치 (col4) → Length = 7 ← 여기!

4. Offset 계산: 3 + 3 = 6바이트 지점에서 7바이트 읽기 ```

1. 1. 1. 1. **B. 실제 테스트로 확인:**

```sql -- 컬럼 접근 패턴 확인 SELECT

   DUMP(col1) as col1_dump,
   DUMP(col2) as col2_dump, 
   DUMP(col4) as col4_dump,
   DUMP(col5) as col5_dump

FROM row_order_test;

-- 결과: -- col1_dump: Typ=2 Len=3: 194,2,1 ← 1번째 저장 -- col2_dump: Typ=1 Len=3: 65,66,67 ← 2번째 저장 -- col4_dump: Typ=12 Len=7: 120,1,1,1,1,1,1 ← 3번째 저장 (col3 건너뜀) -- col5_dump: Typ=96 Len=1: 89 ← 4번째 저장 ```

1. 1. 1. 4. **컬럼 순서 변경의 영향**

1. 1. 1. 1. **A. 비효율적 구조:**

```sql CREATE TABLE inefficient_order (

   frequently_null VARCHAR2(100),    -- 1번째, 자주 NULL
   rarely_null_1 NUMBER NOT NULL,   -- 2번째, 거의 NOT NULL
   frequently_null2 VARCHAR2(200),  -- 3번째, 자주 NULL  
   rarely_null_2 CHAR(1) NOT NULL   -- 4번째, 거의 NOT NULL

);

-- 데이터: (NULL, 123, NULL, 'A') -- 저장 순서: rarely_null_1(2번째), rarely_null_2(4번째) -- 문제: 2번째 컬럼 접근 시 1번째 NULL 체크 필요 ```

1. 1. 1. 1. **B. 효율적 구조:**

```sql CREATE TABLE efficient_order (

   rarely_null_1 NUMBER NOT NULL,   -- 1번째, 거의 NOT NULL
   rarely_null_2 CHAR(1) NOT NULL,  -- 2번째, 거의 NOT NULL
   frequently_null VARCHAR2(100),   -- 3번째, 자주 NULL
   frequently_null2 VARCHAR2(200)   -- 4번째, 자주 NULL

);

-- 데이터: (123, 'A', NULL, NULL) -- 저장 순서: rarely_null_1(1번째), rarely_null_2(2번째) -- 장점: 앞쪽 컬럼들은 NULL 체크 없이 바로 접근 ```

1. 1. 1. 5. **실제 측정으로 확인하기**

1. 1. 1. 1. **A. 컬럼 접근 비용 측정:**

```sql -- 테스트 데이터 생성 (NULL 패턴 다르게) CREATE TABLE access_test_1 AS SELECT

   LEVEL as id,                                    -- 1번째, NOT NULL
   CASE WHEN MOD(LEVEL,2)=0 THEN NULL ELSE 'DATA' END as col2, -- 2번째, 50% NULL
   'FIXED' as important_col,                        -- 3번째, NOT NULL
   CASE WHEN MOD(LEVEL,3)=0 THEN NULL ELSE LEVEL END as col4   -- 4번째, 33% NULL

FROM dual CONNECT BY LEVEL <= 100000;

CREATE TABLE access_test_2 AS SELECT

   LEVEL as id,                                    -- 1번째, NOT NULL
   'FIXED' as important_col,                       -- 2번째, NOT NULL (앞으로 이동)
   CASE WHEN MOD(LEVEL,2)=0 THEN NULL ELSE 'DATA' END as col3, -- 3번째, 50% NULL
   CASE WHEN MOD(LEVEL,3)=0 THEN NULL ELSE LEVEL END as col4   -- 4번째, 33% NULL

FROM dual CONNECT BY LEVEL <= 100000;

-- 성능 비교 SET AUTOTRACE ON STATISTICS

-- important_col 접근 (3번째 vs 2번째 위치) SELECT COUNT(*) FROM access_test_1 WHERE important_col = 'FIXED'; SELECT COUNT(*) FROM access_test_2 WHERE important_col = 'FIXED'; ```

1. 1. 1. 6. **Column Length Array의 정확한 동작**

1. 1. 1. 1. **A. Sparse Column 처리:**

```sql -- 극단적인 예시 CREATE TABLE sparse_test (

   col1 NUMBER,        -- 1번째
   col2 NUMBER,        -- 2번째  
   col3 NUMBER,        -- 3번째
   col4 NUMBER,        -- 4번째
   col5 NUMBER         -- 5번째

);

-- 1번째, 5번째만 값 있음 INSERT INTO sparse_test VALUES (100, NULL, NULL, NULL, 500);

-- Row 구조: -- Header: [0x2C] [0x02] [0x00] ← 2개 컬럼만 저장 -- Length: [0x03] [0x03] ← col1(3), col5(3) -- Data: [100] [500] ← 연속으로 저장 ```

1. 1. 1. 1. **B. 컬럼 위치 매핑:**

```sql -- Oracle은 내부적으로 position mapping 사용 -- 물리적 저장: [col1] [col5] -- 논리적 접근: col5 요청 시 -- 1. col5는 5번째 컬럼 -- 2. 앞의 col2,col3,col4는 NULL이므로 건너뜀 -- 3. Length Array에서 2번째 항목이 col5의 길이 -- 4. 첫 번째 데이터 뒤에서 col5 데이터 찾음 ```

1. 1. **💡 핵심 정리:**

1. 1. 1. **컬럼 저장 순서 규칙:**

1. **테이블 정의 순서 그대로 유지** 2. **NULL 컬럼은 완전히 제외** (Length Array에서도 생략) 3. **NON-NULL 컬럼만 연속으로 저장** 4. **컬럼 접근 시 정의 순서 기준으로 오프셋 계산**

1. 1. 1. **성능에 미치는 영향:**

- **앞쪽 컬럼**: NULL 체크 없이 빠른 접근 - **뒤쪽 컬럼**: 앞의 NULL 컬럼들 건너뛰며 접근 - **자주 사용되는 컬럼**: 앞쪽 배치가 유리 - **NULL이 많은 컬럼**: 뒤쪽 배치가 유리

따라서 **테이블 설계 시 컬럼 순서**가 **물리적 저장 구조와 접근 성능**에 직접적인 영향을 준다는 것이 핵심입니다!