{"id":289,"date":"2016-02-07T21:53:51","date_gmt":"2016-02-07T23:53:51","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=289"},"modified":"2019-11-11T19:01:49","modified_gmt":"2019-11-11T22:01:49","slug":"por-que-minha-consulta-nao-esta-usando-o-indice","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/por-que-minha-consulta-nao-esta-usando-o-indice\/","title":{"rendered":"Por que minha consulta n\u00e3o esta usando o \u00edndice?"},"content":{"rendered":"

Existem muitas raz\u00f5es pelas quais o Otimizador do Oracle Database escolhe n\u00e3o utilizar um \u00edndice de uma tabela, algumas s\u00e3o bem conhecidas outras nem tanto, neste artigo vamos analisar uma consulta que aparentemente deveria utilizar um \u00edndice porem o Otimizador resolve fazer um “FULL TABLE SCAN” na tabela, esse caso especifico \u00e9 bem comum e muitas vezes perdemos muito tempo para identificar a sua causa.<\/span><\/p>\n

Nesta consulta tudo parece indicar que a utiliza\u00e7\u00e3o do \u00edndice seria uma boa escolha para o Otimizador, ela possui um filtro na cl\u00e1usula WHERE cuja coluna da tabela possui um \u00edndice, esse por sua vez tem bom valor de “DENSITY<\/a><\/strong>” pr\u00f3xima de zero (0.001605136), no filtro da consulta n\u00e3o foi especificado uma fun\u00e7\u00e3o, porem o Otimizador escolhe um plano que n\u00e3o utiliza o \u00edndice, o que esta impedindo o Otimizador de utilizar o \u00edndice?<\/span><\/p>\n

Descri\u00e7\u00e3o da simula\u00e7\u00e3o<\/span><\/h2>\n

Para analisar este caso vamos fazer duas simula\u00e7\u00f5es utilizando o “SAMPLE SCHEMA SH<\/a><\/strong>” para isso vamos criar um \u00edndice que seria utilizado pela consulta, pois originalmente o “SCHEMA SH” n\u00e3o possui esse \u00edndice, na primeira simula\u00e7\u00e3o vamos constatar que o Otimizador n\u00e3o vai utilizar o \u00edndice, vamos fazer uma an\u00e1lise do plano e identificar a causa que esta impedindo o Otimizador de utilizar o \u00edndice e na sequ\u00eancia executar a segunda simula\u00e7\u00e3o onde o Otimizador vai utilizar o \u00edndice.<\/span><\/p>\n

Caso tenha alguma d\u00favida na leitura do plano de execu\u00e7\u00e3o gerado pelo pacote DBMS_XPLAN consulte a s\u00e9rie de artigos que vai ajuda-lo a interpretar essas informa\u00e7\u00f5es:<\/span><\/p>\n

1. Como verificar a ordem que as opera\u00e7\u00f5es s\u00e3o realizadas<\/a><\/strong><\/span>
\n2. Como interpretar os valores estat\u00edsticos estimados<\/a><\/strong><\/span>
\n3. Como interpretar os valores estat\u00edsticos coletados durante a execu\u00e7\u00e3o<\/a><\/strong><\/span>
\n4. Como interpretar as se\u00e7\u00f5es n\u00e3o estat\u00edsticas do plano de execu\u00e7\u00e3o<\/a><\/strong><\/span><\/p>\n

Criando o \u00edndice<\/span><\/h2>\n

O primeiro passo em nossa simula\u00e7\u00e3o \u00e9 a cria\u00e7\u00e3o do \u00edndice da coluna “CUST_POSTAL_CODE” na tabela “CUSTOMERS” que ser\u00e1 utilizado na consulta.<\/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
<\/div><\/td>
sh@<\/span>LAB11 ><\/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> Production
\n
\nsh@<\/span>LAB11 ><\/span> create index CUSTOMERS_POSTAL_CODE_BIX on CUSTOMERS(<\/span>CUST_POSTAL_CODE)<\/span>;<\/span>
\n
\n\u00edndice criado.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span> select index_name,<\/span>
\n  2<\/span>         num_rows,<\/span>
\n  3<\/span>         distinct_keys,<\/span>
\n  4<\/span>         visibility,<\/span>
\n  5<\/span>         to_char(<\/span>last_analyzed,<\/span> ' dd\/MON\/yyyy'<\/span>)<\/span> last_analyzed
\n  6<\/span>    from user_indexes
\n  7<\/span>   where table_name =<\/span> 'CUSTOMERS'<\/span>
\n  8<\/span>   order by 1<\/span>;<\/span>
\n
\nINDEX_NAME                       NUM_ROWS DISTINCT_KEYS VISIBILIT LAST_ANALYZE
\n------------------------------<\/span> ----------<\/span> -------------<\/span> ---------<\/span> ------------<\/span>
\nCUSTOMERS_GENDER_BIX                    5<\/span>             2<\/span> VISIBLE    25<\/span>\/<\/span>AGO\/<\/span>2013<\/span>
\nCUSTOMERS_MARITAL_BIX                  18<\/span>            11<\/span> VISIBLE    25<\/span>\/<\/span>AGO\/<\/span>2013<\/span>
\nCUSTOMERS_PK                        55500<\/span>         55500<\/span> VISIBLE    25<\/span>\/<\/span>AGO\/<\/span>2013<\/span>
\nCUSTOMERS_POSTAL_CODE_BIX           55500<\/span>           623<\/span> VISIBLE    02<\/span>\/<\/span>FEV\/<\/span>2016<\/span>
\nCUSTOMERS_YOB_BIX                      75<\/span>            75<\/span> VISIBLE    25<\/span>\/<\/span>AGO\/<\/span>2013<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consulta que n\u00e3o utiliza o \u00edndice<\/span><\/h2>\n

Agora vamos executar a nossa consulta que supostamente deveria utilizar o \u00edndice criado, mas que por algum motivo n\u00e3o vai utiliza-lo.<\/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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> VARIABLE SYS_B_1     number;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> EXEC<\/span><\/a>    :<\/span>SYS_B_1     :=<\/span> 59411<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> select \/* tst101 *\/<\/span> CUST_ID,<\/span> CUST_FIRST_NAME,<\/span> CUST_LAST_NAME
\n  2<\/span>    from customers
\n  3<\/span>   where CUST_POSTAL_CODE =<\/span> :<\/span>SYS_B_1;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n     44116<\/span> Abner                Robbinette
\n     44117<\/span> Abraham              Sadworth
\n     49102<\/span> Bailey               Thompson
\n     49147<\/span> Barnaby              Malone
\n     49114<\/span> Baird                Rogers
\n     49125<\/span> Baldwin              Ball
\n     49136<\/span> Barlow               Charron
\n     .....<\/span>
\n     .....<\/span>
\n     .....<\/span>
\n    103296<\/span> Trinity              Moy
\n
\n320<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/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>
\n488qzbmpb9c1v            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/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> CUST_ID,<\/span> CUST_FIRST_NAME,<\/span> CUST_LAST_NAME   from
\ncustomers  where CUST_POSTAL_CODE =<\/span> :<\/span>SYS_B_1
\n
\nPlan hash<\/span><\/a> value:<\/span> 2008213504<\/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> Reads  |<\/span>
\n--------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>           |<\/span>      1<\/span> |<\/span>        |<\/span>    320<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    1478<\/span> |<\/span>   1454<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> CUSTOMERS |<\/span>      1<\/span> |<\/span>      1<\/span> |<\/span>    320<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.02<\/span> |<\/span>    1478<\/span> |<\/span>   1454<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>TO_NUMBER(<\/span>"CUST_POSTAL_CODE"<\/span>)<\/span>=:<\/span>SYS_B_1)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> @<\/span>desc CUSTOMERS
\n Nome                                Nulo?    Tipo
\n -----------------------------------<\/span> --------<\/span> ------------------------<\/span>
\n CUST_ID                             NOT NULL<\/span> NUMBER
\n CUST_FIRST_NAME                     NOT NULL<\/span> VARCHAR2(<\/span>20<\/span>)<\/span>
\n CUST_LAST_NAME                      NOT NULL<\/span> VARCHAR2(<\/span>40<\/span>)<\/span>
\n CUST_GENDER                         NOT NULL<\/span> CHAR(<\/span>1<\/span>)<\/span>
\n CUST_YEAR_OF_BIRTH                  NOT NULL<\/span> NUMBER(<\/span>4<\/span>)<\/span>
\n CUST_MARITAL_STATUS                          VARCHAR2(<\/span>20<\/span>)<\/span>
\n CUST_STREET_ADDRESS                 NOT NULL<\/span> VARCHAR2(<\/span>40<\/span>)<\/span>
\n CUST_POSTAL_CODE                    NOT NULL<\/span> VARCHAR2(<\/span>10<\/span>)<\/span>
\n CUST_CITY                           NOT NULL<\/span> VARCHAR2(<\/span>30<\/span>)<\/span>
\n CUST_CITY_ID                        NOT NULL<\/span> NUMBER
\n CUST_STATE_PROVINCE                 NOT NULL<\/span> VARCHAR2(<\/span>40<\/span>)<\/span>
\n CUST_STATE_PROVINCE_ID              NOT NULL<\/span> NUMBER
\n COUNTRY_ID                          NOT NULL<\/span> NUMBER
\n CUST_MAIN_PHONE_NUMBER              NOT NULL<\/span> VARCHAR2(<\/span>25<\/span>)<\/span>
\n CUST_INCOME_LEVEL                            VARCHAR2(<\/span>30<\/span>)<\/span>
\n CUST_CREDIT_LIMIT                            NUMBER
\n CUST_EMAIL                                   VARCHAR2(<\/span>30<\/span>)<\/span>
\n CUST_TOTAL                          NOT NULL<\/span> VARCHAR2(<\/span>14<\/span>)<\/span>
\n CUST_TOTAL_ID                       NOT NULL<\/span> NUMBER
\n CUST_SRC_ID                                  NUMBER
\n CUST_EFF_FROM                                DATE<\/span><\/a>
\n CUST_EFF_TO                                  DATE<\/span><\/a>
\n CUST_VALID                                   VARCHAR2(<\/span>1<\/span>)<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima, observamos que o Otimizador utilizou a opera\u00e7\u00e3o “TABLE ACCESS FULL” para acessar os registros da tabela “CUSTOMERS”, logo abaixo temos a se\u00e7\u00e3o “Predicate Information”, nela podemos observar que o Otimizador utilizou implicitamente a fun\u00e7\u00e3o “TO_NUMBER” para filtra os registros.<\/span><\/p>\n

Por que o Otimizador utilizou a fun\u00e7\u00e3o “TO_NUMBER” na coluna “CUST_POSTAL_CODE” inviabilizando a utiliza\u00e7\u00e3o do \u00edndice?<\/span><\/p>\n

A resposta est\u00e1 na defini\u00e7\u00e3o da bind variable “:SYS_B_1” no inicio da simula\u00e7\u00e3o, a defini\u00e7\u00e3o dessa vari\u00e1vel \u00e9 num\u00e9rica, porem se verificarmos na descri\u00e7\u00e3o dos tipos de colunas da tabela, a coluna “CUST_POSTAL_CODE” \u00e9 um “VARCHAR2(10)”, portanto para manter a compatibilidade de tipo na compara\u00e7\u00e3o da igualdade o Otimizador utilizou a fun\u00e7\u00e3o “TO_NUMBER” para converter o conteudo da coluna “CUST_POSTAL_CODE” para num\u00e9rico o que permitir\u00e1 a compara\u00e7\u00e3o com a “bind variable” que tamb\u00e9m \u00e9 num\u00e9rica.<\/span><\/p>\n

Consulta que utiliza o \u00edndice<\/span><\/h2>\n

Na sequ\u00eancia vamos executar a consulta novamente s\u00f3 que desta vez vamos alterar a “bind variable” para o tipo “VARCHAR2” que \u00e9 o mesmo tipo da coluna “CUST_POSTAL_CODE” na tabela “CUSTOMERS” e verificar se o Otimizador vai alterar o plano de execu\u00e7\u00e3o para utilizar o \u00edndice criado.<\/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
72
73
74
75
76
77
78
<\/div><\/td>
sh@<\/span>LAB11 ><\/span> ALTER SESSION SET statistics_level=<\/span>ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> VARIABLE SYS_B_1     varchar2(<\/span>5<\/span>)<\/span>;<\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> EXEC<\/span><\/a>    :<\/span>SYS_B_1     :=<\/span> '59411'<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> select \/* tst102 *\/<\/span> CUST_ID,<\/span> CUST_FIRST_NAME,<\/span> CUST_LAST_NAME
\n  2<\/span>    from customers
\n  3<\/span>   where CUST_POSTAL_CODE =<\/span> :<\/span>SYS_B_1;<\/span>
\n
\n   CUST_ID CUST_FIRST_NAME      CUST_LAST_NAME
\n----------<\/span> --------------------<\/span> ----------------------------------------<\/span>
\n     44116<\/span> Abner                Robbinette
\n     44117<\/span> Abraham              Sadworth
\n     49102<\/span> Bailey               Thompson
\n     49147<\/span> Barnaby              Malone
\n     49114<\/span> Baird                Rogers
\n     49125<\/span> Baldwin              Ball
\n     .....<\/span>
\n     .....<\/span>
\n     .....<\/span>
\n    103296<\/span> Trinity              Moy
\n
\n320<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span> column sql_id new_value m_sql_id
\nsh@<\/span>LAB11 ><\/span> column child_number new_value m_child_no
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/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>
\ndz494vttb896c            0<\/span>
\n
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/span>
\nsh@<\/span>LAB11 ><\/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>
\n
\nEXPLAINED SQL STATEMENT:<\/span>
\n------------------------<\/span>
\nselect \/* tst102 *\/<\/span> CUST_ID,<\/span> CUST_FIRST_NAME,<\/span> CUST_LAST_NAME   from
\ncustomers  where CUST_POSTAL_CODE =<\/span> :<\/span>SYS_B_1
\n
\nPlan hash<\/span><\/a> value:<\/span> 1026787069<\/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> Reads  |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT            |<\/span>                           |<\/span>      1<\/span> |<\/span>        |<\/span>    320<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     328<\/span> |<\/span>    653<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID|<\/span> CUSTOMERS                 |<\/span>      1<\/span> |<\/span>     89<\/span> |<\/span>    320<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     328<\/span> |<\/span>    653<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN          |<\/span> CUSTOMERS_POSTAL_CODE_BIX |<\/span>      1<\/span> |<\/span>     89<\/span> |<\/span>    320<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      25<\/span> |<\/span>      7<\/span> |<\/span>
\n----------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"CUST_POSTAL_CODE"<\/span>=:<\/span>SYS_B_1)<\/span>
\n
\n
\n20<\/span> linhas selecionadas.<\/span>
\n
\nsh@<\/span>LAB11 ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o acima observamos que o \u00edndice “CUSTOMERS_POSTAL_CODE_BIX” foi utilizado e como resultado tivemos uma melhoria de desempenho da ordem de 78% (Redu\u00e7\u00e3o de leitura de Buffers de 1478 para 328), na se\u00e7\u00e3o “Predicate Information” verificamos que o Otimizador n\u00e3o utiliza fun\u00e7\u00e3o para convers\u00e3o de tipo de dados pois a “bind variable” informada \u00e9 do mesmo tipo que a coluna no filtro da cl\u00e1usula WHERE.<\/span><\/p>\n

 <\/p>\n

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

Apesar de toda a evolu\u00e7\u00e3o do Otimizador do banco de dados Oracle (CBO), existem situa\u00e7\u00f5es em que ele consegue evitar que uma instru\u00e7\u00e3o SQL apresente um erro, mas n\u00e3o \u00e9 capaz de criar um plano de execu\u00e7\u00e3o com bom desempenho. No nosso exemplo simulamos uma situa\u00e7\u00e3o em que a aplica\u00e7\u00e3o estava passando para a consulta um tipo de dado incompat\u00edvel com o tipo de dado na coluna da tabela e para resolver o problema simplesmente alteramos o tipo de dado enviado pela aplica\u00e7\u00e3o, mas essa solu\u00e7\u00e3o nem sempre \u00e9 vi\u00e1vel pois as vezes a aplica\u00e7\u00e3o pertence a terceiros e sua corre\u00e7\u00e3o pode levar meses ou anos, nesse caso uma alternativa seria criar um “function-based index”.<\/span><\/p>\n

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

http:\/\/intermediatesql.com\/oracle\/how-oracle-implicit-type-conversion-works-part-1\/<\/a><\/strong><\/p>\n

http:\/\/intermediatesql.com\/oracle\/how-oracle-implicit-type-conversion-works-part-2\/<\/a><\/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":"

Existem muitas raz\u00f5es pelas quais o Otimizador do Oracle Database escolhe n\u00e3o utilizar um \u00edndice de uma tabela, algumas s\u00e3o bem conhecidas outras nem tanto, neste artigo vamos analisar uma consulta que aparentemente deveria utilizar um \u00edndice porem o Otimizador resolve fazer um “FULL TABLE SCAN” na tabela, esse caso especifico \u00e9 bem comum e muitas vezes perdemos muito tempo<\/p>\n","protected":false},"author":2,"featured_media":290,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,62],"tags":[65,6,42,84,36,85,86,82,10,29,11,83,45,61],"class_list":["post-289","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-execution-plan","category-indice","tag-ajuste-de-desempenho","tag-cbo","tag-cost-base-optimizer","tag-density","tag-execution-plan","tag-index-b-tree","tag-indice-b-tree","tag-optimizer","tag-otimizador","tag-performance-tuning","tag-plano-de-execucao","tag-response-time","tag-sql-statement-analysis","tag-tempo-de-reposta"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/289","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=289"}],"version-history":[{"count":14,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/289\/revisions"}],"predecessor-version":[{"id":2105,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/289\/revisions\/2105"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/290"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=289"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}