------------------------------------------------------
TABLE SIZE °è»ê °ø½Ä(ORACLE BLOCK SIZE : 2K ·Î °¡Á¤)
------------------------------------------------------
$ sqlplus scott/tiger
SQL> SELECT GREATEST(4, ceil(ROW_COUNT /
((round(((1958 - (initrans * 23)) *
((100 - PCTFREE) /100)) / ADJ_ROW_SIZE)))) * BLOCK_SIZE)
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 ÀÇ °¹¼ö
*. BLOCK_SIZE : 1 blockÀÇ Å©±â (´ÜÀ§: K)
¿¹) table À̸§ÀÌ EMP ÀÏ °æ¿ì
ROW_COUNT : select count(*) from emp;
ADJ_ROW_SIZE : analyze table emp compute statistics;
(¶Ç´Â °Ç¼ö°¡ ¸Å¿ì ¸¹À» ¶§¿¡´Â compute ´ë½Å estimate »ç¿ë)
select avg_row_len
from user_tables
where table_name='EMP';
---------------------
INDEX SIZE °è»ê °ø½Ä
---------------------
SQL> SELECT GREATEST(4, (1.01) * ((ROW_COUNT /
((floor(((2048 - 113 - (initrans * 23)) *
(1 - (PCTFREE/100))) /
((10 + uniqueness) + number_col_index +
(total_col_length)))))) * DB_BLOCK_SIZE))
IndexSize_Kbytes
FROM dual;
*. ÇÑ °³ÀÇ block¿¡ available ÇÑ bytes ( 1935 or 2048 - 113 )
*. °¢ initrans ´Â 23 Bytes
*. ROW_COUNT : table ÀÇ row ÀÇ °¹¼ö
*. PCTFREE : Index ÀÇ pctfree °ª(default 10)
*. number_col_index : Index ¿¡¼ column ÀÇ ¼ö
*. total_col_length : Index ÀÇ ±æÀÌ ÃßÁ¤Ä¡
*. uniqueness : ¸¸ÀÏ unique index À̸é 1, non-unique index À̸é 0.
*. DB_BLOCK_SIZE : 1 blockÀÇ Å©±â (´ÜÀ§: K)
from http://devworld.co.kr