Qual é mesmo o tamanho de uma tabela no Oracle?

Por Eduardo Legatti
Postado en setembro 2011

Através das views de dicionário de dados DBA/ALL/USER_SEGMENTS, pode-se facilmente obter essa informação, e de várias formas. No entanto, você verá que o tamanho de uma tabela (em bytes) vai muito além do que focar especificamente no segmento da tabela. Sabemos que no Oracle existem vários tipos de segmentos, como segmentos de tabela, índice, lob (large objects), undo, temp, partition, ente outros, mas iremos focar naqueles que geralmente estão mais presentes na maioria dos schemas de banco de dados. Possivelmente alguém não irá concordar comigo, mas, na minha visão, o tamanho real de uma tabela compõe-se não só do tamanho do seu próprio segmento, mas também da soma do tamanho de todos os outros segmentos que dependem dela. Para demonstrar tais informações, executarei uma query que mostram essas informações logo mais abaixo

Bom, após criar 9 tabelas devidamente populadas no meu schema de banco de dados, a soma do tamanho de todos os segmentos foi de aproximadamente 3,14 GB como demonstrado pela query abaixo:

SQL> select count(*) segments,
  2         round(sum(bytes)/1024/1024,2) size_mb
  3    from user_segments;

  SEGMENTS    SIZE_MB
---------- ----------
       250    3216,19

Vale a pena salientar que algumas dessas tabelas são particionadas, outras possuem índices normais, locais ou globais, e outras tabelas possuem colunas do tipo CLOB. Portanto, podemos verificar pelo resultado da query acima que a criação de 9 tabelas gerou um total de 250 segmentos. Agora irei executar a query que eu criei de forma a demonstrar como esses 3,14 GB estão distribuídos entre essas 9 tabelas.

SQL> break on report
SQL> compute sum of data_mb on report
SQL> compute sum of indx_mb on report
SQL> compute sum of lob_mb on report
SQL> compute sum of total_mb on report

SQL> select table_name,
  2    decode(partitioned,'/','NO',partitioned) partitioned,
  3    num_rows,
  4    data_mb,
  5    indx_mb,
  6    lob_mb,
  7    total_mb
  8     from (select data.table_name,
  9             partitioning_type
 10             || decode (subpartitioning_type,
 11                        'none', null,
 12                        '/' || subpartitioning_type)
 13                    partitioned,
 14             num_rows,
 15             nvl(data_mb,0) data_mb,
 16             nvl(indx_mb,0) indx_mb,
 17             nvl(lob_mb,0) lob_mb,
 18             nvl(data_mb,0) + nvl(indx_mb,0) + nvl(lob_mb,0) total_mb
 19             from (  select table_name,
 20                   nvl(min(num_rows),0) num_rows,
 21                   round(sum(data_mb),2) data_mb
 22                      from (select table_name, num_rows, data_mb
 23                          from (select a.table_name,
 24                                a.num_rows,
 25                                b.bytes/1024/1024 as data_mb
 26                                  from user_tables a, user_segments b
 27                                  where a.table_name = b.segment_name))
 28                 group by table_name) data,
 29                 (  select a.table_name,
 30                        round(sum(b.bytes/1024/1024),2) as indx_mb
 31                     from user_indexes a, user_segments b
 32                       where a.index_name = b.segment_name
 33                    group by a.table_name) indx,
 34                 (  select a.table_name,
 35                       round(sum(b.bytes/1024/1024),2) as lob_mb
 36                    from user_lobs a, user_segments b
 37                   where a.segment_name = b.segment_name
 38                    group by a.table_name) lob,
 39                   user_part_tables part
 40             where     data.table_name = indx.table_name(+)
 41                   and data.table_name = lob.table_name(+)
 42                   and data.table_name = part.table_name(+))
 43  order by table_name;


TABLE_NAME  PARTITIONED  NUM_ROWS   DATA_MB   INDX_MB    LOB_MB  TOTAL_MB
----------- ----------- --------- --------- --------- --------- ---------
T1          NO            5912285       576      1160         0      1736
T2          RANGE         1597647     75,81     70,94         0    146,75
T3          NO             700890        80         0         0        80
T4          NO              24008         4        ,5       287     291,5
T5          RANGE/HASH      60000      66,5     11,25       1,5     79,25
T6          RANGE/LIST    1572864    236,44    154,31     96,69    487,44
T7          LIST          1527191        23        95         0       118
T8          NO              19441         3      1,25       271    275,25
T9          NO              10565         2         0         0         2
                                  --------- --------- --------- ---------
sum                                 1066,75   1493,25    656,19   3216,19

9 linhas selecionadas.

Perceberam que do total de 3216,19 MB, 33% (1066,75 MB) representam segmentos de dados de tabela, 47% (1493,25 MB) segmentos de índices e 20% (656,19 MB) segmentos de lob? O gráfico abaixo nos dá uma visão mais clara.

Através das informações retornadas pela query, vemos que o segmento de dados da tabela T1 possui um tamanho de 576 MB (em aproximadamente 6 milhões de linhas), e que os índices desta tabela somam 1160 MB dando um total de 1736 MB. Portanto, na minha visão, o tamanho real desta tabela é de 1736 MB, e não 576 MB como muitos poderão dizer. Lembrem-se de que eu não estou falando apenas do segmento de tabela (T1), mas de todos os outros que dependem dela, como segmentos de índice e de lob por exemplo.

Vejamos agora a tabela T4. Apesar do tamanho do seu segmento de dados ter apenas 4 MB, a mesma tem como dependentes um ou mais índices que somam 0,5 MB e um ou mais segmentos lob que somam 287 MB. Portanto, podemos verificar que o tamanho real desta tabela é de 291,5 MB e não apenas 4 MB.

Se alguém me perguntasse qual o espaço necessário que precisaria ser alocado na tablespace para duplicar as linhas da tabela T6 nela própria, eu diria que precisaríamos de no mínimo 487,44 MB e não 236,44 MB.

Portanto, deixo aqui a dica de não apenas focarmos no tamanho do segmento da tabela, mas também de prestarmos atenção em todos os outros segmentos dependentes dela. Isso com certeza fará diferença quando fizermos algum trabalho relacionado à estimativa para alocação de espaço destes objetos, ou até mesmo nas estimativas de espaço necessárias para uma migração de dados, etc...

Por fim, o gráfico abaixo nos mostra todos os tipos de segmentos que foram criados e a proporção de espaço alocado de cada um




Postado por Eduardo Legatti (http://eduardolegatti.blogspot.com), Analista de Sistemas e DBA Oracle. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g – OCE, e vem trabalhando como DBA Oracle desde a versão 8.0.5.