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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s