Indices e Estatisticas

O ponto a discutir neste post, é como o Oracle avalia blocos de indices vazios, no cálculo de estatisticas de indices e que implicações isto pode ter no CBO .

Pra entender como isso funciona, vou criar uma tabela com 10000 linhas e posteriormente deletar a maioria delas.

SQL> CREATE TABLE alex
2 AS SELECT rownum id, ‘Rush’ text
3 FROM dual
4 CONNECT BY level <=10000;

Tabela criada.

SQL> create index alex_id on alex(id);

Índice criado.

Temos então um indice com 10000 entradas. Vamos ver quanto leaf blocks nós temos

SQL> analyze index alex_id validate structure;

Índice analisado.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

LF_ROWS LF_BLKS DEL_LF_ROWS
———- ———- ———–
10000 21 0

Portanto neste momento temos 10000 LF_ROWS, 21 LF_BLKS e nenhuma linha indexada deletada.

Vamos deletar a maioria das linhas e consequentemente as entradas de indice.

SQL> delete alex where id <=9990;

9990 linhas deletadas.

SQL> commit;

Commit concluído.

Certo, agora nos temos um indice com a maioria de suas entradas deletadas e todos os blocos folha dos indice menos um vazios.

Vamos ver, inicialmente como o ANALYZE lida com estes blocos de indices vazios e com a deleções.

SQL> analyze index alex_id validate structure;

Índice analisado.

SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;

LF_ROWS LF_BLKS DEL_LF_ROWS
———- ———- ———–
10000 21 9990

Como voce pode notar a estatistica LF_ROWS ainda mostra o valor 10000, ou seja, ainda conta as entradas dos indice apesar da deleção. LF_BLKS também continua 21, portanto os blocos vazios ainda são levados em consideração.

Vamos ver o que acontece quando utilizamos os DBMS_STATS

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘ALEX’, cascade => true, estimate_p
ercent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);

Procedimento PL/SQL concluído com sucesso.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘ALEX_ID’;

INDEX_NAME      NUM_ROWS LEAF_BLOCKS
————- ———- ———–
ALEX_ID                              10                    1

Notamos imediatamente duas diferenças.

Primeira : NUM_ROWS igual a 10. Mostrando que apenas as entradas não deletadas são levadas em consideração.

Segunda : LEAF_BLOCKS igual a 1. Mostrando que apenas os blocos que contem entradas não deletadas são levados em consideração. Embora a estrutura do indice possua outrso 20 “Leaf Blocks”, estes não são considerados pelo CBO do Oracle quando utilizamos DBMS_STATS.

Vamos apenas executar uma consulta simples para ver seu plano de execução.

SQL> select * from alex where id between 1 and 10000;

ID TEXT
———- —-
9991 Rush
9992 Rush
9993 Rush
9994 Rush
9995 Rush
9996 Rush
9997 Rush
9998 Rush
9999 Rush
10000 Rush

10 linhas selecionadas.

Plano de Execução
———————————————————-
Plan hash value: 1467085234

——————–
| Id | Operation | Name |
——————–
| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX |
|* 2 | INDEX RANGE SCAN | ALEX_ID |
———————————-

O indice é utilizado por que o custo de é baixo.

Já se usarmos o ANALYZE

Vejamos se tem diferenças

SQL> analyze index alex_id COMPUTE STATISTICS;

Índice analisado.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘ALEX_ID’
2 /

INDEX_NAME NUM_ROWS LEAF_BLOCKS
————— ———- ———–
ALEX_ID 10 21

Como voce pode notar o Analyze leva em consideração os blocos vazios. LEAF_BLOCKS 21 em vez de apenas 1 como anteriormente. Mas, blocos folhas (leaf blocks) são apenas uma das estatisticas que o CBO utiliza quando calcula o custo de se utilizar um determinado indice como acesso aos dados. E qual o impacto disso na nossa consulta.

SQL> select * from alex where id between 1 and 10000;

ID TEXT
———- —-
9991 Rush
9992 Rush
9993 Rush
9994 Rush
9995 Rush
9996 Rush
9997 Rush
9998 Rush
9999 Rush
10000 Rush

10 linhas selecionadas.

Plano de Execução
—————————————————–
Plan hash value: 2452671568

—————————
| Id | Operation | Name|
—————————
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| ALEX |
—————————-

Agora o CBO decidiu fazer um full table scan, devido ao custo adicional calculado ao se utilizar o indice.

Portanto blocos de indices vazios tem um grande impacto, não apenas em como a consulta pode ser realizada, mas como o CBO calcula o custo associado, dependendo de como a estatistica tenha sido gerada.

Esta é apenas uma das razões pelas quais voce deve utilizar o DBMS_STATS ao invés do ANALYZE