Início / dba / Perguntas / 224277
Accepted
Hybris95
Asked: 2018-12-07 03:16:55 +0800 CST

O Oracle Optimizer se recusa a usar meu índice

  • 772

Eu tenho uma consulta relativamente simples que SELECT dados em uma tabela.

SELECT l.columnRecovered FROM schema.TABLE l
WHERE (column1 = :a or (:a is null AND column1 is null))
AND (column2 = :b or (:b is null AND column2 is null))
AND (column3 = :c or (:c is null AND column3 is null));

Isso gera o seguinte plano:

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   309 (100)|          |
|*  1 |  TABLE ACCESS FULL| TABLE     |     1 |    56 |   309   (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((("COLUMN2"=:3 OR ("COLUMN2" IS NULL AND :4 IS NULL)) AND
              ("COLUMN1"=:1 OR ("COLUMN1" IS NULL AND :2 IS NULL)) AND ((:6 IS NULL
              AND "COLUMN3" IS NULL) OR "COLUMN3"=:5)))

Nesta tabela, tenho um UNIQUE INDEX que contém todas essas 3 colunas.
Mas o otimizador prefere fazer um FULL SCAN na mesa ao invés de usar o INDEX.

Onde as coisas começam a ficar estranhas, é quando tento essa consulta sem meus vínculos, mas substituindo os valores diretamente na consulta.
Claro que o SQL_ID muda, mas dessa vez o otimizador decide usar o INDEX e vai de 1104 para 6 (o que é claro uma ótima otimização).

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    56 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE         |     1 |    56 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | TABLE_IDX     |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("COLUMN3"='DATA3' AND "COLUMN2"='DATA2' AND "COLUMN1"='DATA1')

As estatísticas estão atualizadas e não há histogramas nesta tabela.

Alguma maneira de fazer o otimizador usar esse índice sem modificar a consulta?

Eu acho que o otimizador pensa que a possibilidade de ter 3 NULLs pode acontecer então isso desabilita ele de usar o índice certo?

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

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

   1 - filter("COLUMN2" IS NULL AND "COLUMN1" IS NULL AND "COLUMN3" IS
              NULL)
oracle index

1 respostas

  1. Best Answer

    O otimizador não usa seu índice, não porque não queira, mas porque não pode. Para uma consulta como essa, o índice não cobre todos os resultados possíveis.

    Quando :a, :b, :csão todos NULL e você está procurando por linhas em que column1, column2, column3são todas NULL, essas linhas não são indexadas por um (column1, column2, column3)índice de árvore b, portanto, ele não pode ser usado para recuperar as linhas correspondentes.

    Adicionar NOT NULLrestrições pode ajudar, mas se você tiver valores NULL, é claro que isso não é possível (e estou assumindo que você tem valores NULL, caso contrário, por que você construiria uma consulta como essa).

    Você também pode tentar criar um índice que inclua um valor constante, para que mesmo essas linhas sejam indexadas, onde as colunas indexadas são NULL. Então, em vez de um (column1, column2, column3)índice, você pode tentar criar um (column1, column2, column3, 'A')índice.

    A melhor solução na minha opinião é, no entanto, ter consultas diferentes geradas sempre que uma variável de ligação não recebeu um valor. Consultas como as acima são resultados da tentativa de lidar com vários casos com uma única consulta. Isso pode parecer elegante e compacto, e sim, eu sei, gerar consultas diferentes com base na presença de parâmetros pode ser complicado, mas já vi esse tipo de preguiça causando problemas graves de desempenho inúmeras vezes.

    Sim, a Oracle tem alguns truques, como otimização NVL, expansão OR, para lidar com esses predicados. Esses funcionam perfeitamente em Powerpoint e materiais de treinamento, com exemplos simples, mas na realidade, com consultas complexas nem sempre podem ser usados.

    • 3

relate perguntas