Como o Oracle recupera os dados - Parte 2

O último artigo da série aborda o acesso aos dados através dos índices. Cada variante desta forma de acesso será apresentada, de forma que os desenvolvedores possam compreender suas vantagens.

por Vinícius Ronconi

O artigo anterior apresentou as formas de acesso aos dados e explicou os critérios utilizados pelo Oracle para efetuar a escolha da forma de acesso. As opções foram detalhadas, buscando mostrar ao leitor quais as vantagens e possíveis problemas que podem surgir em virtude da utilização de cada opção. A única exceção foi a busca pelo índice, que foi apenas apresentada, sem o devido detalhamento.

Neste artigo a busca pelos registros utilizando índices será detalhada, apresentando as diferentes maneiras de aproveitar a existência destas estruturas para recuperar os dados com melhor desempenho.

Nesta forma de acesso, o servidor de banco de dados verifica se os campos utilizados para filtrar os registros fazem parte de algum índice da tabela acessada. Caso um ou mais campos das restrições da consulta estejam presentes em um índice, então o Oracle utiliza esta estrutura para encontrar os registros que satisfazem à condição. Se a consulta acessar apenas os campos do índice, então o Oracle é capaz de recuperar as informações diretamente desta estrutura sem acessar a tabela.

Os próximos artigos desta série explicarão detalhadamente a estrutura de um índice. Neste momento é suficiente que o leitor saiba que o índice é composto pelos valores indexados da tabela e, também, pelo ROWID de cada registro. Desta forma, caso o Oracle necessite recuperar informações adicionais na tabela ele poderá fazer isto diretamente utilizando o ROWID ou através do “cluster scan”.

1. SELECT * 2. FROM employees 3. WHERE NVL(first_name, ‘Vinicius Ronconi’) = ‘Vinicius Ronconi’

Listagem 1 – Comando que utiliza função em uma coluna do índice

O Oracle não será capaz de utilizar a busca pelo índice quando alguma das situações abaixo relacionadas ocorrer:

• As restrições que se referem às colunas do índice possuem funções. Por exemplo, o Oracle desprezará o índice na Listagem 1, uma vez que a função NVL foi utilizada. Deve-se prestar atenção à utilização de funções implícitas, como a conversão de um valor numérico para alfanumérico. A Listagem 2 ilustra esta situação. Neste exemplo, o campo “employee_id” é numérico, mas a restrição foi informada com a utilização de apóstrofos;

1. SELECT * 2. FROM employees 3. WHERE employee_id = ‘1’

Listagem 2 – Comando que utiliza função implícita

• As colunas do índice são comparadas através do operador “LIKE”, como no exemplo da Listagem 3.

1. SELECT * 2. FROM employees 3. WHERE first_name LIKE ‘Vinicius%’

Listagem 3 - Comando utilizando o operador LIKE

As formas de acesso aos dados através de índice são: index unique scan (índice único), index range scan (por escala), index range scan descending (por escala descendente), index skip scan (desprezando a coluna principal), index full scan (busca completa), fast full index scan (busca rápida completa), index join (junção de índices) e bitmap join (junção de mapas de bits).

Index unique scan

Esta busca é utilizada quando a chave primária, ou outro índice único, é capaz de filtrar os registros da tabela. Sempre que esta forma de acesso for utilizada existe a garantia de que no máximo um registro será recuperado do banco de dados.

Somente é possível utilizar esta busca quando todos os campos do índice estiverem presentes na condição e utilizarem o operador de igualdade (“=”).

O exemplo da Listagem 4 será resolvido utilizando a opção index unique scan, uma vez que a chave primária desta tabela é composta apenas pelo campo “employee_id” e foi utilizado o operador de igualdade. Se este índice fosse composto por outros campos, ou se algum outro operador fosse utilizado, então o comando seria resolvido utilizando outras formas de acesso, como, por exemplo, a index range scan.

1. SELECT * 2. FROM employees 3. WHERE employee_id = 1

Listagem 4 - Comando que informa todos os campos de um índice único

