Por que o operador NOT EQUAL prejudica performance?

Operator Not Equal

As instruções SQL que utilizam NOT EQUAL na cláusula WHERE em base de dados Oracle não utilizam índice no plano de execução para acessar as linhas da tabela, em algumas situações essa limitação do Otimizador pode prejudicar o desempenho das instruções SQL. Nesse artigo vamos entender porque essa limitação existe e verificar quais alternativas podemos utilizar em nossa instrução SQL para evitar que ela prejudique a performance.

 

Como trabalha o Otimizador

O Otimizador (CBO) é um código muito complexo que tem de lidar com inúmeros cenários diferentes para determinar o plano de execução mais eficiente possível.
 
O objetivo principal do Otimizador é determinar um plano de execução que consiga processar a instrução SQL o mais rápido possível, porem o tempo que ele gasta para determinar esse plano esta incluso no tempo total que a instrução SQL vai levar para realizar a tarefa solicitada ao Banco de dados, logo esse trabalho tem que ser realizado num intervalo de tempo muito pequeno, caso contrário ele pode ser o responsável pelo tempo insatisfatório para execução da instrução SQL.

Para realizar esta tarefa complexa com extrema precisão e rapidez o Otimizador utiliza vários tipos de atalhos e suposições. No entanto, esses atalhos às vezes podem ser problemáticos se não forem reconhecidos e tratados de forma adequada.

Um desses pequenos atalhos digno de nota é a forma como o Otimizador trata os operadores NOT EQUAL (e NOT IN).

Normalmente, quando temos uma situação em que dizemos que queremos o que não seja igual a alguma coisa especifica (NOT EQUAL), estamos sugerindo basicamente que estamos interessado em tudo exceto aquela coisa especifica.

Por exemplo quando temos uma condição como:

WHERE frutas <> ‘LARANJA’

Essa condição nos diz que estamos interessados em todas as frutas existentes na tabela exceto a LARANJA.

Numa situação como essa, em que estamos interessados na maioria dos registros de uma tabela, sabemos que o Otimizador do Oracle prefere fazer o acesso a tabela através de uma operação FULL TABLE SCAN, pois ela é mais eficiente do que utilizar um índice para retornar a maioria dos registros da tabela.

Porem existem situações em que utilizamos o operador NOT EQUAL e a quantidade de linhas retornada é muito pequena considerando a quantidade total de registros da tabela, nesses casos a utilização de um índice seria mais eficiente.

Simulando uma consulta com NOT EQUAL

A seguir vamos criar uma tabela com índice e coletar as estatísticas, na sequência fazer uma consulta nesta tabela utilizando o operador NOT EQUAL para verificar se o Otimizador vai utilizar o índice para acessar a tabela.

Vamos criar uma tabela com 3 valores distintos (P, T e F) sendo que um deles (P) tem uma quantidade de registros muito superior aos outros dois:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

1 row selected.

SQL >
SQL >
SQL > create table dbtw01 (id , result, text )
  2      as
  3      select rownum, decode (mod(rownum, 30000), 0, 'F', 1, 'T',  'P'), 'DBTimeWizard - Performance and Tuning'
  4        from dual connect by level <= 100000;

Table created.

SQL >
SQL > create index dbtw01_idx on dbtw01(result);

Index created.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

SQL >
SQL >
SQL > SELECT result , count(*)
  2    FROM dbtw01
  3   GROUP BY result;

R   COUNT(*)
- ----------
P      99993
T          4
F          3

3 rows selected.

SQL >

 

Vamos executar uma consulta onde vamos selecionar todos os registros diferentes de “P”:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > select /* tst101 */ *
  2    from dbtw01
  3   where result != 'P';

        ID R TEXT
---------- - -------------------------------------
         1 T DBTimeWizard - Performance and Tuning
     30000 F DBTimeWizard - Performance and Tuning
     30001 T DBTimeWizard - Performance and Tuning
     60000 F DBTimeWizard - Performance and Tuning
     60001 T DBTimeWizard - Performance and Tuning
     90000 F DBTimeWizard - Performance and Tuning
     90001 T DBTimeWizard - Performance and Tuning

7 rows selected.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dqak0zs4mj0w2            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst101 */ *   from dbtw01  where result != :"SYS_B_0"

Plan hash value: 658621498

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |      7 |00:00:00.01 |     699 |
|*  1 |  TABLE ACCESS FULL| DBTW01 |      1 |      7 |      7 |00:00:00.01 |     699 |
--------------------------------------------------------------------------------------

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

   1 - filter("RESULT"<>:SYS_B_0)


18 rows selected.

SQL >

 

Verificamos que apesar da consultar retornar somente 7 linhas o Otimizador escolheu fazer uma operação de FULL TABLE SCAN para acessar essas linhas na tabela.

Contornando a limitação do operador NOT EQUAL

Agora vamos reescrever a cláusula WHERE de outra forma, mas como na consulta anterior, indicando ao Otimizador que queremos valores diferentes de “P”:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SQL > select /* tst102 */ *
  2    from dbtw01
  3   where result > 'P'
  4      or result < 'P';

        ID R TEXT
---------- - -------------------------------------
     30000 F DBTimeWizard - Performance and Tuning
     60000 F DBTimeWizard - Performance and Tuning
     90000 F DBTimeWizard - Performance and Tuning
         1 T DBTimeWizard - Performance and Tuning
     30001 T DBTimeWizard - Performance and Tuning
     60001 T DBTimeWizard - Performance and Tuning
     90001 T DBTimeWizard - Performance and Tuning

