Neste artigo será demonstrada
a vantagem da utilização de compactação dos índices.
Um índice criado com uma coluna que possuam muitos registros duplicados pode ser ajustado para
economizar espaço em disco, e também para melhorar o
I/O.
Quando um índice é criado no Oracle, a compressão
é desabilitada (por DEFAULT). Se um
índice for definido em uma coluna que contenha nomes
de todos os clientes,
alguns dos nomes são muito comuns, como por exemplo um sobrenome. Pode-se tirar proveito desta duplicação de dados por meio da compressão do índice.
alguns dos nomes são muito comuns, como por exemplo um sobrenome. Pode-se tirar proveito desta duplicação de dados por meio da compressão do índice.
Índices compactados reduzem o espaço de armazenamento, permitindo caber mais linhas em menos folhas de blocos (Leaf Blocks) o que indicará menos atividades de I/O, durante os acessos aos dados.
[Conceito]
Folhas de Blocos: Ou ”Leaf Blocks” são os blocos de índice. Em um índice de B-tree, blocos de índices são blocos de um ramo (são o blocos do "topo" em um índice deB-tree) e os blocos de folhas (são os blocos "inferiores").
Estrutura interna de um índice B-tree
Oracle Database 11g Concepts
A seguir será feita
uma demonstração da compactação de índices:
Criação
da tabela AUTORES.
SQL>
CREATE TABLE AUTORES (AUTORES_ID INT, NOME_AUTORES VARCHAR2(40));
Table
created.
Inclusão de 10
linhas na tabela AUTORES.
SQL> INSERT INTO AUTORES VALUES(1,'Ariano Suassuna');
1 row created.
SQL> INSERT INTO AUTORES VALUES(2,'Caetano
Teixeira');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(3,'Machado de Assis');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(4,'Jorge Amado');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(5,'Eça de Queiroz');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(6,'Zelia Gatai');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(7,'José Sarney');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(8,'Clarice Linspector');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(9,'Cora Coralina');
1
row created.
SQL>
INSERT INTO AUTORES VALUES(10,'Carlos Drummond');
1
row created.
SQL> COMMIT;
Commit complete.
Verificação
das linhas incluídas na tabela.
SQL> SELECT * FROM AUTORES;
AUTORES_ID
NOME_AUTORES
----------
----------------------------------------
2 Caetano Teixeira
3 Machado de Assis
4 Jorge Amado
5 Eça de Queiroz
6 Zelia Gatai
7 José Sarney
8 Clarice Linspector
9 Cora Coralina
10 Carlos Drummond
1 Ariano Suassuna
10
rows selected.
Criação
de um índice utilizando a coluna (NOME_AUTORES).
SQL>
CREATE INDEX AUTORES_IDX ON AUTORES(NOME_AUTORES);
Index
created.
Geração das estatísticas do índice.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME
=> 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE
AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);
PL/SQL procedure successfully completed.
Verificar as estatísticas do índice.
SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,
ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER
BLOCK", DISTINCT_KEYS,
NUM_ROWS,(NUM_ROWS-DISTINCT_KEYS) DUP_ROWS
FROM USER_INDEXES
WHERE INDEX_NAME = 'AUTORES_IDX'
ORDER BY DUP_ROWS DESC;
INDEX_NAME COMPRESS LEAF_BLOCKS ROWS PER BLOCK
DISTINCT_KEYS NUM_ROWS DUP_ROWS
----------- -------- ----------- --------------
------------- -------- ---------
AUTORES_IDX DISABLED 1 10 10 10 0
Nota: Como demonstrado, existe um bloco de folhas (leaf blocks)
que contém os dados, são 10 linhas por bloco.Há um total de 10 linhas no total, e não existem nomes
duplicados.
Adicionando 1.000 linhas com o mesmo
nome.
SQL> DECLARE v_a NUMBER;
BEGIN
v_a := 11;
WHILE v_a < 1000
LOOP
INSERT INTO AUTORES VALUES(v_a,'Oliveira');
v_a := v_a + 1;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure
successfully completed.
Verificando a quantidades de linhas
inseridas na tabela AUTORES.
SQL> SELECT COUNT(*) FROM AUTORES;
COUNT(*)
----------
999
Atualizando as estatísticas do índice.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME
=> 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE
AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);
PL/SQL procedure successfully completed.
Verificar as
estatísticas do índice
SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,
ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER
BLOCK", DISTINCT_KEYS,
NUM_ROWS,(NUM_ROWS-DISTINCT_KEYS) DUP_ROWS
FROM USER_INDEXES
WHERE INDEX_NAME = 'AUTORES_IDX'
ORDER BY DUP_ROWS DESC;
INDEX_NAME COMPRESS LEAF_BLOCKS ROWS PER BLOCK
DISTINCT_KEYS NUM_ROWS DUP_ROWS
------------ -------- ----------- --------------
------------- --------- ---------
AUTORES_IDX
DISABLED 4 250 11 999
988
Nota: Como demonstrado, existe quatro blocos de folhas
(leaf blocks) que contém os dados,
observar, são 250 linhas por bloco.Há um
total de 999 linhas no total,
dos quais 988 entradas são duplicados (sobrenome = Oliveira).
Recriando o mesmo índice, porém de modo compactado.
SQL> DROP INDEX AUTORES_IDX;
Index
dropped.
SQL> CREATE INDEX AUTORES_IDX ON AUTORES(NOME_AUTORES)COMPRESS TABLESPACE
USERS;
Index created.
Geração das estatísticas do índice.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME
=> 'SCOTT', TABNAME => 'AUTORES', METHOD_OPT => 'FOR ALL COLUMNS SIZE
AUTO',ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE => TRUE);
PL/SQL
procedure successfully completed.
Verificação das estatísticas do índice
compactado.
SQL> SELECT INDEX_NAME,COMPRESSION,LEAF_BLOCKS,
ROUND(NUM_ROWS/DECODE(LEAF_BLOCKS,0,1,LEAF_BLOCKS)) "ROWS PER
BLOCK", DISTINCT_KEYS,
NUM_ROWS, (NUM_ROWS-DISTINCT_KEYS) DUP_ROWS
FROM USER_INDEXES
WHERE INDEX_NAME = 'AUTORES_IDX'
ORDER BY DUP_ROWS DESC;
INDEX_NAME COMPRESS LEAF_BLOCKS ROWS PER BLOCK
DISTINCT_KEYS NUM_ROWS DUP_ROWS
------------ --------
----------- -------------- ------------- -------- --------
AUTORES_IDX ENABLED 2 500 11 999
988
Como podemos verificar, agora, existem apenas dois blocos de
folhas (leaf blocks) e 500 linhas estão num mesmo
bloco. Com esse dispositivo pode-se verificar a economia de espaço em disco.
Referências:
Oracle® Database SQL Language Reference 11g Release 2 (11.2)
Oracle® Database Concepts 11g Release 2 (11.2)
Oracle® Database Administrator's Guide 11g Release 2 (11.2)
MSc. Rubens Oliveira
DBA Oracle Consultor