Index range scan

Esta é uma das formas de acesso mais comuns quando se utiliza a busca através de índices. Utilizada quando a coluna principal do índice foi informada, mas nem todos os demais campos foram restringidos com o operador de igualdade. Os demais campos podem utilizar outros operadores, ou mesmo nem estarem presentes nas restrições de consulta.

1. SELECT * 2. FROM job_history 3. WHERE start_date = TO_DATE(‘01/03/1997’, ‘dd/mm/yyyy’)

Listagem 5 - Consulta que não utiliza o primeiro campo de um índice

No exemplo da Listagem 5, os dados não são acessados através da opção index range scan, pois a coluna principal (employee_id) da chave primária não foi informada. Já o exemplo da Listagem 6 utilizará esta opção, pois o comando informou a coluna principal do índice. Se todos os campos que compõem o índice único fossem informados com o operador de igualdade, então a forma de acesso utilizada seria a opção index unique scan, já que seria retornado no máximo um registro para atender ao comando.

1. SELECT * 2. FROM job_history 3. WHERE employee_id = 1

Listagem 6 - Consulta utilizando index range scan

Caso o índice utilizado para acessar os dados de uma tabela não possua a opção “índice único”, então a forma de acesso utilizada será index range scan, mesmo que todos os campos sejam informados nas condições de consulta com o operador de igualdade.

Sempre que os operadores de escala (“>”, “>=”, “<”, “<=” ou BETWEEN) forem utilizados em pelo menos um dos campos do índice o Oracle utilizará a opção index range scan para recuperar os dados para resolver o comando.

Os dados são retornados pela ordem ascendente dos campos do índice. Se houver mais de um registro com valores idênticos o desempate realizar-se-á pelo ROWID. Caso os registros precisem estar numa ordem específica, a cláusula ORDER BY deve ser utilizada na consulta. Se o índice satisfizer a cláusula ORDER BY então o otimizador despreza a cláusula e utiliza a ordem do índice, oferecendo, assim, ganhos de desempenho.

Index range scan descending

Esta forma de acesso é similar à forma anterior, porém será útil quando o usuário solicitar que os dados sejam organizados de forma descendente. Por exemplo, para organizar os dados por uma data, de forma que os registros mais recentes sejam exibidos primeiramente.

Para que esta forma de acesso seja escolhida, o comando deve filtrar os registros cujo valor seja igual ou menor ao parâmetro informado pelo usuário. Esta opção também será utilizada quando a cláusula ORDER BY possuir a opção “DESC”, que indica que os dados serão organizados de maneira decrescente.

1. SELECT * 2. FROM job_history 3. WHERE end_date <= TO_DATE(‘01/01/1981’, ‘dd/mm/yyyy’) 4. ORDER BY end_date DESC

Listagem 7 - Consulta ordenando de forma descendente

A consulta realizada na Listagem 7 utiliza a opção index range scan descending para recuperar os registros do banco de dados, uma vez que a opção “DESC” foi informada.

Index skip scan

Disponível a partir do Oracle 9i, esta forma de acesso permite que um índice seja utilizado mesmo que as primeiras colunas de um índice não tenham sido informadas. Esta forma de acesso pode ser útil quando houver poucos valores diferentes para os primeiros campos de um índice e muitos valores diferenciados nas demais colunas. Quando utiliza esta forma de acesso, o Oracle cria diversos sub-índices, de acordo com a quantidade de valores distintos para as primeiras colunas.

A Listagem 5, que, conforme citado, não utiliza o index range scan, poderá resolvido utilizando a opção index skip scan, uma vez que a coluna principal deste índice não foi informada. A chave primária da tabela “job_history” é composta pelos campos “employee_id” e “start_date”, nesta ordem. Sem a utilização desta forma de acesso, a melhor forma para resolver a consulta seria através da leitura seqüencial dos registros.
A partir da implantação da busca pelo índice desprezando as colunas principais, o Oracle pode dividir este índice em vários sub-índices, de acordo com a quantidade de valores distintos para o primeiro campo do índice. Caso existam três empregados diferentes, então, o Oracle irá criar três sub-índices e efetuar a procura dos registros dentro de cada divisão. Desta maneira, não acontece uma busca completa no índice para cada valor distinto da coluna não informada, mas apenas uma busca dentro de cada subdivisão.