7 rows selected.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
4uf7c3a4vdmmc            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst102 */ *   from dbtw01  where result > :"SYS_B_0"     or
result < :"SYS_B_1"

Plan hash value: 3833081860

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      7 |00:00:00.01 |      12 |
|   1 |  CONCATENATION               |            |      1 |        |      7 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DBTW01     |      1 |      3 |      3 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN          | DBTW01_IDX |      1 |      3 |      3 |00:00:00.01 |       3 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DBTW01     |      1 |      3 |      4 |00:00:00.01 |       6 |
|*  5 |    INDEX RANGE SCAN          | DBTW01_IDX |      1 |      4 |      4 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("RESULT"<:SYS_B_1)
   5 - access("RESULT">:SYS_B_0)
       filter(LNNVL("RESULT"<:SYS_B_1))


25 rows selected.

SQL >

 

Reescrevendo a consulta com os operadores “>” “OR” “<” verificamos que o Otimizador calculou que seria mais eficiente utilizar índice, nesse caso ele utilizou as estatísticas existentes na base sobre a tabela pois no código do Otimizador não existe atalhos para esses tipos de operadores. Se observarmos a quantidade de Buffers lidos na primeira consulta (699) e compararmos com a quantidade de Buffers lidos na consulta acima (12), podemos afirmar que essa consulta que utilizou índice é muito mais eficiente que a primeira.

Segunda alternativa ao operador NOT EQUAL

Outra alternativa para reescrever a cláusula WHERE seria utilizar o operador IN ():

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SQL > select /* tst103 */ *
  2    from dbtw01
  3   where result IN ('F','T');

        ID R TEXT
---------- - -------------------------------------
     30000 F DBTimeWizard - Performance and Tuning
     60000 F DBTimeWizard - Performance and Tuning
     90000 F DBTimeWizard - Performance and Tuning
         1 T DBTimeWizard - Performance and Tuning
     30001 T DBTimeWizard - Performance and Tuning
     60001 T DBTimeWizard - Performance and Tuning
     90001 T DBTimeWizard - Performance and Tuning

7 rows selected.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst103%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
b11d2xwtzxjjw            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst103 */ *   from dbtw01  where result IN
(:"SYS_B_0",:"SYS_B_1")

Plan hash value: 2152084041

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      7 |00:00:00.01 |      12 |
|   1 |  INLIST ITERATOR             |            |      1 |        |      7 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DBTW01     |      2 |      6 |      7 |00:00:00.01 |      12 |
|*  3 |    INDEX RANGE SCAN          | DBTW01_IDX |      2 |      6 |      7 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------

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

   3 - access(("RESULT"=:SYS_B_0 OR "RESULT"=:SYS_B_1))


21 rows selected.

SQL >

 

Verificando o resultado na log acima notamos que o Otimizador utilizou o índice novamente pois eliminamos a limitação NOT EQUAL ao reescrever a consulta utilizando o operador IN ().

Terceira alternativa ao operador NOT EQUAL

Outra alternativa que temos para reescrever a cláusula WHERE seria utilizar o UNION ALL:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
SQL > select /* tst104 */ *
  2    from dbtw01
  3   where result > 'P'
  4  union all
  5  select *
  6    from dbtw01
  7   where result < 'P';

        ID R TEXT
---------- - -------------------------------------
         1 T DBTimeWizard - Performance and Tuning
     30001 T DBTimeWizard - Performance and Tuning
     60001 T DBTimeWizard - Performance and Tuning
     90001 T DBTimeWizard - Performance and Tuning
     30000 F DBTimeWizard - Performance and Tuning
     60000 F DBTimeWizard - Performance and Tuning
     90000 F DBTimeWizard - Performance and Tuning

7 rows selected.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst104%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
3rtvk926fj5zu            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst104 */ *   from dbtw01  where result > :"SYS_B_0" union
all select *   from dbtw01  where result < :"SYS_B_1"

Plan hash value: 2290229173

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |      7 |00:00:00.01 |      12 |
|   1 |  UNION-ALL                   |            |      1 |        |      7 |00:00:00.01 |      12 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DBTW01     |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN          | DBTW01_IDX |      1 |      4 |      4 |00:00:00.01 |       3 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DBTW01     |      1 |      3 |      3 |00:00:00.01 |       5 |
|*  5 |    INDEX RANGE SCAN          | DBTW01_IDX |      1 |      3 |      3 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("RESULT">:SYS_B_0)
   5 - access("RESULT"<:SYS_B_1)


24 rows selected.

SQL >

 

Novamente vamos obter o mesmo resultado com um plano de execução eficiente utilizando o índice.

Conclusão

Devido a limitação de tempo para determinar o plano de execução de uma instrução SQL, o Otimizador recorre a algumas regras pré-definidas que não são verdadeiras em todas as situações, essa limitação leva o Otimizador em alguns momentos a produzir planos de execução ineficientes, conhecer quais são esses atalhos e suposições é muito importante pois podemos avaliar se eles são validos ou não nas instruções SQL das aplicações e quando identificamos que eles não são validos podemos buscar alternativas de código SQL para contornar essas limitações.

Referências

http://richardfoote.wordpress.com/2008/08/13/indexes-and-not-equal-not-now-john/

 

Mídia social

 

Deixe uma resposta

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