Menu de navegação

[ELobaoParticipante]

Bom dia a todos

Tenho uma tabela com cerca de 5milhoes de linhas sendo que meu filtro de pesquisa retorna cerca de 800mil linhas.

Meu problema é quando faço consulta sobre um campo varchar indexado. Preciso filtrar somente os dois primeiros caracteres deste campo. Testei o filtro usado o comando like “where campo like ‘AA%'” e “where substr(campo,1,2) = ‘AA'”.

Sempre soube que a utilizacao de uma funcao sobre um campo indexado anulava o uso do indice, entao no caso acima o like seria mais rapido nao? Na pratica tive o resultado mais rapido (cerca de 80%) no uso do substr.

Por não ser um DBA e sim desenvolvedor não entendi este comportamento do banco. Alguem pode me explicar pq isto aconteceu?

Obrigado

[burgaParticipante]

Oi ELobao,

Não necessariamente o uso de um índice em uma consulta vai acarretar em uma performance melhor. Principalmente no seu caso, em que o número de registros retornados é bem maior do que 10% do total de registros da tabela. Isto se deve ao fato de que quando se utiliza um índice, o mesmo bloco de dados pode ser lido várias vezes, a cada busca por um registro diferente no bloco, gerando muita I/O de disco, enquanto que em um full table scan o bloco seria lido somente uma vez para todos os registros contidos nele.

É provável ainda que o Oracle não esteja utilizando o índice em nenhuma das duas ocasiões. Um índice existir em uma coluna não significa que ele vai ser utilizado, pra saber mais você precisaria analisar o explain plan das suas consultas.

Pra entender melhor, explicando bem grosseiramente, imagine que a sua consulta com like utilize o índice, então vai ocorrer que pra cada registro retirado, o Oracle consulta o índice, pega o endereço de memória do registro e faz a leitura do bloco onde o registro se encontra. Isto seria feito 800 mil vezes, uma pra cada registro retirado, ou seja, 800 mil leituras de blocos. Agora, imagine que um bloco contém 10 registros e você vai fazer um full table scan com substr, com o total de 5 milhões de registros, a tabela teria, hipotéticamente, 500 mil blocos, sendo que cada bloco seria lido somente uma vez. Isto dá uma diferença de 300 mil leituras de blocos lidos entre um index scan e um full table scan. Com isso você já pode tirar uma conclusão de que o uso de índice nem sempre vale a pena…

8)

[ELobaoParticipante]

Obrigado pela resposta Ricardo.

O que fiquei sem entender no meu caso é que olhei o plano de execução das duas consultas, não tenho os planos aqui, mas no caso da consulta com o like o índice é utilizado e no caso da consulta com o substr é feito o full scan e dos tempos foram muito diferentes…

[burgaParticipante]

Então ELobao,

Vejo que não consegui explicar direito, pra entender melhor este comportamento você teria que conhecer um pouco da arquitetura do banco, em especial a parte de armazenamento, e o comportamento d banco na utilização de index scans vs. full table scans.

É errado acreditar que a utilização de índice seja melhor do que não utilizá-lo. Cada caso é um caso…

Leia os links abaixo pra ver se esclarece melhor sua dúvida:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4433887271030

http://richardfoote.wordpress.com/2008/05/12/index-scan-or-full-table-scan-the-magic-number-magic-dance/

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968

Os links falam, com muito mais detalhes, o que eu tentei te explicar na mensagem anterior.

[ELobaoParticipante]

Certo Ricardo

Com sua ajuda e os links o assunto está assimilado.

Obrigado pelas dicas.

[Autor]

Posts