꿈꾸는 개발자, DBA 커뮤니티 구루비

정보시스템의 설계 단계에 있어 데이터 모델링은 매우 중요한 부분을 차지한다. 업무의 특성을 고려해 보다 유연한 모델링을 개발해야 하다보니 많은 노력이 필요하다. 특히 성능 측면에서 고려해야 할 점도 많다.

모델링 시 고려해야 할 성능을 모두 다룬다는 것은 쉽지 않은 일이다. 그 양이 방대하기 때문이다. 그러므로 이 글에서는 필자가 고민했던 한 가지 사례를 중심으로 이야기하고자 한다.

일반적으로 모델링 시 NULL 허용 컬럼은 테이블의 맨 뒤에 두는 것이 성능상 유리하다고 말한다. 그런데 과연 정말 그러할까? 그렇다면 그 이유는 무엇일까?

[리스트 1] 처음과 끝에 NOT NULL 컬럼이 그 사이는 모두 NULL 허용 컬럼인 예

#### 테이블 생성 ####
SQL> CREATE TABLE TEST_MID_NULL
    (COL1 VARCHAR2(3) NOT NULL,
     COL2 VARCHAR2(4000) NULL,
     COL3 VARCHAR2(4000) NULL,
     COL4 VARCHAR2(4000) NULL,
     COL5 VARCHAR2(4000) NULL,
     COL6 VARCHAR2(4000) NULL,
     COL7 VARCHAR2(4000) NULL,
     COL8 VARCHAR2(4000) NULL,
     COL9 VARCHAR2(4000) NULL,
     COL10 VARCHAR2(4000) NOT NULL); -- 처음과 끝 컬럼은 NOT NULL,
중간은 NULL 허용 컬럼

테이블이 생성됐습니다.


#### 데이터 삽입 #### : 정확한 테스트를 위해 데이터 길이는 모두 일정하게 함
SQL> insert into TEST_MID_NULL
     select lpad(level,3,'0'),NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'A'
     from dual
     connect by level < 1000;

999개의 행이 만들어졌습니다.


SQL> commit;
커밋이 완료됐습니다.


#### 한 블록에 얼마큼의 데이터가 저장되는지 확인 ####
SQL> SELECT count(*),
            DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_NO,
            DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_NO,
            DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO
      FROM TEST_MID_NULL
     GROUP BY DBMS_ROWID.ROWID_OBJECT(ROWID),
              DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

  COUNT(*)  OBJECT_NO    FILE_NO   BLOCK_NO
---------- ---------- ---------- ----------
       382      79414          1      87794     --한 블록에 최대 382건 저장
       382      79414          1      87793
       235      79414          1      87795

[리스트 2] 처음 두 컬럼은 NOT NULL 컬럼, 뒤에 존재하는 컬럼은 모두 NULL 허용 컬럼인 예

#### 테이블 생성 ####
SQL> CREATE TABLE TEST_END_NULL
    (COL1 VARCHAR2(3) NOT NULL,
     COL2 VARCHAR2(4000) NOT NULL, -- 처음 두 컬럼만 NOT NULL
     COL3 VARCHAR2(4000) NULL,
     COL4 VARCHAR2(4000) NULL,
     COL5 VARCHAR2(4000) NULL,
     COL6 VARCHAR2(4000) NULL,
     COL7 VARCHAR2(4000) NULL,
     COL8 VARCHAR2(4000) NULL,
     COL9 VARCHAR2(4000) NULL,
     COL10 VARCHAR2(4000) NULL);
테이블이 생성됐습니다.


#### 데이터 삽입 #### : 정확한 테스트를 위해 데이터의 길이는 모두 일정하게 함
SQL> insert into TEST_END_NULL
     select lpad(level,3,'0'),'A',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
     from dual
     connect by level < 1000;

999개의 행이 만들어졌습니다.


SQL> commit;

커밋이 완료됐습니다.


#### 한 블록에 얼마큼의 데이터를 저장하는지 확인 ####
SQL> SELECT count(*),
            DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_NO,
            DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_NO,
            DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NO
      FROM TEST_END_NULL
     GROUP BY DBMS_ROWID.ROWID_OBJECT(ROWID),
              DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),
              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID);

  COUNT(*)  OBJECT_NO    FILE_NO   BLOCK_NO
---------- ---------- ---------- ----------
       660      79415          1      87801 -- 한 블록에 최대 660건 저장
       339      79415          1      87802

<리스트 1>과 <리스트 2>는 ROW 길이가 동일하지만 블록 1개에 저장 가능한 최대 개수는 크게 다르다. 왜 이런 결과가 나온 것일까? 블록 덤프를 통해 이 둘을 비교해 보자.

[리스트 3] 테이블 중간에 NULL 허용 컬럼이 존재하는 예

block_row_dump:
tab 0, row 0, @0x1f8f
tl: 17 fb: --H-FL-- lb: 0x1  cc: 10
col  0: [ 3]  30 30 31
col  1: *NULL*
col  2: *NULL*
col  3: *NULL*
col  4: *NULL*
col  5: *NULL*
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: [ 1]  41
이하 생략

[리스트 4] 테이블 끝에 NULL 허용 컬럼이 존재

block_row_dump: 
tab 0, row 0, @0x1f97 
tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 
col 0: [ 3] 30 30 31 
col 1: [ 1] 41 
이하 생략..

<리스트 3>의 경우 NULL이라는 값도 블록에 포함돼 있다. 그러므로 <리스트 4>처럼 테이블을 만들어야 성능, 즉 저장공간 측면에서 유리하다.

그 이유는 오라클 9i 레퍼런스의 일부 내용을 발취한 다음 글에서 찾을 수 있다.

“데이터가 존재하는 컬럼 사이의 NULL은 데이터베이스에 저장된다. 이때 NULL은 컬럼당 1바이트를 차지한다. 그런데 테이블 맨 뒤의 NULL은 저장되지 않는다. 예컨대 테이블 맨 뒤의 3개 컬럼이 NULL인 경우 NULL을 저장하지 않는다. 그러므로 컬럼이 많은 테이블인 경우 NULL을 포함할 가능성이 높은 컬럼을 테이블 맨 뒤에 위치시키는 게 저장공간 절감에 유리하다.”

테스트 결과를 통해 알 수 있듯 NULL 허용 컬럼이 테이블 뒤로 배치될 경우 저장공간 효율이 증가하게 된다. 이는 곧 I/O 량이 줄는 결과를 가져오므로 자연스레 SQL 수행 속도가 빨라지게 된다.

물론 운영을 하다보면 테이블 모델이 계속 변경되기에 이러한 장점을 활용하지 못할 수 있다고 생각할 수 있다. 그러나 모델링 단계에서 이런 장점을 고려해 설계한다면 분명 전체적인 공간 효율성과 성능 향상 이 두 마리 토끼를 잡을 수 있을 것이다.