오라클 컬럼의 저장순서

오라클 컬럼의 저장 순서

  vpn_key Oracle DB의 로우(row)에서 실제 저장된 컬럼의 순서는 매우 중요한 개념이므로 정확히 이해 해야 합니다.

  playlist_add_check

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
Data Type:	NUMBER	VARCHAR2	NUMBER	DATE	CHAR	VARCHAR2
Values:	100	'ABC'	NULL	SYSDATE	'Y'	NULL
Storage:	YES	YES	NO	YES	YES	NO

• 컬럼 순서와 NULL 컬럼을 주의 깊게 보기 바랍니다.

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']

menu_book 핵심 포인트

* 테이블 정의 순서대로 저장하되 NULL 컬럼만 건너뜀!!

컬럼 접근 시 Oracle의 내부 동작 방식

• 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바이트 읽기

• 실제 테스트로 확인:

-- 컬럼 접근 패턴 확인
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번째 저장

컬럼 순서 변경의 영향

• 비효율적 구조:

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 체크 필요

• 효율적 구조: 자주쓰는 컬럼이 앞으로 not null 조건이 앞으로

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. 컬럼 접근 비용 측정

   -- 테스트 데이터 생성 (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;

2. 성능 비교

   SET AUTOTRACE ON STATISTICS

3. 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';

Column Length Array의 정확한 동작

1. Sparse(드물게있는) Column 처리:

   -- 극단적인 예시
   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]             ← 연속으로 저장

2. 컬럼 위치 매핑:

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

menu_book * [결론] 컬럼 저장 순서 규칙:

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

• 성능에 미치는 영향:

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

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