{"id":575,"date":"2016-04-17T22:24:45","date_gmt":"2016-04-18T01:24:45","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=575"},"modified":"2019-11-11T18:58:36","modified_gmt":"2019-11-11T21:58:36","slug":"por-que-o-operador-not-equal-prejudica-performance","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-o-operador-not-equal-prejudica-performance\/","title":{"rendered":"Por que o operador NOT EQUAL prejudica performance?"},"content":{"rendered":"

As instru\u00e7\u00f5es SQL que utilizam NOT EQUAL na cl\u00e1usula WHERE em base de dados Oracle n\u00e3o utilizam \u00edndice no plano de execu\u00e7\u00e3o para acessar as linhas da tabela, em algumas situa\u00e7\u00f5es essa limita\u00e7\u00e3o do Otimizador pode prejudicar o desempenho das instru\u00e7\u00f5es SQL. Nesse artigo vamos entender porque essa limita\u00e7\u00e3o existe e verificar quais alternativas podemos utilizar em nossa instru\u00e7\u00e3o SQL para evitar que ela prejudique a performance.<\/span><\/p>\n

 <\/p>\n

Como trabalha o Otimizador<\/span><\/h2>\n

O Otimizador (CBO) \u00e9 um c\u00f3digo muito complexo que tem de lidar com in\u00fameros cen\u00e1rios diferentes para determinar o plano de execu\u00e7\u00e3o mais eficiente poss\u00edvel. <\/span>
\n 
\nO objetivo principal do Otimizador \u00e9 determinar um plano de execu\u00e7\u00e3o que consiga processar a instru\u00e7\u00e3o SQL o mais r\u00e1pido poss\u00edvel, porem o tempo que ele gasta para determinar esse plano esta incluso no tempo total que a instru\u00e7\u00e3o 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\u00e1rio ele pode ser o respons\u00e1vel pelo tempo insatisfat\u00f3rio para execu\u00e7\u00e3o da instru\u00e7\u00e3o SQL.<\/span><\/p>\n

Para realizar esta tarefa complexa com extrema precis\u00e3o e rapidez o Otimizador utiliza v\u00e1rios tipos de atalhos e suposi\u00e7\u00f5es. No entanto, esses atalhos \u00e0s vezes podem ser problem\u00e1ticos se n\u00e3o forem reconhecidos e tratados de forma adequada.<\/span><\/p>\n

Um desses pequenos atalhos digno de nota \u00e9 a forma como o Otimizador trata os operadores NOT EQUAL (e NOT IN).<\/span><\/p>\n

Normalmente, quando temos uma situa\u00e7\u00e3o em que dizemos que queremos o que n\u00e3o seja igual a alguma coisa especifica (NOT EQUAL), estamos sugerindo basicamente que estamos interessado em tudo exceto aquela coisa especifica.<\/span><\/p>\n

Por exemplo quando temos uma condi\u00e7\u00e3o como:<\/span><\/p>\n

WHERE frutas <> ‘LARANJA’<\/span><\/p>\n

Essa condi\u00e7\u00e3o nos diz que estamos interessados em todas as frutas existentes na tabela exceto a LARANJA.<\/span><\/p>\n

Numa situa\u00e7\u00e3o 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\u00e9s de uma opera\u00e7\u00e3o FULL TABLE SCAN, pois ela \u00e9 mais eficiente do que utilizar um \u00edndice para retornar a maioria dos registros da tabela.<\/span><\/p>\n

Porem existem situa\u00e7\u00f5es em que utilizamos o operador NOT EQUAL e a quantidade de linhas retornada \u00e9 muito pequena considerando a quantidade total de registros da tabela, nesses casos a utiliza\u00e7\u00e3o de um \u00edndice seria mais eficiente.<\/span><\/p>\n

Simulando uma consulta com NOT EQUAL<\/span><\/h2>\n

A seguir vamos criar uma tabela com \u00edndice e coletar as estat\u00edsticas, na sequ\u00eancia fazer uma consulta nesta tabela utilizando o operador NOT EQUAL para verificar se o Otimizador vai utilizar o \u00edndice para acessar a tabela.<\/span><\/p>\n

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:<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> create table dbtw01 (<\/span>id ,<\/span> result,<\/span> text )<\/span>
\n  2<\/span>      as<\/span>
\n  3<\/span>      select rownum,<\/span> decode (<\/span>mod(<\/span>rownum,<\/span> 30000<\/span>)<\/span>,<\/span> 0<\/span>,<\/span> 'F'<\/span>,<\/span> 1<\/span>,<\/span> 'T'<\/span>,<\/span>  'P'<\/span>)<\/span>,<\/span> 'DBTimeWizard - Performance and Tuning'<\/span>
\n  4<\/span>        from dual connect by level <=<\/span> 100000<\/span>;<\/span>
\n
\nTable created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw01_idx on dbtw01(<\/span>result)<\/span>;<\/span>
\n
\nIndex created.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> exec<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW01'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> estimate_percent=><\/span> null<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE 254'<\/span>)<\/span>;<\/span>
\n
\nPL\/<\/span>SQL procedure successfully completed.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT result ,<\/span> count<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n  2<\/span>    FROM dbtw01
\n  3<\/span>   GROUP BY result;<\/span>
\n
\nR   COUNT<\/span><\/a>(<\/span>*<\/span>)<\/span>
\n-<\/span> ----------<\/span>
\nP      99993<\/span>
\nT          4<\/span>
\nF          3<\/span>
\n
\n3<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Vamos executar uma consulta onde vamos selecionar todos os registros diferentes de “P”:<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSession altered.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select \/* tst101 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where result !=<\/span> 'P'<\/span>;<\/span>
\n
\n        ID R TEXT
\n----------<\/span> -<\/span> -------------------------------------<\/span>
\n         1<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     30000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     30001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     60000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     60001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     90000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     90001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n
\n7<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst101%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\ndqak0zs4mj0w2            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst101 *\/<\/span> *<\/span>   from dbtw01  where result !=<\/span> :<\/span>"SYS_B_0"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 658621498<\/span>
\n
\n--------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name   |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n--------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>        |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     699<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW01 |<\/span>      1<\/span> |<\/span>      7<\/span> |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     699<\/span> |<\/span>
\n--------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>"RESULT"<\/span><>:<\/span>SYS_B_0)<\/span>
\n
\n
\n18<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificamos que apesar da consultar retornar somente 7 linhas o Otimizador escolheu fazer uma opera\u00e7\u00e3o de FULL TABLE SCAN para acessar essas linhas na tabela.<\/span><\/p>\n

Contornando a limita\u00e7\u00e3o do operador NOT EQUAL<\/span><\/h2>\n

Agora vamos reescrever a cl\u00e1usula WHERE de outra forma, mas como na consulta anterior, indicando ao Otimizador que queremos valores diferentes de “P”:<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> select \/* tst102 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where result ><\/span> 'P'<\/span>
\n  4<\/span>      or result <<\/span> 'P'<\/span>;<\/span>
\n
\n        ID R TEXT
\n----------<\/span> -<\/span> -------------------------------------<\/span>
\n     30000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     60000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     90000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n         1<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     30001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     60001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     90001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n
\n7<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst102%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n4uf7c3a4vdmmc            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n----------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst102 *\/<\/span> *<\/span>   from dbtw01  where result ><\/span> :<\/span>"SYS_B_0"<\/span>     or
\nresult <<\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3833081860<\/span>
\n
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  CONCATENATION               |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>      3<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       6<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"RESULT"<\/span><:<\/span>SYS_B_1)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"RESULT"<\/span>>:<\/span>SYS_B_0)<\/span>
\n       filter(<\/span>LNNVL(<\/span>"RESULT"<\/span><:<\/span>SYS_B_1)<\/span>)<\/span>
\n
\n
\n25<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Reescrevendo a consulta com os operadores “>” “OR” “<” verificamos que o Otimizador calculou que seria mais eficiente utilizar \u00edndice, nesse caso ele utilizou as estat\u00edsticas\u00a0existentes\u00a0na base\u00a0sobre a tabela pois no c\u00f3digo do Otimizador n\u00e3o 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\u00a0consulta acima (12), podemos afirmar que essa consulta que utilizou \u00edndice \u00e9 muito mais eficiente que a primeira.<\/span><\/p>\n

Segunda alternativa ao operador NOT EQUAL<\/span><\/h2>\n

Outra alternativa para reescrever a cl\u00e1usula WHERE seria utilizar o operador IN ():<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> select \/* tst103 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where result IN (<\/span>'F'<\/span>,<\/span>'T'<\/span>)<\/span>;<\/span>
\n
\n        ID R TEXT
\n----------<\/span> -<\/span> -------------------------------------<\/span>
\n     30000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     60000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     90000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n         1<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     30001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     60001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     90001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n
\n7<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst103%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nb11d2xwtzxjjw            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst103 *\/<\/span> *<\/span>   from dbtw01  where result IN
\n(<\/span>:<\/span>"SYS_B_0"<\/span>,:<\/span>"SYS_B_1"<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2152084041<\/span>
\n
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  INLIST ITERATOR             |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      2<\/span> |<\/span>      6<\/span> |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      2<\/span> |<\/span>      6<\/span> |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>(<\/span>"RESULT"<\/span>=:<\/span>SYS_B_0 OR "RESULT"<\/span>=:<\/span>SYS_B_1)<\/span>)<\/span>
\n
\n
\n21<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o resultado na log acima notamos que o Otimizador utilizou o \u00edndice novamente pois eliminamos a limita\u00e7\u00e3o NOT EQUAL ao reescrever a consulta utilizando o operador IN ().<\/span><\/p>\n

Terceira alternativa ao operador NOT EQUAL<\/span><\/h2>\n

Outra alternativa que temos para reescrever a cl\u00e1usula WHERE seria utilizar o UNION ALL:<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> select \/* tst104 *\/<\/span> *<\/span>
\n  2<\/span>    from dbtw01
\n  3<\/span>   where result ><\/span> 'P'<\/span>
\n  4<\/span>  union all
\n  5<\/span>  select *<\/span>
\n  6<\/span>    from dbtw01
\n  7<\/span>   where result <<\/span> 'P'<\/span>;<\/span>
\n
\n        ID R TEXT
\n----------<\/span> -<\/span> -------------------------------------<\/span>
\n         1<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     30001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     60001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     90001<\/span> T DBTimeWizard -<\/span> Performance and Tuning
\n     30000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     60000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n     90000<\/span> F DBTimeWizard -<\/span> Performance and Tuning
\n
\n7<\/span> rows selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%tst104%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n3rtvk926fj5zu            0<\/span>
\n
\n1<\/span> row selected.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'basic iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------<\/span>
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst104 *\/<\/span> *<\/span>   from dbtw01  where result ><\/span> :<\/span>"SYS_B_0"<\/span> union
\nall select *<\/span>   from dbtw01  where result <<\/span> :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2290229173<\/span>
\n
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name       |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT             |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  UNION-<\/span>ALL                   |<\/span>            |<\/span>      1<\/span> |<\/span>        |<\/span>      7<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      12<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       7<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>      4<\/span> |<\/span>      4<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       3<\/span> |<\/span>
\n|<\/span>   4<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW01     |<\/span>      1<\/span> |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n|*<\/span>  5<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW01_IDX |<\/span>      1<\/span> |<\/span>      3<\/span> |<\/span>      3<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       2<\/span> |<\/span>
\n-----------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   3<\/span> -<\/span> access(<\/span>"RESULT"<\/span>>:<\/span>SYS_B_0)<\/span>
\n   5<\/span> -<\/span> access(<\/span>"RESULT"<\/span><:<\/span>SYS_B_1)<\/span>
\n
\n
\n24<\/span> rows selected.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Novamente vamos obter o mesmo resultado com um plano de execu\u00e7\u00e3o eficiente utilizando o \u00edndice.<\/span><\/p>\n

Conclus\u00e3o<\/span><\/h2>\n

Devido a limita\u00e7\u00e3o de tempo para determinar o plano de execu\u00e7\u00e3o de uma instru\u00e7\u00e3o SQL, o Otimizador recorre a algumas regras pr\u00e9-definidas que n\u00e3o s\u00e3o verdadeiras em todas as situa\u00e7\u00f5es, essa limita\u00e7\u00e3o leva o Otimizador em alguns momentos a produzir planos de execu\u00e7\u00e3o ineficientes, conhecer quais s\u00e3o esses atalhos e suposi\u00e7\u00f5es \u00e9 muito importante pois podemos avaliar se eles s\u00e3o validos ou n\u00e3o nas instru\u00e7\u00f5es SQL das aplica\u00e7\u00f5es e quando identificamos que eles n\u00e3o s\u00e3o validos podemos buscar alternativas de c\u00f3digo SQL para contornar essas limita\u00e7\u00f5es.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

http:\/\/richardfoote.wordpress.com\/2008\/08\/13\/indexes-and-not-equal-not-now-john\/<\/a><\/span><\/strong><\/p>\n

 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

As instru\u00e7\u00f5es SQL que utilizam NOT EQUAL na cl\u00e1usula WHERE em base de dados Oracle n\u00e3o utilizam \u00edndice no plano de execu\u00e7\u00e3o para acessar as linhas da tabela, em algumas situa\u00e7\u00f5es essa limita\u00e7\u00e3o do Otimizador pode prejudicar o desempenho das instru\u00e7\u00f5es SQL. Nesse artigo vamos entender porque essa limita\u00e7\u00e3o existe e verificar quais alternativas podemos utilizar em nossa instru\u00e7\u00e3o SQL<\/p>\n","protected":false},"author":2,"featured_media":580,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[65,121,82,10,29,61],"class_list":["post-575","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-ajuste-de-desempenho","tag-not-equal","tag-optimizer","tag-otimizador","tag-performance-tuning","tag-tempo-de-reposta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/575","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=575"}],"version-history":[{"count":15,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions"}],"predecessor-version":[{"id":2099,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/575\/revisions\/2099"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/580"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=575"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=575"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=575"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}