Por que o Oracle ignora o INDEX e faz Full Table Scan?

Recentemente atendi um incidente crítico em que o cliente reportava que, após realizar uma atualização no sistema, uma transação ficou extremamente lenta. Ao analisar o caso, identifiquei que o Oracle estava realizando Full Table Scan (FTS) em uma consulta, mesmo com um INDEX criado corretamente na coluna utilizada no predicado.

Neste post, apresento uma simulação baseada no incidente real. Os exemplos aqui descritos foram criados exclusivamente para evidenciar o que de fato aconteceu em um ambiente produtivo e facilitar a compreensão do problema, sem reproduzir exatamente a consulta do cliente.

O Problema

SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL> VARIABLE SYS_BIN_1     NUMBER;
SQL> EXEC    :SYS_BIN_1     := 190518;

PL/SQL procedure successfully completed.

SQL>
SQL> SET FEEDBACK OFF;
SQL> select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1;

LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
       2000 40-5-12 Laogianggen                      190518       Beijing                                                  CN
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor(NULL,NULL,'advanced allstats -outline last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7urqspapjqh82, child number 0
-------------------------------------
select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1

Plan hash value: 2989070059

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS FULL| LOCATIONS |      1 |      1 |    48 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / "LOCATIONS"@"SEL$1"

Peeked Binds (identified by position):
--------------------------------------

   1 - (NUMBER): 190518

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

   1 - filter(TO_NUMBER("POSTAL_CODE")=:SYS_BIN_1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LOCATIONS"."LOCATION_ID"[NUMBER,22], "LOCATIONS"."STREET_ADDRESS"[VARCHAR2,40],
       "POSTAL_CODE"[VARCHAR2,12], "LOCATIONS"."CITY"[VARCHAR2,30], "LOCATIONS"."STATE_PROVINCE"[VARCHAR2,25],
       "LOCATIONS"."COUNTRY_ID"[CHARACTER,2]

Query Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]


SQL> SET ECHO OFF
SQL>
SQL> SELECT index_name, column_name, column_position
  2    FROM DBA_ind_columns
  3   WHERE table_name = 'LOCATIONS'
  4   AND TABLE_OWNER = 'HR'
  5  ORDER BY index_name, column_position;

Indice                                   Coluna                                   Posicao
---------------------------------------- ---------------------------------------- -------
IDX_LOCATIONS_POSTAL_CODE                POSTAL_CODE                                    1
LOC_CITY_IX                              CITY                                           1
LOC_COUNTRY_IX                           COUNTRY_ID                                     1
LOC_ID_PK                                LOCATION_ID                                    1
LOC_STATE_PROVINCE_IX                    STATE_PROVINCE                                 1

5 rows selected.

SQL>

Mesmo com o INDEX IDX_LOCATIONS_POSTAL_CODE criado na coluna POSTAL_CODE, o plano de execução gerado mostrou que o Oracle realizou um Full Table Scan:

Análise do Problema

Ao investigar o plano de execução, ficou claro que o Oracle estava ignorando o INDEX e realizando Full Table Scan devido à incompatibilidade de tipos de dados entre a coluna POSTAL_CODE e a variável bind :SYS_BIN_1. Nesse ponto já foi constatado que, durante o deploy, a equipe de desenvolvimento alterou o tipo da variável que era originalmente uma VARCHAR2 para NUMBER. Essa alteração foi a raiz do problema:

A coluna POSTAL_CODE é do tipo VARCHAR2.
A variável bind :SYS_BIN_1 passou a ser do tipo NUMBER.
O Oracle aplicou a função TO_NUMBER nos valores da coluna para realizar a comparação com a variável bind e isso impede o uso do INDEX, já que a conversão força a realizar a comparação linha a linha.

Solução 1: Ajustar a variável bind

A solução mais eficiente foi solicitar à equipe de desenvolvimento que ajustasse o tipo da variável bind para que fosse consistente com o tipo da coluna POSTAL_CODE. Isso eliminou a necessidade de conversão e permitiu que o INDEX fosse utilizado.

SQL> set lines 188  pages 300 VERIFY off FEEDBACK ON;
SQL> SET ECHO on TAB off;
SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL> VARIABLE SYS_BIN_1     VARCHAR2(12);
SQL> EXEC    :SYS_BIN_1     := '190518';

PL/SQL procedure successfully completed.

SQL>
SQL> SET FEEDBACK OFF;
SQL> select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1;

LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
       2000 40-5-12 Laogianggen                      190518       Beijing                                                  CN
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor(NULL,NULL,'advanced allstats -outline last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7urqspapjqh82, child number 0
-------------------------------------
select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1

Plan hash value: 1528426575

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                           |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS                 |      1 |      1 |    48 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      1 |
|*  2 |   INDEX RANGE SCAN                  | IDX_LOCATIONS_POSTAL_CODE |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / "LOCATIONS"@"SEL$1"
   2 - SEL$1 / "LOCATIONS"@"SEL$1"

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=873): '190518'

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

   2 - access("POSTAL_CODE"=:SYS_BIN_1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LOCATIONS"."LOCATION_ID"[NUMBER,22], "LOCATIONS"."STREET_ADDRESS"[VARCHAR2,40], "POSTAL_CODE"[VARCHAR2,12], "LOCATIONS"."CITY"[VARCHAR2,30],
       "LOCATIONS"."STATE_PROVINCE"[VARCHAR2,25], "LOCATIONS"."COUNTRY_ID"[CHARACTER,2]
   2 - "LOCATIONS".ROWID[ROWID,10], "POSTAL_CODE"[VARCHAR2,12]

Query Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]
  
  SQL>

Após o ajuste, o plano de execução indicou que o INDEX foi utilizado corretamente:

Solução 2: Criar um FUNCTION-BASED INDEX (FBI)

Embora a Solução 1 seja ideal, nem sempre é possível ajustar o código da aplicação.

Aplicações fechadas: O código é proprietário e o acesso está bloqueado.
Fornecedores sem suporte: Não há mais suporte técnico do fornecedor para modificar a aplicação.
Impacto em sistemas legados: Alterar tipos de dados em variáveis pode quebrar outras partes da aplicação.

Então vamos marretar . . .

Nesse caso, criei um FUNCTION-BASED INDEX (FBI)

SQL> CREATE INDEX IDX_FBI_LOCATIONS_POSTAL_CODE_NUM ON HR.LOCATIONS (TO_NUMBER(POSTAL_CODE));

Index created.

SQL>

Consulta original (sem alteração na bind):

SQL> ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL> VARIABLE SYS_BIN_1     NUMBER;
SQL> EXEC    :SYS_BIN_1     := 190518;

PL/SQL procedure successfully completed.

SQL>
SQL> SET FEEDBACK OFF;
SQL> select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1;

LOCATION_ID STREET_ADDRESS                           POSTAL_CODE  CITY                           STATE_PROVINCE            CO
----------- ---------------------------------------- ------------ ------------------------------ ------------------------- --
       2000 40-5-12 Laogianggen                      190518       Beijing                                                  CN
SQL>
SQL> SELECT * FROM TABLE (dbms_xplan.display_cursor(NULL,NULL,'advanced allstats -outline last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7urqspapjqh82, child number 0
-------------------------------------
select * from HR.LOCATIONS where POSTAL_CODE = :SYS_BIN_1

Plan hash value: 3543225418

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                   |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LOCATIONS                         |      1 |      1 |    48 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN                  | IDX_FBI_LOCATIONS_POSTAL_CODE_NUM |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / "LOCATIONS"@"SEL$1"
   2 - SEL$1 / "LOCATIONS"@"SEL$1"

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

   2 - access("LOCATIONS"."SYS_NC00007$"=:SYS_BIN_1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "LOCATIONS"."LOCATION_ID"[NUMBER,22], "LOCATIONS"."STREET_ADDRESS"[VARCHAR2,40], "LOCATIONS"."POSTAL_CODE"[VARCHAR2,12],
       "LOCATIONS"."CITY"[VARCHAR2,30], "LOCATIONS"."STATE_PROVINCE"[VARCHAR2,25], "LOCATIONS"."COUNTRY_ID"[CHARACTER,2]
   2 - "LOCATIONS".ROWID[ROWID,10], "LOCATIONS"."SYS_NC00007$"[NUMBER,22]

SQL>

Com o FUNCTION-BASED INDEX, o Oracle passou a utilizá-lo

Essa solução resolve o problema, mas demanda mais espaço e recursos no banco devido ao custo de manutenção do FUNCTION-BASED INDEX (FBI). Em tabelas com alta frequência de atualizações, o impacto pode ser ainda maior. Por isso, o uso de FBIs deve ser avaliado com cautela e aplicado apenas quando ajustes no código não forem possíveis.

Esse incidente foi resolvido após identificar que, durante o deploy, a equipe de desenvolvimento alterou o tipo da variável, gerando uma incompatibilidade com o tipo da coluna. Ajustar a variável bind (Solução 1) foi a abordagem mais eficiente. Entretanto, em cenários mais complexos, criar um FUNCTION-BASED INDEX (Solução 2) também pode ser uma alternativa válida a ser considerada.

Os exemplos deste post são uma simulação desenvolvida para ilustrar o problema e suas soluções de forma clara. Eles não refletem exatamente a consulta original do cliente, mas foram criados para evidenciar o comportamento observado em um ambiente produtivo.

e … zas

🛰️🚀#20250117 #DBASobrinho #GuinaNãoTinhaDó #BóBó #CaceteDeAgulha #OracleACE #VanziniReclamona #QueroAguáAgora #AnoComeçou #AnnaNaNatação 🛰️🚀

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.