Index Full Scan

O index full scan estará disponível se pelo menos uma das restrições indicadas na cláusula WHERE se referenciarem a pelo menos um dos campos do índice. Caso não exista cláusula WHERE, o Oracle também utilizará esta forma de acesso quando todas as colunas referenciadas na cláusula SELECT estiverem presentes em um índice que contenha pelo menos uma coluna que não permita valores nulos.

Para recuperar as informações na mesma ordem dos campos do índice a cláusula ORDER BY pode ser dispensada, uma vez que os dados serão recuperados utilizando a ordem do índice. Assim como no acesso através da opção index range scan, mesmo que a cláusula ORDER BY esteja presente, o Oracle se encarrega de desprezá-la.

Index Fast Full Scan

O index fast full scan é uma alternativa ao index full scan, sendo utilizada quando o índice possui todas as colunas necessárias para a consulta e existe pelo menos uma das colunas do índice com a restrição “NOT NULL”. Além disto, a análise do índice precisa ter sido realizada. Sem a análise do índice, o otimizador poderá optar por não utilizar o índice, devido à ausência de informações.

Ao contrário do index full scan, esta forma de acesso utiliza apenas os blocos do índice, sem a necessidade de realizar o acesso aos blocos da tabela. Desta forma, os dados estarão organizados pelo ROWID de cada registro, não sendo possível, portanto, desprezar a cláusula ORDER BY para definir a ordem dos dados.
Como o acesso será restrito aos blocos do índice, o Oracle realiza a leitura de vários blocos numa única operação, oferecendo um desempenho ainda melhor. Já na opção full scan, apenas um bloco é lido a cada leitura realizada no disco.

Index join

Esta forma de acesso surgiu a partir da versão 8i, mas esta versão não trazia esta forma de acesso habilitada na instalação padrão do servidor de banco de dados. Apenas a partir da versão 9i que esta maneira de acessar os dados passou a estar habilitada na instalação padrão do Oracle.

Esta forma de acesso é escolhida pelo Oracle quando todas as colunas referenciadas em uma consulta estão presentes em diferentes índices da tabela. Nesta situação, o Oracle carrega estes índices para a memória e realiza uma operação hash para combinar os índices e encontrar apenas os registros que satisfazem à consulta. Desta maneira, o Oracle dispensa o acesso aos blocos da tabela, sendo capaz de recuperar as informações utilizando apenas os blocos dos índices. Caso o usuário necessite obter os dados em uma ordem específica, a cláusula ORDER BY deverá ser utilizada.

Bitmap join

Esta forma de acesso utiliza um bitmap (mapa de bits) como valor chave para a identificação dos registros e uma função para realizar o mapeamento, convertendo as posições dos bits em ROWID’s dos registros nas tabelas. Esta forma de acesso somente está disponível a partir da versão Oracle 9i Enterprise Edition.
Um índice bitmap pode realizar a fusão de índices utilizados na cláusula WHERE de uma consulta, utilizando operações “booleanas” para resolver os operadores AND e OR. Este tipo de índice é amplamente utilizado em sistemas de dataware house, e será detalhado nos próximos artigos desta série.

Conclusão

Este artigo apresentou a busca dos dados utilizando os índices existentes nas tabelas. Mesmo com a utilização dos índices existem diferentes formas de acesso, que serão utilizadas de acordo com os dados fornecidos na consulta. Os próximos artigos desta série apresentarão aos leitores outro fator que poderá influenciar o desempenho de um comando, que são as formas de relacionar as tabelas.

Referências

• http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm
• http://www.akadia.com/services/ora_interpreting_explain_plan.html
• http://www.oracle.com/technology//products/oracle9i/daily/apr22.html