[Table] 테이블 사이즈 예측

# How To Estimate the Size of Table

 

신규 테이블 생성 후 적재되는 n건 rows에 대한 대략적인 사이즈를 확인하고 싶을때 활용.

 

--. Test 1

drop table test;
 
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
 
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
 
commit;
 
select * from test;
 
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'ora',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
 
select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';
 
--AVG_ROW_LEN
-------------
--         77
 
Drop table test;
 
set serveroutput on
 
DECLARE
 ub NUMBER;
 ab NUMBER;
BEGIN
  DBMS_SPACE.CREATE_TABLE_COST('USERS',77,100000,10,ub,ab);
  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub));
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
 
-- 예상
-- Used Bytes      = 9109504
-- Allocated Bytes = 9437184
 
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
 
BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/
 
select BYTES, SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
 
-- 실제
-- BYTES SEGMENT_TYPE
-- ------------- ------------------
--       9437184 TABLE

 

--. Test 2

set serveroutput on;
 
DECLARE
 ub NUMBER;
 ab NUMBER;
 cl sys.create_table_cost_columns;
BEGIN
  cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
        sys.create_table_cost_colinfo('CHAR',30),
        sys.create_table_cost_colinfo('CHAR',30),
        sys.create_table_cost_colinfo('DATE',NULL));
 
  DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab);
 
  DBMS_OUTPUT.PUT_LINE('Used Bytes      = ' || TO_CHAR(ub));
  DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
 
-- 예상
-- Used Bytes      = 9314304
-- Allocated Bytes = 9437184
 
drop table test;
 
create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;
 
BEGIN
  FOR i IN 1..100000 LOOP
  INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/
 
select BYTES, SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
 
-- 실제
--      BYTES SEGMENT_TYPE
-- ---------- ------------------
--     9437184 TABLE