Por que o Oracle não está utilizando o Índice? Campos DATE + FUNCTIONS

O uso de funções em uma cláusula WHERE não irá utilizar índices, a menos que estes sejam Funtion Based indexes.

Nestes casos, é muito comum lembrar-se de funções como UPPER, LOWER, SUBSTR. O que vejo que é muito esquecido é o uso de funções inapropriadas para pesquisar campos do tipo DATE. E é muito, muito comum desenvolvedores utilizarem TO_CHAR e TRUNC para campos DATE.

No exemplo abaixo, tenho uma tabela com um índice em um campo DATE.

15:32:28 RICARDO@DEV> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

Elapsed: 00:00:02.43
15:32:35 RICARDO@DEV> CREATE INDEX IDX_T_CREATED ON T(CREATED);

Index created.

Elapsed: 00:00:00.11
15:33:11 RICARDO@DEV>

Ao pesquisar o campo do tipo DATE sem funções, o índice é utilizado.

15:33:11 RICARDO@DEV> SET AUTOTRACE TRACEONLY EXPLAIN
15:33:18 RICARDO@DEV> ALTER SESSION SET NLS_DATE_FORMAT = 'DDMMYYYY';

Session altered.

Elapsed: 00:00:00.00
15:33:31 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE CREATED = '18092018';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1253423996

-----------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		    |	 55 |	715 |	  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T 	    |	 55 |	715 |	  3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_T_CREATED |	 55 |	    |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED"=TO_DATE(' 2018-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15:33:38 RICARDO@DEV>

Mas o índice não é utilizado ao utilizar os tão comuns TO_CHAR e TRUNC.

15:33:38 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE TO_CHAR(CREATED,'DDMMYYYY') = '18092018';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   683 |  8879 |   376   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T	 |   683 |  8879 |   376   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_CHAR(INTERNAL_FUNCTION("CREATED"),'DDMMYYYY')='18092018
	      ')

15:33:52 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE TRUNC(CREATED,'DDMMYYYY') = '18092018';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   683 |  8879 |   377   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T	 |   683 |  8879 |   377   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"),'DDMMYYYY')=TO_DATE('
	      2018-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15:33:59 RICARDO@DEV> 

Se o SQL não pode ser modificado, pode ser criado um índice de função com o TO_CHAR ou TRUNC, que irá viabilizar a utilização de índices mesmo com estas funções.

15:34:31 RICARDO@DEV> CREATE INDEX IDX_T_CREATED_TO_CHAR ON T(TO_CHAR(CREATED,'DDMMYYYY'));

Index created.

Elapsed: 00:00:00.10
15:35:32 RICARDO@DEV> CREATE INDEX IDX_T_CREATED_TRUNC ON T(TRUNC(CREATED));

Index created.

Elapsed: 00:00:00.11
15:34:57 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE CREATED = '18092018';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1253423996

-----------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		    |	 55 |	715 |	  3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T 	    |	 55 |	715 |	  3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_T_CREATED |	 55 |	    |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED"=TO_DATE(' 2018-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

15:35:04 RICARDO@DEV> 
15:34:38 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE TO_CHAR(CREATED,'DDMMYYYY') = '18092018';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1432790342

-------------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name		    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |			    |	683 |  7513 |	 69   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T 		    |	683 |  7513 |	 69   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_T_CREATED_TO_CHAR |	273 |	    |	 63   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TO_CHAR(INTERNAL_FUNCTION("CREATED"),'DDMMYYYY')='18092018')

15:38:01 RICARDO@DEV> SELECT OBJECT_ID FROM T WHERE TRUNC(CREATED) = '18092018';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2560210846

-----------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name		  | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |			  |   683 |  9562 |    66   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T 		  |   683 |  9562 |    66   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | IDX_T_CREATED_TRUNC |   273 |	  |    60   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2018-09-18 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss'))

15:39:23 RICARDO@DEV> 

A solução preferencial é mudar o SQL, para evitar o índice, que trará um custo maior para as gravações. Mas muitas vezes não temos esta opção.

5 comments

  1. Boa tarde Portilho,
    Excelente post. Fiz uma simulação aqui e forçando o to_char (created,’DDMMYYYY’)=’19092018′) ele usou o INDEX FAST FULL SCAN.

  2. Interessante,
    Select count(*) forçado o to_char na cláusula ele usa INDEX FAST FULL SCAN
    select object_id forçando o to_char ele faz o Table access full.

    Excelente post Portilho..
    Luiz Marinho

    1. Sim, para contar quantas linhas tem com o valor solicitado na coluna indexada, “basta” ele ler o índice todo. Mas bem pior que o INDEX RANGE SCAN.
      Mas a coluna OBJECT_ID não está no índice, aí ele tem que acessar a tabela. Como já vai acessar a tabela inteira, não precisa do índice.

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.