Como o Oracle recupera os dados - Parte 1

O primeiro artigo desta série apresenta as formas utilizadas pelo Oracle para recuperar os dados. Cada forma de acesso será abordada, apresentando suas vantagens e situações em que o Oracle a utilizará.

por Vinícius Ronconi

Esta série de artigos se concentrará nas formas utilizadas pelo Oracle para recuperar os dados armazenados e produzir os resultados para o usuário. A escolha da forma de acesso adequada para cada situação pode trazer diferenças significativas no desempenho de um sistema. Imagine, por exemplo, que o usuário solicite a busca de informações numa tabela com alguns milhões de registros, e o banco de dados precise percorrer cada um destes registros para encontrar o conjunto que satisfaça às condições definidas. A utilização de uma forma de acesso inadequada poderá ser desastrosa, tornando o sistema inviável.

O Oracle oferece diferentes maneiras de acesso aos dados: leitura seqüencial (full table scan), busca pelo identificador do registro (ROWID scan), busca pelo índice (index scan), “cluster scan”, “hash scan” e busca por amostragem (sample table scan).

Para escolher a forma de acesso aos dados para resolver uma consulta, o Oracle primeiramente determina quais são as formas disponíveis para a operação. Ele realiza esta operação analisando as cláusulas FROM e WHERE do comando. Em seguida, o otimizador gera um conjunto de planos de execução utilizando as opções disponíveis e verifica qual é aquela que gera o menor custo para obter os resultados.

Alguns fatores podem influenciar na escolha, como as dicas (“hints”) informadas pelos desenvolvedores sobre como resolver um comando. Neste caso, o otimizador não gera o conjunto de planos de execução, mas apenas utiliza a dica dada pelo desenvolvedor. As dicas para o otimizador não fazem parte do escopo deste artigo.

Outra maneira pela qual o otimizador pode ser influenciado na escolha da forma de acesso é através das estatísticas de tabelas e índices. Elas armazenam informações como quantidade de registros, blocos e tamanho médio dos registros. Caso as estatísticas estejam desatualizadas, o otimizador buscará dados antigos, que não refletem a realidade de um objeto. Em conseqüência, o otimizador escolhe uma forma de acesso inadequada para a ocasião. Portanto, é importante que os desenvolvedores verifiquem com os administradores de banco de dados se as estatísticas são atualizadas periodicamente.

Informações sobre a leitura dos blocos

Antes de detalhar as formas de acesso, é importante explicar como o Oracle recupera as informações contidas no banco de dados. Os registros das tabelas são armazenados em unidades conhecidas como “blocos”, que possuem um tamanho fixo e são configurados pelo administrador de banco de dados. A quantidade de registros armazenados em um bloco é obtida através da fórmula (tamanho do bloco / tamanho dos registros).

Sempre que precisa recuperar os registros o Oracle carrega um bloco inteiro para a memória, e em seguida seleciona apenas os registros que lhe interessam. A vantagem desta abordagem é que reduz a quantidade de leituras feitas em disco, que é uma operação mais lenta do que o acesso às informações contidas na memória.

O Oracle mantém um histórico dos blocos que foram utilizados por uma tabela através de um recurso conhecido como “marca d’água” (high water mark). Esta marca indica o último bloco utilizado pela tabela para armazenar informações. Esta marca vai aumentando à medida que os dados são criados em uma tabela. Mesmo que os dados sejam excluídos, esta marca continua apontando para o último bloco utilizado.
Existem apenas duas maneiras de reduzir o valor desta marca: pelos comandos DROP (remove uma tabela) ou TRUNCATE (apaga todos os registros de uma tabela, permitindo a liberação dos blocos).

Enquanto estes comandos não são executados, o Oracle efetua a leitura de todos os blocos que já possuíram dados desta tabela no passado, mesmo que atualmente não possuam nenhum registro sequer. Isto torna a leitura seqüencial ainda mais lenta, uma vez que mesmo blocos sem qualquer informação precisam ser lidos.

