Oracle估算表大小的小方法

1.创建测试表和索引

SQL> create table t as select * from dba_objects;

Table created.

 

SQL> create index t_ind on t(object_id);

Index created.

 

2.收集统计信息

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);             

PL/SQL procedure successfully completed.

 

3.计算

SQL> select avg_row_len from user_tables where table_name='T';

AVG_ROW_LEN
-----------
  98

 

SQL> select segment_name,segment_type,bytes from user_segments where segment_name in('T','T_IND');

SEGMENT_NAME           SEGMENT_TYPE    BYTES
--------------------------------------------------------------------------------- ------------------ ----------
T            TABLE   9437184
T_IND            INDEX   2097152

 

SQL> select trunc((2097152/9437184)*100) ind_pct from dual;

   IND_PCT
----------
 22

 

表大小=row_number*98 *(1+0.22) bytes

 

整理自《让Oracle跑的更快》谭怀远