Lendas de Oracle Tuning: busca por NULL não utiliza índice

Não é bem assim.: uma busca direta por campos NULL não utilizará o índice, mas uma contagem dos valores NULL conseguirá fazer um RANGE SCAN – não despreze a criação de índices para otimizar SQLs que utilizam NULL no WHERE.

SQL> CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(100));

Tabela criada.

SQL> CREATE INDEX T1_IDX_C1 ON T1(C1);

Indice criado.

SQL> CREATE INDEX T1_IDX_C2 ON T1(C2);

Indice criado.

SQL> INSERT INTO T1 SELECT OBJECT_ID, OBJECT_TYPE FROM ALL_OBJECTS;

64824 linhas criadas.

SQL> COMMIT;

Commit concluido.

SQL> UPDATE T1 SET C2 = NULL WHERE C2 = 'TABLE';

1592 linhas atualizadas.

SQL> COMMIT;

Commit concluido.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT C1 FROM T1 WHERE C2 IS NULL;

Plano de Execuc?o
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1518 | 98670 |    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1518 | 98670 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C2" IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT C2 FROM T1 WHERE C2 IS NULL;

Plano de Execuc?o
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1518 | 78936 |    68   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1518 | 78936 |    68   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("C2" IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT COUNT(C1) FROM T1 WHERE C2 IS NULL;

Plano de Execuc?o
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    65 |    68   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    65 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  1518 | 98670 |    68   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("C2" IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> SELECT COUNT(C2) FROM T1 WHERE C2 IS NULL;

Plano de Execuc?o
----------------------------------------------------------
Plan hash value: 2910198670

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    52 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    52 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_IDX_C2 |  1518 | 78936 |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access("C2" IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>