Leitura Seqüencial

Nesta forma de acesso, o Oracle faz a leitura seqüencial de todos os registros de uma tabela, verificando um a um quais são aqueles que atendem às condições de consulta.

Esta forma de acesso pode ser útil quando a consulta retorna a maior parte dos registros de uma tabela. Isto acontece porque a leitura seqüencial permite que sejam realizadas leituras de vários blocos do Oracle numa única operação. Como citado anteriormente, reduzir a quantidade de acessos ao disco gera benefícios no desempenho, já que o acesso ao disco é uma operação lenta. Quando as leituras são realizadas através dos índices, o acesso ao disco é limitado ao tamanho do bloco.

Desta maneira, ao contrário do que pode parecer, a leitura seqüencial pode apresentar resultados melhores do que o acesso através de índices em situações específicas. O otimizador do Oracle poderá optar pela leitura seqüencial em algumas situações:

Ausência de índices

Caso o Oracle não encontre nenhum índice que possa ser utilizado para realizar o acesso aos dados, então a leitura é realizada de maneira seqüencial.

Se houver algum índice disponível, mas as condições definidas pelo usuário possuírem alguma função, então não será possível utilizar este índice, tornando necessário o acesso seqüencial aos dados. Por exemplo, se a tabela “employees” possuir um índice pela coluna “last_name”, e o usuário enviar o comando descrito na Listagem 1, o Oracle não será capaz de utilizar este índice. Neste caso, será feita uma leitura seqüencial de todos os registros da tabela “employees” para encontrar os registros de empregados cujo sobrenome possua “RONCONI”.

1. SELECT * 2. FROM employees 3. WHERE UPPER(last_name) LIKE ‘%RONCONI%’

Listagem 1 - Consulta por campo utilizando função

Acesso à grande parte dos dados

Caso o otimizador verifique a necessidade de acessar a maior parte dos blocos de uma tabela para produzir o resultado, então ele despreza os índices e realiza a leitura seqüencial. Como descrito anteriormente, isto acontece por que durante a leitura seqüencial o Oracle pode realizar acessos ao disco maiores numa única operação. A redução na quantidade de acessos ao disco melhora o desempenho das consultas.

Tabelas pequenas

Se uma tabela ocupar poucos blocos do Oracle, sendo possível recuperar todos estes blocos numa única operação de acesso ao disco, então o otimizador utiliza a leitura seqüencial para acessar os dados. Isto será mais rápido do que acessar o índice para encontrar os blocos necessários, e, somente então, efetuar a leitura em disco.

Busca pelo identificador do registro

A leitura seqüencial dos dados pode apresentar um alto custo para obter os registros que satisfazem uma condição, exceto nas condições específicas apresentadas. Por outro lado, a forma mais rápida para acessar um registro específico sempre será a busca através do identificador do registro.

Nesta forma de acesso, o ROWID do registro desejado é passado pronto para o Oracle. O ROWID especifica exatamente o local físico em que um registro se encontra. Ele indica o arquivo de dados e o bloco em que o registro se encontra. Além disto, também informa qual é a posição do registro dentro do bloco. Desta maneira, o Oracle é capaz de ir diretamente ao registro desejado, sem precisar fazer qualquer outro tipo de verificação.

Para acessar uma tabela pelo ROWID, o Oracle primeiramente obtém o ROWID dos registros selecionados através da cláusula WHERE do comando, ou pelo índice da tabela.

Por exemplo, na consulta descrita na Listagem 2 o Oracle primeiramente busca na tabela EMPLOYEES os funcionários que recebem o salário informado como parâmetro. Em seguida, o Oracle acessa o índice da tabela DEPARTMENTS para identificar o ROWID dos registros encontrados na tabela EMPLOYEES. Finalmente, o Oracle acessa a tabela DEPARTMENTS, buscando os registros diretamente pelo ROWID, trazendo apenas os blocos necessários para a memória.

