Evitando FULL TABLE SCANS quando usar IS NULL em cláusulas WHERE
Olá pessoal,
Neste post vou falar sobre um tuning simples, mas eficaz quando trabalhamos com valores nulos em filtros.
R. Uma tabela é um objeto do tipo SEGMENTO no banco de dados que armazena dados em forma de linhas e colunas, certo? Tipo uma planilha do excel:
O que é um índice?
R. Índice é outro objeto do tipo SEGMENTO que, uma vez criado em uma tabela por exemplo poderá ser utilizado para agilizar a pesquisa quando alguns critérios forem utilizados. Imagine um índice de um livro. Ele é feito para agilizar a pesquisa e você encontrar o assunto que está procurando de forma mais rápida. No banco de dados é similar, o índice é constituído basicamente do valor da coluna (ou colunas) indexada da tabela com um ponteiro que aponta para a linha (ou linhas) que possui aquele valor. Basicamente assim:
- Funções é um objeto de código de programação que retorna algum valor. Por exemplo: a função SUM retorna o somatório dos valores, a função AVG a média de valores, a função MIN retorna o valor mínimo de uma determinada coluna, a função UPPER transforma valores minúsculos em maiúsculos. Por exemplo:
O plano de execução gerado para a consulta acima realizaria um FULL TABLE SCAN (FTS) mesmo se o índice fosse criado (levariam aproximadamente 9 minutos de execução):
Quando você criou um índice para aquela coluna, todos os valores que não existiam (NULOS) também não existiam no índice. Então o Oracle não teria como usar o índice para saber quais seriam os valores NULOS.
Existe duas formas mais conhecidas de contornar (leia-se "workaround" ou "adaptações técnicas").
Existem vários tipos de índices que você pode criar para várias situações. Neste caso você pode criar um índice baseado em função (FUNCTION BASED INDEX) que irá gerar dados no índice de acordo com uma função, mesmo que os dados não estejam (ou não existam) na origem (coluna ou colunas). Se criarmos um índice desse tipo usando a função NVL, o valor NULO será substituído pelo valor arbitrário informado. Vamos explicar melhor. Veja primeiro o exemplo de uso da função NVL:
Nessa consulta, todos os alunos que não possuírem dados na coluna TELEFONE, ou seja, que não possuírem telefone cadastrado, ao invés de não aparecer valor, vai aparecer "SEM TELEFONE" no resultado da consulta:
Ou seja, a função NLV substituiu o valor NULO por um texto específico durante a execução. Claro que esse texto é apenas ilustrativo, ou seja, só aparece quando consultamos os dados. Não estamos alterando a tabela, apenas a forma de visualizar o resultado. Isso que o comando SELECT faz, consultas.
Agora vamos misturar a função com o índice criando um FUNCTION-BASED INDEX para usá-lo depois em uma consulta:
Esse índice será criado um pouco diferente do índice comum. Ao invés de não criar valores nulos, ele irá criar valores "0" onde existirem NULOS (valores arbitrários). Assim a consulta abaixo utilizará este índice ao invés de realizar um FTS:
Neste post vou falar sobre um tuning simples, mas eficaz quando trabalhamos com valores nulos em filtros.
Antes de tudo, contextualizando:
O que é uma tabela?R. Uma tabela é um objeto do tipo SEGMENTO no banco de dados que armazena dados em forma de linhas e colunas, certo? Tipo uma planilha do excel:
O que é um índice?
R. Índice é outro objeto do tipo SEGMENTO que, uma vez criado em uma tabela por exemplo poderá ser utilizado para agilizar a pesquisa quando alguns critérios forem utilizados. Imagine um índice de um livro. Ele é feito para agilizar a pesquisa e você encontrar o assunto que está procurando de forma mais rápida. No banco de dados é similar, o índice é constituído basicamente do valor da coluna (ou colunas) indexada da tabela com um ponteiro que aponta para a linha (ou linhas) que possui aquele valor. Basicamente assim:
- Funções é um objeto de código de programação que retorna algum valor. Por exemplo: a função SUM retorna o somatório dos valores, a função AVG a média de valores, a função MIN retorna o valor mínimo de uma determinada coluna, a função UPPER transforma valores minúsculos em maiúsculos. Por exemplo:
SELECT UPPER('texto') FROM DUAL;
-- Retorno da consulta usando a função:
TEXTO
Agora vamos pra questão em si.
Imagine agora uma tabela chamada TB_ALUNOS em que há uma coluna chamada TELEFONE. Nesta coluna TELEFONE são cadastrados os TELEFONES dos alunos, mas você percebe que nesta coluna são permitidos valores NULOS, ou seja, permite que nenhum telefone seja inserido. Além disso você cria um índice nesta coluna TELEFONE para agilizar pesquisas filtradas pelo telefone. Até aí tudo bem, mas diante disso você quer saber quais alunos NÃO possuem telefones cadastrados. O que você faz? Traduzindo para a linguagem SQL seria assim:SELECT * FROM tb_alunos WHERE telefone IS NULL;ou SELECT * FROM tb_alunos WHERE telefone='';- Exemplo de criação de um índice comum na coluna TELEFONE do exemplo:
CREATE INDEX idx_alunos_telefone ON tb_alunos(telefone);
O plano de execução gerado para a consulta acima realizaria um FULL TABLE SCAN (FTS) mesmo se o índice fosse criado (levariam aproximadamente 9 minutos de execução):
Execution PlanQuando usamos um filtro procurando por valores NULOS em uma tabela, o banco de dados executa um FTS (Full Table Scan) que é uma leitura COMPLETA da tabela por linhas que possuem valores NULOS, ou seja, alunos que não possuem telefones cadastrados. Como o valor NULO na verdade é a ausência de valor, ou seja, NADA foi gravado naquela coluna, então como indexar?
----------------------------------------------------------
Plan hash value: 2837633100
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57664 | 2027K| 44448 (1)| 00:08:54 |
|* 1 | TABLE ACCESS FULL| TB_ALUNOS | 57664 | 2027K| 44448 (1)| 00:08:54 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TELEFONE" IS NULL)
Quando você criou um índice para aquela coluna, todos os valores que não existiam (NULOS) também não existiam no índice. Então o Oracle não teria como usar o índice para saber quais seriam os valores NULOS.
Existe duas formas mais conhecidas de contornar (leia-se "workaround" ou "adaptações técnicas").
1. Criando um índice baseado em função (Function-Based Index):
Existem vários tipos de índices que você pode criar para várias situações. Neste caso você pode criar um índice baseado em função (FUNCTION BASED INDEX) que irá gerar dados no índice de acordo com uma função, mesmo que os dados não estejam (ou não existam) na origem (coluna ou colunas). Se criarmos um índice desse tipo usando a função NVL, o valor NULO será substituído pelo valor arbitrário informado. Vamos explicar melhor. Veja primeiro o exemplo de uso da função NVL:
SELECT nome,NVL(telefone,'SEM TELEFONE') FROM tb_alunos;
Nessa consulta, todos os alunos que não possuírem dados na coluna TELEFONE, ou seja, que não possuírem telefone cadastrado, ao invés de não aparecer valor, vai aparecer "SEM TELEFONE" no resultado da consulta:
NOME TELEFONE
--------- -------------
Fernando 98989898
Renato 78787878
Natália SEM TELEFONE
Hamilton 45454545
Ou seja, a função NLV substituiu o valor NULO por um texto específico durante a execução. Claro que esse texto é apenas ilustrativo, ou seja, só aparece quando consultamos os dados. Não estamos alterando a tabela, apenas a forma de visualizar o resultado. Isso que o comando SELECT faz, consultas.
Agora vamos misturar a função com o índice criando um FUNCTION-BASED INDEX para usá-lo depois em uma consulta:
CREATE INDEX idx_alunos_nvltelefone ON tb_alunos(NVL(telefone,0));
Esse índice será criado um pouco diferente do índice comum. Ao invés de não criar valores nulos, ele irá criar valores "0" onde existirem NULOS (valores arbitrários). Assim a consulta abaixo utilizará este índice ao invés de realizar um FTS:
SELECT * FROM tb_alunos WHERE NVL(telefone,0)=0;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 1376 | 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ALUNOS | 32 | 1376 | 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ALUNOS_NVLTELEFONE | 32 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("TELEFONE",0)=0)
2. Criando um índice composto (Composite Index) com valor arbitrário (Oracle 11g):
Um índice composto é quando criamos um índice formado por mais de uma coluna. No nosso caso, vamos especificar a primeira coluna TELEFONE e a segunda coluna um valor arbitrário "0". Veja o comando de criação por exemplo:CREATE INDEX idx_alunos_nometelefone ON tb_alunos(telefone,0);A coluna TELEFONE da tabela TB_ALUNOS foi concatenada (junta) a "0" no índice, possível na versão 11g. Dessa forma garantimos que sempre haverá valor indexado, pois os valores das colunas serão concatenadas. Onde for NULO na coluna TELEFONE, na outra coluna haverá "0", portanto há entrada no índice. Veja o exemplo da consulta:
SELECT * FROM tb_alunos WHERE telefone IS NULL;
Execution Plan
----------------------------------------------------------
Plan hash value: 1405752932
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57664 | 2027K| 27682 (1)| 00:05:33 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ALUNOS | 57664 | 2027K| 27682 (1)| 00:05:33 |
|* 2 | INDEX RANGE SCAN | IDX_ALUNOS_TELEFONENULL | 57664 | | 160 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TELEFONE" IS NULL)
Uma coisa tão simples, mas normalmente explicada de maneira confusa nos sites da internet. Sua explicação foi perfeita e me ajudou bastante, obrigado!
ResponderExcluir