TABLE/INDEX SIZE 계산 공식

1. TABLE SIZE 계산 공식(ORACLE BLOCK SIZE : 2K 로 가정)
-------------------------------------------------------
    $ sqlplus scott/tiger
      SQL> SELECT  GREATEST(4, ceil(ROW_COUNT /
                  ((round(((1958 - (initrans * 23)) *
                  ((100 - PCT_FREE) /100)) / ADJ_ROW_SIZE)))) * 2)
                  TableSize_Kbytes
        FROM dual;

    *. 한개의 BLOCK에 Available 한 Bytes - 1958
    *. 각 initrans 는 23 Bytes
    *. PCT_FREE : Table 의 pctfree 값(default 10)
    *. ADJ_ROW_SIZE : 각 row 의 평균 SIZE 추정치 
    *. ROW_COUNT : table 의 row 의 갯수 

2. INDEX SIZE 계산공식 
----------------------
    SQL> SELECT  GREATEST(4, (1.01) * ((ROW_COUNT /
                  ((floor(((2048 - 113 - (initrans * 23)) * 
                  (1 - (percent_free/100))) /
                  ((10 + uniqueness) + number_col_index + 
                  (total_col_length)))))) *2))
                  IndexSize_Kbytes
        FROM dual;

    *. 한개의 BLOCK에 Available 한 Bytes ( 1935 or 2048 - 113 )
    *. 각 initrans 는 23 Bytes
    *. ROW_COUNT : table 의 row 의 갯수
    *. PCT_FREE : Index 의 pctfree 값(default 10)
    *. number_col_index : Index 에서 column 의 수
    *. total_col_length : Index 의 길이 추정치
    *. uniqueness : 만일 unique index 면 1, non-unique index 면 0. 

TABLE SIZE 계산 추정 방법

TABLE(NON-CLUSTERD TABLE)의 초기 사용량을 추정하는 방법을 살펴보자.
여기서는 ORACLE7 SERVER를 기준으로 하여 설명 하기 하며,
TABLE의 초기 사용량을 계산하는 단계를 다음과 같이 나눌 수 있다.

STEP 1 - 총 블럭 헤드 크기(BLOCK HEADER SIZE)를 계산
STEP 2 - 데이타 블럭 당 사용 가능한 데이타 영역을 계산
STEP 3 - 평균 ROW의 전체 컬럼의 길이(COMBINED COLUMN LENGTH)를 계산
STEP 4 - 총 평균 ROW 크기를 계산
STEP 5 - 데이타 블럭내의 평균 ROW 수를 계산
STEP 6 - 테이블에서 요구되는 블럭과 바이트 수를 계산


1. [ STEP 1 ] : 총 블럭 헤드 크기(TOTAL BLOCK HEADER SIZE)를 계산

1) total block header 

    total block header = block header, part A +
                        block header, part B
    
    block header, part A = (fixed header + variable transaction header) 
    block header, part B = (table directory + row directory)
  
    ** fixed header          : 57 bytes (고정된 블럭 헤드)
      variable transaction  : 23 * I
      header                ( I는 해당 TABLE의 INITRANS의 값)

      table directory        : 4
      row directory          : 2 * R
                              ( R은 블럭의 ROW 수, STEP 5에서 계산)

2) 예를 들어, INITRANS = 1일때 경우의 total block header
    total block header = (57 + (23*1)) + (4 + (2*R))
                      = 80 + (4 + (2*R)) bytes

2. [ STEP 2 ] : 데이타 블럭 당 사용 가능한 데이타 영역
                (DATA SPACE PER DATA BLOCK)을 계산

  1) available data space

    available data space = (block size - total block header) -
                          ((block size - block header, part A) *
                            (PCTFREE/100))
    
    ** block size  :  데이타베이스의 블럭 크기
                      (SQL*DBA의 SHOW PARAMETER에서 
                      db_block_size를 확인)
      
2) 예를 들어, db_block_size=2K, PCTFREE=10 일 경우
    available data space = (2048 - (80 + (4 + 2R)) -
                          ((2048 - 80) * (10 / 100))
                        = (1964 - 2R) - (1968 * 0.1)
                        = (1964 - 2R - 196)
                        = (1768 - 2R) bytes

3. [ STEP 3 ] : 평균 ROW의 전체 컬럼의 길이
                (COMBINED COLUMN LENGTH)를 계산

    ROW 길이를 계산하기 위하여 TABLE 정의에서 컬럼 수, 각 컬럼의 데이타 
  타입, 가변 길이 컬럼의 평균 크기등을 참조한다.

1) D (data space/average row) : table T 가 다음의 스키마로 정의되어 
                                있을 경우의 ROW의 평균 길이
    - create table T ( A char(10), B date, C number(10,2))

  
    D (data space/average row) = ( A + B + C ) 
  

  ** A(컬럼 A의 길이) : 10 bytes -----> CHAR 타입인 경우 fixed length
      B(    B의 길이) :  7 bytes -----> DATE 타입인 경우 
      C(    C의 길이) :  5 bytes = (P / 2) + 1 
                                  -----> NUMBER 타입인 경우에는
                                        PRECISION을 고려해서 계산됨
                                        (단, PRECISION에서 NUMBER의 길 
                                        이를 나타내므로 SCALE은 무관함)
  
