[Index] 인덱스 사이즈 예측

# How To Estimate the Size of Index

 

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 (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
  END LOOP;
 END;
/
 
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'orakim',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
 
set serveroutput on
 
declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index test_indx on test (a,b) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   
 
-- 예상
-- Used Bytes      = 3800000
-- Allocated Bytes = 6291456
 
create index test_indx on test (a,b) tablespace users;
 
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';
 
-- 실제 
--      BYTES SEGMENT_TYPE
-- ---------- ------------------
--    6291456 INDEX
 
 
-------------------------------------------
-- 테스트 emp_1 테이블
-------------------------------------------
 
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'orakim',tabname=>'emp_1',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
 
set serveroutput on
 
declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'CREATE INDEX IDX_01 ON EMP_1(ENAME,JOB)',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/   
 
-- 예상
-- Used Bytes      = 132120576
-- Allocated Bytes = 260046848
 
drop index idx_01;
 
create index idx_01 on emp_1(ename,job);
 
select BYTES/1024/1024,SEGMENT_TYPE from user_segments where SEGMENT_NAME='IDX_01';
 
-- 실제
-- 276824064    INDEX