Quando o assunto é compressão, as primeiras options que vem em mente são table compression e advanced row compression (que ajudam a reduzir o tamanho da base em um fator de 2 a 5 vezes, variando de ambiente para ambiente).
Porém não podemos esquecer que muitas vezes os índices podem representar um tamanho superior do que os dados, uma vez que para cada tabela podemos ter N índices.
Algumas vezes os índices podem representar mais de 50% do tamanho da base, por isso é importante lembrar que desde a versão 8.1.3 (antes mesmo da table compression lançada na versão 9.2) temos a feature index key compression.
A feature index key compression é uma feature Enterprise Edition (sem custo adicional) que atua na redução dos valores repetidos, comprimindo os dados e dividindo o índice em 2 partes o principal grupo de colunas, denominado entrada de prefixo (potencialmente compartilhado em vários valores de chave) e as colunas de sufixo (que é exclusivo para cada chave de índice). Como os prefixos são potencialmente compartilhados em várias chaves de um bloco, estes podem ser armazenados de forma mais otimizada (ou seja, apenas uma vez) e compartilhados em múltiplas entradas de sufixo, resultando na compactação dos dados de um índice. Além da compactação o armazenamento de mais chaves para cada bloco de índice implica na melhora do desempenho uma vez que causará menos I/O.
A figura abaixo, ilustra a diferença estrutural entre um índice não comprimido e um índice comprimido.
Porém para que esta opção traga benefício ao ambiente, precisamos validar se o índice que estamos analisando é um índice que terá ganhos caso com a feature index key compression.
Para descobrir esta informação, precisamos realizar um analyze do indice e então consultar se o OPT_CMPR_COUNT é >= 1.
Estarei criando uma tabela chamada lamimtst, copia da dba_objects e um índice para as colunas ower e status, para podermos demonstrar na pratica o processo de compressão do índice e os ganhos do mesmo.
SQL> conn lamim/lamim Connected. SQL> SQL> conn lamim Enter password: Connected. SQL> create table lamimtst as select * from dba_objects; Table created. SQL> SQL> create index ixlamimtst on lamimtst(OWNER,STATUS); Index created.
Realizada a criação da tabela do índice para demonstração, vou verificar o tamanho do indice e gerar um trace de um select que utilize o mesmo antes de aplicarmos a compressão.
SQL> select segment_name, sum(bytes/1024) from user_segments where segment_name='IXLAMIMTST' group by segment_name;
SEGMENT_NAME SUM(BYTES/1024)
--------------------------------------------- ---------------
IXLAMIMTST 4096
select * from lamimtst
where STATUS='VALID'
and OWNER='LAMIM'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 96 82 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.05 97 87 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID LAMIMTST (cr=5 pr=1 pw=0 time=217 us cost=1 size=207 card=1)
1 1 1 INDEX RANGE SCAN IXLAMIMTST (cr=4 pr=0 pw=0 time=50 us cost=1 size=0 card=1)(object id 268044528)
Agora vamos realizar um analyze do índice para verificar se o mesmo é elegível para compressão.
SQL> set lines 210 SQL> select name, height, blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE FROM index_stats; NAME HEIGHT BLOCKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ------------------------------ ---------- ---------- -------------- ---------------- IXLAMIMTST 3 512 2 53
No analyze realizado acima, podemos constatar que utilizando um fator de compressão 2 (OPT_CMPR_COUNT) teremos uma redução de aproximadamente 53% (OPT_CMPR_PCTSAVE) na área utilizada pelo mesmo.
Agora, vamos comprimir o índice e realizar ma nova validação do seu tamanho e gerar um novo trace da mesma consulta para verificar os ganhos obtidos.
SQL> alter index IXLAMIMTST rebuild compress 2;
Index altered.
SQL> select segment_name, sum(bytes/1024) from user_segments where segment_name='IXLAMIMTST' group by segment_name;
SEGMENT_NAME SUM(BYTES/1024)
--------------------------------------------------------------------------------- ---------------
IXLAMIMTST 2048
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 22 89 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.05 22 63 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 71
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID LAMIMTST (cr=4 pr=0 pw=0 time=82 us cost=1 size=207 card=1)
1 1 1 INDEX RANGE SCAN IXLAMIMTST (cr=3 pr=0 pw=0 time=53 us cost=1 size=0 card=1)(object id 268044528)
É possível verificar que nosso índice que inicialmente possuía um tamanho de 4096 kB após a compressão passou a ter 2048 kB. Uma redução de 50% no tamanho do mesmo.
Vale destacar também que nosso se observarmos a coluna disk do trace gerado, temos uma redução de aproximadamente 77% no I/O gerado pela consulta.
Fonte:
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf
http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT1177
http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317
https://blogs.oracle.com/dbstorage/compressing-your-indexes:-index-key-compression-part-1