4. [ STEP 4 ] : 총 평균 ROW 크기를 계산(TOTAL AVERAGE ROW SIZE) 
                    
  여기서는 테이블의 ROW에 의해 요구되는 최소 사용 영역을 계산하는 공식을 
  제공한다.

1) bytes/row 

      bytes/row = row header + F + V + D

    
  ** row header : ROW 당 3 bytes ( NON-CLUSTERED TABLE)
    F          : 250 bytes 이하를 사용하는 컬럼의 TOTAL LENGTH BYTES
                ( 각 컬럼 당 1 bytes )
    V          : 250 bytes 이상을 사용하는 컬럼의 TOTAL LENGTH BYTES
                ( 각 컬럼 당 3 bytes )
    D          : [STEP 3]의 D (data space/average row)

2) 예를 들어, [STEP 3]의 테이블 T 의 TOATL AVERAGE ROW SIZE
  - 250 byte 이하의 컬럼 수    = 3
    250 byte 이상의 컬럼 수    = 0
    D (data space/average row) = 22 인 경우
  - avg. row size, table T = ( 3 + (1 * 3) + (3 * 0) + 22)
                            = 28 bytes
  
5. [ STEP 5 ] : 데이타 블럭내의 평균 ROW 수(AVERAGE ROWS PER BLOCK)를  계산
                              
  
1) R (avg. # of rows/block) 

    R (avg. # of rows/block) = available space / average row size

  
    ** available space  : [STEP 2]의 available data space
      average row size : [STEP 4]의 bytes/row    

2) 예를 들어, [STEP 2]의 available space가 (1768 - 2R) bytes이고,
    [STEP 4]의 테이블 T의 bytes/row가 28bytes일 경우

    -    R = (1768 - 2R)bytes / 28bytes
      28R = 1768 - 2R
      30R = 1768
        R = 58 rows/block

6. [ STEP 6 ] : 테이블에서 요구되는 블럭과 바이트 수를 계산
                (NUMBER OF BLOCKS AND BYTES)

1) # blocks for table과 # bytes for table  


    # blocks for table = # rows  /  R


    ** # rows  : TABLE의 ROW 수
      R      : [STEP 5]의 R rows/block


    # bytes for table = # blocks for table  *  2048 bytes/block


    ** 2048 bytes/block : 데이타베이스의 블럭 크기 (db_block_size)  


2) 예를 들어, 테이블 T가 10000개의 rows을 가질 경우의 
    테이블 당 블럭 수 
  
  - # blocks for table T = 10000 rows  /  58 rows/block
                        = 173 blocks

  - # bytes for table T  = 173 blocks  *  2048 bytes/block
                        = 354304 bytes
                        = 346 Kbytes

Oracle Korea Customer Support Technical Bulletins를 참조했습니다.

인덱스 사이즈 구하는 방법

인덱스 SIZE를 계산하는 공식입니다.

SELECT GREATEST(4, (1.01) * ((ROW_COUNT /

((floor(((2048 - 113 - (initrans * 23)) *

(1 - (PCT_FREE/100))) /

((10 + uniqueness) + number_col_index +

(total_col_length)))))) *2))

IndexSize_Kbytes

FROM dual;

한개의 BLOCK에 Available 한 Bytes ( 1935 or 2048 - 113 )

각 initrans 는 23 Bytes

ROW_COUNT : table 의 row 의 갯수

PCT_FREE : Index 의 pctfree 값(default 10)

number_col_index : Index 에서 column 의 수

total_col_length : Index 의 길이 추정치

uniqueness : 만일 unique index 면 1, non-unique index 면 0

initrans = 1,PCT_FREE = 10으로놓고 계산해보니 310M정도 나옵니다.

from en-core.com

특정 테이블이 차지하는 크기 알기

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 라는걸 사용해서 테이블 통계를 구하면, 다음과 같이해서 blocks와 empty_blocks를 볼 수 있는데, 이 때 구해지는데 이 블럭수에 DB_BLOCK_SIZE 를 곱하면 거의 정확한 사이즈가 구해지죠. (byte단위)

SELECT blocks, empty_blocks FROM user_tables WHERE table_name = <table_name>

아니면, 실제 물리적으로 점유하고 있는 크기로 알고 싶으시면 dba_segments라는 뷰를 보시면 되는데, 이는 이름 그대로 dba권한이 있어야 하구요.

dba_segments뷰에서 segment_name을 table이름으로 주셔서

SELECT segment_name, bytes, blocks, extent FROM dba_segments WHERE segment_name = <table_name> 로 보시면 됩니다.

dba_segment의 blocks는 user_tables 에서 구한 blocks + empty_blocks가

되구요.

실제로, 테이블은 extent단위로 확장되기 때문에 현재 점유하고 있는 물리적인 크기는 dba_segment가 맞구요. 최적의 extent를 구성하기 위해서는 user_tables의 blocks로 고려해야겠죠.

from oracle forum