오라클 컬럼의 저장순서

오라클 컬럼의 저장 순서

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

playlist_add_check

테이블 정의 순서(column id)대로 저장 하되 NULL 컬럼은 기록하지 않음.

1. 데이터 블럭을 읽을경우 NULL인 컬럼은 블럭에 저장되어 있지 않기 때문에 테이블의 메타데이터에서 null 컬럼여부를 확인해야 하는 과정(블럭을 읽을때 읽는 컬럼이 널인지 확인))이 필요함(성능저하의 원인)
2. not null 컬럼인 경우 기록된 블럭의 헤더 정보로 바로 읽어야할 블럭의 길이를 알수 있음.( 성능 우수함. 블럭을 읽을때 해당 컬럼의 길이 많큼만 읽으면 끝.)

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 데이터 찾음

task_alt

결론

* [결론]

1. 컬럼 저장 순서 규칙:
   1. 테이블 정의 순서 그대로 유지
   2. NULL 컬럼은 완전히 제외(Length Array에서도 생략)
   3. NON-NULL 컬럼만 연속으로 저장
   4. 컬럼 접근 시 정의 순서 기준으로 오프셋 계산
2. 성능에 미치는 영향:
   1. 앞쪽 컬럼: NULL 체크 없이 빠른 접근
   2. 뒤쪽 컬럼: 앞의 NULL 컬럼들 건너뛰며 접근
   3. 자주 사용되는 컬럼: 앞쪽 배치가 유리
   4. NULL이 많은 컬럼: 뒤쪽 배치가 유리

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

arrow_back이전 글이전글: 오라클 컬럼의 구조

arrow_forward다음 글다음글: 컬럼의 순서가 변경되면 ROW의 물리적 구조도 바뀌는건가?