Lendas de Índices: Cláusulas de Negação

Há uma lenda de que se você utiliza cláusulas de negação (<>, !=, NOT IN, NOT EXISTS) em um SQL, este não se beneficiará de índices.

Esta afirmação está incorreta, especialmente se o indice evitar o acesso à tabela, como no método de acesso Fast Index Scan. Para testar primeiramente vamos criar uma rápida tabela de teste, com cerca de 90.000 linhas, e um índice.

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> CREATE INDEX IDX_T ON T(OBJECT_TYPE);

Index created.

SQL> SELECT COUNT(OBJECT_TYPE) FROM T;

COUNT(OBJECT_TYPE)
------------------
             89727

SQL> 

Agora, os planos de execução da seleção de uma quase toda a tabela, de cerca de metade, e até de uma pequena parte, utilizando cláusulas de negação.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'TABLE';

COUNT(OBJECT_TYPE)
------------------
             87308


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T | 88554 |  1124K|    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
             52429


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T | 50496 |   641K|    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE NOT IN ('SYNONYM', 'JAVA CLASS', 'VIEW', 'INDEX');

COUNT(OBJECT_TYPE)
------------------
             11448


Execution Plan
----------------------------------------------------------
Plan hash value: 2371838348

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13 |    72   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T |  7296 | 94848 |    72   (5)| 00:00:01 |
-------------------------------------------------------------------------------

SQL>

Ou seja, o índice é plenamente utilizado, na operação Index Fast Full Scan (onde não há o acesso à tabela, somente ao índice).

Agora os mesmos SQLs, mas sem o índice. Comparem o custo, de 72 para 416, e a troca de operação para Full Table Scan.

SQL> DROP INDEX IDX_T;

Index dropped.

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'TABLE';

COUNT(OBJECT_TYPE)
------------------
             87308


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 88554 |  1124K|   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE != 'SYNONYM';

COUNT(OBJECT_TYPE)
------------------
             52429


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 50496 |   641K|   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> SELECT COUNT(OBJECT_TYPE) FROM T WHERE OBJECT_TYPE NOT IN ('SYNONYM', 'JAVA CLASS', 'VIEW', 'INDEX');

COUNT(OBJECT_TYPE)
------------------
             11448


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   416   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  7296 | 94848 |   416   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL>

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.