Leitura de índices B-Tree – Considerações

Deve-se fazer apenas considerações (aka “bitaites”) sobre o uso de determinados índices se realmente sobermos aquilo que estamos a falar, caso contrário, caímos na especulação e somos o próximo treinador de bancada falhado.

 

O post de hoje vai ajudar a entender isso, no entanto não esperem conversa para iniciantes.

A abordagem vai ser basicamente um misto de teoria e prática sobre índices Btree.

Para iniciar criaremos uma tabela:


SQL> create table indx_con1 as select rownum as R0, mod(rownum, 200) as R1 from dual connect by level <= 200000;

Table created.


SQL> create index i1 on indx_con1(R0);

Index created.

A primeira análise a fazer será na tabela dba_indexes para analisarmos os dados relativos ao índice que acabámos de criar. Vejamos:

SQL> select blevel, leaf_blocks, clustering_factor from dba_indexes where index_name = 'I1';

BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
1 445 372

Isto significa que o índice consiste basicamente apenas pelo Root Block (nó pai), informação dada pelo BLEVEL=1 (profundidade da árvore), por 445 leaf blocks (nós filhos). Como tal se necessitarmos de ler uma determinada entrada do índice será necessário ler primeiramente o root block (nó pai) e posteriormente o leaf block especifico (nó filho). No total serão 2 leituras.

Vamos então verificar se esta explicação coincide com aquela que o CBO nos fornece.

 


SQL> set autotrace traceonly explain statistics
SQL> select * from indx_con1 where R0=22;


| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 0 0:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| INDX_CON1 | 1 | 26 | 2 (0)| 0 0:00:01 |

|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 0 0:00:01 |

-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("R0"=22)

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
591 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

Podemos tirar várias ilações sobre os resultados anteriores nomeadamente:

1 – O custo de aceder ao índice (e apenas ao índice) é de 1, significa que o CBO sabe que a profundidade da árvore é 1 (BLEVEL=1), o índice é pequeno logo o root block (nó pai) está em cache e como tal é excluido do cálculo do custo.

2 – O custo TOTAL do plano (e não apenas de ler a entrada no índice) é 2 (ver Cost no Id 0 no plano), ou seja, dado que o root block está excluído do custo, é necessário uma leitura para o leaf block (nó filho) e outra leitura para ler o bloco na tabela, pois neste caso e dada a query executada apenas a leitura do índice não é suficiente pois não contem todas as colunas que pretendemos devolver.

3 – O número estimado de linhas a devolver é 1 (ver coluna Rows).

4 – O número de consistent gets é 4: 1 para ler o root block (nó pai em cache), 1 para ler o leaf block (nó filho), 1 para ler o table block (acesso à tabela) e por fim mais 1 pois o indice não é unique e é necessário perceber se há mais registos a retornar.

Como conclusão, uma análise simples mas essencial para quem pretende saber realmente do que fala 😉

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