1. SELECT d.department_name 2. FROM employees e, departments d 3. WHERE e.salary = 17000 4. AND e.department_id = d.department_id

Listagem 2 - Consulta utilizando o ROWID

Esta busca somente acontece se alguma das colunas acessadas não estiver presente no índice. Se todas as colunas acessadas estiverem presentes no índice, então o acesso pelo ROWID não se torna necessário.
Apesar de ser a forma mais rápida de acesso aos dados, não é recomendado que os desenvolvedores utilizem esta forma de acesso diretamente. O ROWID é uma informação interna do Oracle, podendo ser de modificado através de algumas operações de administração do banco de dados, como importação e exportação das tabelas.

Busca em Cluster

Esta forma de acesso somente se aplica às tabelas que compõem um cluster. O cluster é formado por um conjunto de tabelas que possuem dados em comum e que geralmente são utilizadas em conjunto. Para isto, as colunas em comum entre as tabelas são definidas como as chaves do cluster.

Quando esta chave é criada, um índice também será associado à estas colunas, permitindo que os dados de todas as tabelas sejam recuperados com a menor quantidade possível de acessos ao disco. A criação de clusters é transparente para usuários e aplicações, uma vez que o acesso às tabelas e aos dados continuará sendo realizado da mesma maneira.

Um exemplo de tabelas que poderiam compor um cluster são as tabelas necessárias para manter a nota fiscal de compra de mercadorias. Em geral, utiliza-se uma tabela para conter as informações gerais da nota, como seu número, data, o responsável pela compra, dentre outras informações. Em uma tabela separada, armazenam-se os itens desta nota fiscal, contendo a identificação dos produtos e suas respectivas quantidades. Neste caso, um cluster poderia ser criado contendo a identificação da nota fiscal como chave do cluster.

Para recuperar as informações de um índice cluster, o Oracle primeiramente busca no índice cluster o ROWID dos registros que satisfazem à condição. Possuindo o ROWID, o Oracle busca então nos blocos de dados das tabelas os registros necessários.

Busca Hash

Um hash cluster é uma alternativa para melhorar o desempenho na recuperação de dados em tabelas que não fazem parte de um cluster. Para isto, é necessário criar um hash cluster e carregar a tabela nesta área. O Oracle armazena, então, os dados da tabela nesta estrutura e recupera os dados de acordo com o resultado da função hash.

A vantagem da utilização desta estrutura é que todos os registros com o mesmo valor hash são armazenados em um mesmo bloco de dados, reduzindo, assim, a necessidade de acessos ao disco.

Para realizar a busca hash, o Oracle aplica uma função hash ao valor dos campos da chave do cluster e, em seguida, busca nos blocos de dados os registros que possuem o mesmo valor hash.

Busca por amostragem

Utilizado quando a cláusula FROM possui as opções “SAMPLE” ou “SAMPLE BLOCK”, esta forma de acesso recupera uma pequena amostra dos dados da tabela. Estas opções recuperam um percentual dos registros ou dos blocos de dados, respectivamente. A Listagem 3 exemplifica a cláusula SAMPLE, retornando 30% do empregados admitidos antes de 1950.

1. SELECT * 2. FROM employees e SAMPLE (30) 3. WHERE hire_date < TO_DATE(‘01/01/1998’, ‘dd/mm/yyyy’)

Listagem 3 - Consulta em uma amostra da tabela

Esta operação não será permitida quando a consulta efetuar uma junção entre duas ou mais tabelas. Esta restrição poderá ser contornada através da utilização do comando “CREATE TABLE AS SELECT”, para materializar a consulta desejada.

Conclusão

Este artigo explicou os critérios utilizados pelo Oracle para determinar qual será a forma de acessar os dados necessários para resolver uma consulta. Além disto, salientou a necessidade da atualização constante das estatísticas. As formas de acesso foram relacionadas e devidamente detalhadas, à exceção da busca pelo índice, que será o tema do próximo artigo da série.

Referências
• http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm
• http://www.ss64.com/orasyntax/4clusters.html