Leitura de índices B-Tree: Alteração do Clustering Factor

POR LUÍS MARQUES, ÀS 20:47 | COMENTAR

Como já tinha explicado antes, o CF (vou me referir a partir de agora como CF, para me facilitar) é basicamente uma métrica que compara a ordem no índice com o grau de desordem na tabela, ou de outra forma se quiserem, é forma como os dados estão alinhados na tabela em relação à ordem no índice e o I/O necessário para ler a tabela inteira via full index scan.

Dado um índice é organizado e ordenado, um “rebuild” ao índice nunca (ou em circunstâncias especiais)  alterará o valor do CF, pois a ordem das entradas no índice mantêm-se igual após o rebuild tal e qual a ordem dos registos na tabela. Assim é facil entender que para alterar o valor do CF temos que reorganizar a tabela associada e assim alterar o valor do CF.

É bom relembrar ainda que por norma um bom CF é um valor igual (ou abaixo, dado que podem existir blocos vazios abaixo do HWM) ao número de registos da tabela a que se refere o índice.

Vamos então ao exemplo:

SQL> create table t_cf as select dbms_random.value(0,500) as N1, 
dbms_random.string('A',45) as A1, 
dbms_random.string('l',45) as L1 from dual connect by level <= 400000;
Table created.


SQL> create index i1_cf on t_cf(A1);

Index created.


SQL> exec dbms_stats.gather_table_stats('LCMARQUES','T_CF', cascade=>TRUE);

PL/SQL procedure successfully completed.



SQL> select blevel, leaf_blocks, clustering_factor from user_indexes where index_name = 'I1_CF';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        3200            399942



SQL> select num_rows, blocks from user_tables where table_name = 'T_CF';

  NUM_ROWS     BLOCKS
---------- ----------
    400000       6779

Temos um índice recém criado (I1_CF) que tem um CF de 3999942, um valor bastante distante do número de blocos da tabela (6779), fazendo dele um índice com um mau CF (relembrar apenas que o CF é apenas um dos critérios escolhidos pelo CBO). Vamos então tentar reorganizar os mesmo dados e obter um CF diferente, um pouco melhor:

SQL> create table t2_cf as select * from T_CF;

Table created.

SQL> truncate table T_CF;

Table truncated.

SQL> insert into t_cf select * from t2_cf order by A1;

400000 rows created.

SQL> exec dbms_stats.gather_table_stats('LCMARQUES','T_CF', cascade=>TRUE);


SQL> select blevel, leaf_blocks, clustering_factor from user_indexes where index_name = 'I1_CF';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        2858              6558

A estratégia foi simples, existem várias formas de reorganizar a tabela e uma delas é usando os CTAS, ou seja criou-se uma tabela auxiliar com os dados e inseriu-se posteriormente na tabela original (T_CF) ordenamente pela coluna que o índice contem. Assim os registos inseridos estão agora organizados exactamente da mesma forma que o índice, levando como é obvio a um valor bastante bom de CF e a um decréscimo do I/O na próxima visita à tabela por índice full scan.

No entanto, existe uma dúvida e é legitima, dado que criou-se uma tabela com 400k registos, depois um índice, levantou-se as estatísticas e o valor do CF saiu péssimo. Isto acontece pois na criação do índice inicialmente os registos na tabela estão desorganizados e na natureza de qualquer índice estes encontram-se devidamente ordenados segundo as colunas presentes, dai que a opção viável para alterar o CF será reordenar a tabela.

Vimos aqui como um índice recém-criado pode não corresponder às espectativas em termos de custo, levando a um excessido consumo de I/O dado que será necessário re-visitar o mesmo bloco “n” vezes dada a aleatoriedade dos blocos (e registos) na tabela, para tal a análise do CF deve ser cuidada, dado que não é o único factor que influencia a decisão do CBO, mas é um bastante importante. Importante também que se a tabela conter mais índices, decidir o critério de organização nem sempre é facil.

Existem outras formas de alterar o valor do CF, entre elas mexendo apenas no índice, mas deixarei isso para a parte 2 deste ponto.

Tinha mostrado anteriormente que apenas reorganizando a tabela seria possível alterar o valor do CF e que  uma reorganização do índice nada poderia fazer, pois o CF depende directamente da desordem dos blocos na tabela comparada com a organização no índice. No entanto existe uma forma relativamente simples de alterar o valor do CF que é usando reverse key index (não sei o termo em português).

Existem alguns casos onde este tipo de índices é útil, que foram desenhados essencialmente para resolver o problema de contenção nos blocos (index block contention). Basicamente ocorrem em cenários de muita concorrência (DML insert, update ou delete) onde as várias sessões concorrentes precisam de aceder ao mesmo bloco (chamado “hot block”) gerando assim contenção ao nível do bloco do índice causando inumeros wait events do tipo “buffer busy waits”, por exemplo.

Mas o post não é sobre os Reverse Key Index, mas sim sobre o CF e como este valor pode ser alterado usando um índice reverted. A alteração da ordem no índice leva a que a desordem na tabela seja diferente. Como exemplo simples, se um ID para inserir na tabela for gerado como 112233 será inserido como 332211 no índice. Este tipo de “reverse” permite que os inserts sejam espalhados por toda a estrutura do índice, evitando a contenção em apenas um só leaf bloco (o mais há direita). Com isto fazemos com que as entradas no índice deixem de estar ordenadas da forma natural e como conhecemos, ou seja, o 112234 a seguir ao 112233.
Apesar de parecer resolver alguns problemas nomeadamente em ambientes RAC muito concorridos, cria uma outra panóplia de problemas que não discutiremos neste post.

O código seguinte mostrará uma tabela, um índice normal que depois será convertido para “reverse” e os respectivos
valores do CF após cada etapa:

SQL> create table t_cf2 as select ceil(dbms_random.value(0,100000)) N1 from dual connect by level <= 100000;

Table created.


SQL> create index i_cf2 on t_cf2(N1);

Index created.


SQL> exec dbms_stats.gather_table_stats(null,'T_CF2', cascade=>TRUE);

PL/SQL procedure successfully completed.


SQL>  select blevel, leaf_blocks, clustering_factor  from user_indexes where index_name = 'I_CF2';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1         222             99270

SQL> alter index i_cf2 rebuild reverse;

Index altered.


SQL>  select blevel, leaf_blocks, clustering_factor  from user_indexes where index_name = 'I_CF2';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1         222             99265

Temos uma valor diferente no CF, sendo que a diferença é pouca o que significa que o meu exemplo aqui não foi o melhor, no entanto, como nota final não devem de forma alguma usar os RKI sem cuidado, pois a ordem das chaves deixa de ser a natural e os “range scans” deixam de ser possíveis (predicados como BETWEEN, LIKE, > <) e o CBO vai por completo ignorar este tipo de índices. Estes dois posts sobre o CF foram apenas para fazer entender como o CF varia em função das várias ordens seja na tabela ou no índice.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em INDEX TUNING, PLSQL TUNING e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s