Oracle Function Based Indexes

A partir do Oracle versão 10 é possível criar índices no banco de dados baseado em funções, para agilizar consultas como esta:

select codigo, nome
from produto
where substr(nome,3,1) = 'i';

O resultado desta consulta pode ser a seguinte:

    CODIGO NOME
---------- --------------------------------------------------
         2 feijão

Analisando o plano de acesso do Oracle para a resolução desta consulta, observamos que o banco teve que percorrer a tabela toda para alcançar o resultado, fazendo um FULL-TABLE-SCAN, conforme podemos ver a serguir:

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    10 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PRODUTO |     1 |    10 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Obviamente que em tabelas pequenas isso não é problema. Em alguns casos a leitura completa da tabela também pode ser a melhor forma de retornar os dados. Mas pensando em uma consulta que retorna poucos registros de uma tabela grande, a opção por um índice para acessar esta tabela seria mais viável.

No Oracle é possível criar um índice que se baseie nesta função, conforme o exemplo a seguir:

create index fbi_produto on produto (SUBSTR("NOME",3,1));

Executando novamente a consulta, percebemos que o plano de execução já mudou, passando a utilizar o índice:

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PRODUTO     |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FBI_PRODUTO |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

A partir do Progress 10 o Dataserver for Oracle passou a se utilizar deste poderoso recurso do Oracle para resolver a questão das consultas case-sensitive. Até a versão 9 do Progress apenas era possível esta compatibilidade com a replicação de algumas colunas no lado Oracle que armazenavam o conteúdo da coluna original em maiúsculo.

Agora utilizar essas colunas shadow é opcional, o Progress passará a utilizar índices criados no Oracle baseados na função UPPER, que transformam o valor original das colunas em seu correspondente em maiúsculo.