Categorias
Foto de Roberto Sobrinho
Roberto Sobrinho

23/09/2025

Function-Based Index no Oracle: exemplos, benefícios e contrapontos

Em consultas SQL, quando utilizamos funções ou expressões em cláusulas WHERE, o Oracle não consegue aproveitar o INDEX tradicional. Nesses casos, o otimizador deixa de usar o acesso por INDEX e acaba recorrendo a varreduras completas de tabela (full table scan).

O Function-Based Index (FBI) foi criado para resolver esse problema. Ele permite a construção de INDEX sobre resultados de funções ou expressões, possibilitando que o otimizador utilize caminhos mais eficientes em consultas que dependem desses cálculos.

Neste artigo, será demonstrado o uso de Function-Based Index com exemplos práticos. Cada caso será validado por meio do plano de execução real, utilizando a hint /*+ gather_plan_statistics */ e exibindo os resultados com o pacote DBMS_XPLAN.

O Que é um Function-Based Index?

O Function-Based Index é um índice construído a partir de uma transformação de coluna. Essa transformação pode ser:

  • Aplicação de uma função nativa (UPPER, TRUNC, SUBSTR, etc.);
  • Uma expressão aritmética ou lógica (por exemplo, salario * 12);
  • Uma função definida pelo usuário (desde que seja determinística);
  • Uma coluna virtual derivada de outras colunas.

Caso 1 > Pesquisa Case-Insensitive com UPPER(LAST_NAME)

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Baseline: consulta sem Function-Based Index
SQL> --------------------------------------------------------------------------------
SQL> SET SERVEROUTPUT OFF
SQL>
SQL> SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name
  2  FROM hr.employees
  3  WHERE UPPER(last_name) = 'SMITH';

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME
-----------|--------------------|--------------------
        159|Lindsey             |Smith
        171|William             |Smith

2 rows selected.

SQL>
SQL> -- Exibir plano real 
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  86upfs70715rj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name
FROM hr.employees WHERE UPPER(last_name) = 'SMITH'

Plan hash value: 1445457117

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

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

   1 - filter(UPPER("LAST_NAME")='SMITH')


19 rows selected.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Criação do INDEX function-based em UPPER(LAST_NAME)
SQL> --------------------------------------------------------------------------------
SQL> CREATE INDEX hr.idx_emp_lastname_upper ON hr.employees (UPPER(last_name));

Index created.

SQL>
SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Reexecução da consulta (agora com o INDEX disponível)
SQL> --------------------------------------------------------------------------------
SQL> SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name
  2  FROM hr.employees
  3  WHERE UPPER(last_name) = 'SMITH';

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME
-----------|--------------------|--------------------
        159|Lindsey             |Smith
        171|William             |Smith

2 rows selected.

SQL>
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  86upfs70715rj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name
FROM hr.employees WHERE UPPER(last_name) = 'SMITH'

Plan hash value: 1940200029

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

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

   2 - access("EMPLOYEES"."SYS_NC00012$"='SMITH')


20 rows selected.

SQL>
SQL> DROP INDEX hr.idx_emp_lastname_upper;

Index dropped.

SQL>

Sem o FBI, o Oracle pode fazer varredura completa. Com o IDX_EMP_LASTNAME_UPPER, o otimizador usa INDEX RANGE SCAN direto em UPPER(last_name).

Caso 2 > INDEX em Expressão: Salário Anual com Comissão

SQL> --------------------------------------------------------------------------------
SQL> -- Baseline: consulta sem INDEX na expressão
SQL> --------------------------------------------------------------------------------
SQL> SET SERVEROUTPUT OFF
SQL>
SQL> SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name, job_id, salary, commission_pct
  2  FROM hr.employees
  3  WHERE salary * 12 * (1 + NVL(commission_pct, 0)) >= 120000;

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME           |JOB_ID      |    SALARY|COMMISSION_PCT
-----------|--------------------|--------------------|------------|----------|--------------
        100|Steven              |King                |AD_PRES     |     24000|
        101|Neena               |Kochhar             |AD_VP       |     17000|
        102|Lex                 |De Haan             |AD_VP       |     17000|
        108|Nancy               |Greenberg           |FI_MGR      |     12008|
        114|Den                 |Raphaely            |PU_MAN      |     11000|
        145|John                |Russell             |SA_MAN      |     14000|            .4
        146|Karen               |Partners            |SA_MAN      |     13500|            .3
        147|Alberto             |Errazuriz           |SA_MAN      |     12000|            .3
        148|Gerald              |Cambrault           |SA_MAN      |     11000|            .3
        149|Eleni               |Zlotkey             |SA_MAN      |     10500|            .2
        150|Peter               |Tucker              |SA_REP      |     10000|            .3
        151|David               |Bernstein           |SA_REP      |      9500|           .25
        152|Peter               |Hall                |SA_REP      |      9000|           .25
        156|Janette             |King                |SA_REP      |     10000|           .35
        157|Patrick             |Sully               |SA_REP      |      9500|           .35
        158|Allan               |McEwen              |SA_REP      |      9000|           .35
        159|Lindsey             |Smith               |SA_REP      |      8000|            .3
        162|Clara               |Vishney             |SA_REP      |     10500|           .25
        163|Danielle            |Greene              |SA_REP      |      9500|           .15
        168|Lisa                |Ozer                |SA_REP      |     11500|           .25
        169|Harrison            |Bloom               |SA_REP      |     10000|            .2
        170|Tayler              |Fox                 |SA_REP      |      9600|            .2
        174|Ellen               |Abel                |SA_REP      |     11000|            .3
        175|Alyssa              |Hutton              |SA_REP      |      8800|           .25
        176|Jonathon            |Taylor              |SA_REP      |      8600|            .2
        177|Jack                |Livingston          |SA_REP      |      8400|            .2
        201|Michael             |Hartstein           |MK_MAN      |     13000|
        204|Hermann             |Baer                |PR_REP      |     10000|
        205|Shelley             |Higgins             |AC_MGR      |     12008|

29 rows selected.

SQL>
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  buabkzk4dmpmn, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, first_name,
last_name, job_id, salary, commission_pct FROM hr.employees WHERE
salary * 12 * (1 + NVL(commission_pct, 0)) >= 120000

Plan hash value: 1445457117

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

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

   1 - filter("SALARY"*12*(1+NVL("COMMISSION_PCT",0))>=120000)


20 rows selected.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Criação do iNDEX na expressão
SQL> --------------------------------------------------------------------------------
SQL> CREATE INDEX hr.idx_emp_annual_total
  2    ON hr.employees ( salary * 12 * (1 + NVL(commission_pct, 0)) );

Index created.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Reexecução da consulta (mesma expressão no WHERE)
SQL> --------------------------------------------------------------------------------
SQL> SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name, job_id, salary, commission_pct
  2  FROM hr.employees
  3  WHERE salary * 12 * (1 + NVL(commission_pct, 0)) >= 120000;

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME           |JOB_ID      |    SALARY|COMMISSION_PCT
-----------|--------------------|--------------------|------------|----------|--------------
        204|Hermann             |Baer                |PR_REP      |     10000|
        177|Jack                |Livingston          |SA_REP      |      8400|            .2
        176|Jonathon            |Taylor              |SA_REP      |      8600|            .2
        159|Lindsey             |Smith               |SA_REP      |      8000|            .3
        163|Danielle            |Greene              |SA_REP      |      9500|           .15
        114|Den                 |Raphaely            |PU_MAN      |     11000|
        175|Alyssa              |Hutton              |SA_REP      |      8800|           .25
        152|Peter               |Hall                |SA_REP      |      9000|           .25
        170|Tayler              |Fox                 |SA_REP      |      9600|            .2
        151|David               |Bernstein           |SA_REP      |      9500|           .25
        169|Harrison            |Bloom               |SA_REP      |     10000|            .2
        108|Nancy               |Greenberg           |FI_MGR      |     12008|
        205|Shelley             |Higgins             |AC_MGR      |     12008|
        158|Allan               |McEwen              |SA_REP      |      9000|           .35
        149|Eleni               |Zlotkey             |SA_MAN      |     10500|            .2
        157|Patrick             |Sully               |SA_REP      |      9500|           .35
        150|Peter               |Tucker              |SA_REP      |     10000|            .3
        201|Michael             |Hartstein           |MK_MAN      |     13000|
        162|Clara               |Vishney             |SA_REP      |     10500|           .25
        156|Janette             |King                |SA_REP      |     10000|           .35
        148|Gerald              |Cambrault           |SA_MAN      |     11000|            .3
        174|Ellen               |Abel                |SA_REP      |     11000|            .3
        168|Lisa                |Ozer                |SA_REP      |     11500|           .25
        147|Alberto             |Errazuriz           |SA_MAN      |     12000|            .3
        101|Neena               |Kochhar             |AD_VP       |     17000|
        102|Lex                 |De Haan             |AD_VP       |     17000|
        146|Karen               |Partners            |SA_MAN      |     13500|            .3
        145|John                |Russell             |SA_MAN      |     14000|            .4
        100|Steven              |King                |AD_PRES     |     24000|

29 rows selected.

SQL>
SQL> -- Verificação do plano: esperado INDEX RANGE SCAN em IDX_EMP_ANNUAL_TOTAL
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  buabkzk4dmpmn, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, first_name,
last_name, job_id, salary, commission_pct FROM hr.employees WHERE
salary * 12 * (1 + NVL(commission_pct, 0)) >= 120000

Plan hash value: 2675496352

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

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

   2 - access("EMPLOYEES"."SYS_NC00012$">=120000)


21 rows selected.

SQL>
SQL>  DROP INDEX hr.idx_emp_annual_total;

Index dropped.

SQL>
SQL>

O Oracle antes precisava calcular linha a linha. Com o IDX_EMP_ANNUAL_TOTAL, a busca é feita direto via INDEX RANGE SCAN na expressão.

Caso 3 > Coluna Virtual + INDEX

QL> --------------------------------------------------------------------------------
SQL> -- Criação da coluna virtual (annual_total)
SQL> --------------------------------------------------------------------------------
SQL> ALTER TABLE hr.employees
  2    ADD (annual_total AS ( salary * 12 * (1 + NVL(commission_pct, 0)) ));

Table altered.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Criação do IDX sobre a coluna virtual
SQL> --------------------------------------------------------------------------------
SQL> CREATE INDEX hr.idx_emp_annual_total_vc ON hr.employees (annual_total);

Index created.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Consulta utilizando a coluna virtual
SQL> --------------------------------------------------------------------------------
SQL> SELECT /*+ gather_plan_statistics */ employee_id, first_name, last_name, job_id, annual_total
  2  FROM hr.employees
  3  WHERE annual_total BETWEEN 100000 AND 180000
  4  ORDER BY annual_total DESC;

EMPLOYEE_ID|FIRST_NAME          |LAST_NAME           |JOB_ID      |ANNUAL_TOTAL
-----------|--------------------|--------------------|------------|------------
        168|Lisa                |Ozer                |SA_REP      |      172500
        174|Ellen               |Abel                |SA_REP      |      171600
        148|Gerald              |Cambrault           |SA_MAN      |      171600
        156|Janette             |King                |SA_REP      |      162000
        162|Clara               |Vishney             |SA_REP      |      157500
        201|Michael             |Hartstein           |MK_MAN      |      156000
        150|Peter               |Tucker              |SA_REP      |      156000
        157|Patrick             |Sully               |SA_REP      |      153900
        149|Eleni               |Zlotkey             |SA_MAN      |      151200
        158|Allan               |McEwen              |SA_REP      |      145800
        205|Shelley             |Higgins             |AC_MGR      |      144096
        108|Nancy               |Greenberg           |FI_MGR      |      144096
        169|Harrison            |Bloom               |SA_REP      |      144000
        151|David               |Bernstein           |SA_REP      |      142500
        170|Tayler              |Fox                 |SA_REP      |      138240
        152|Peter               |Hall                |SA_REP      |      135000
        175|Alyssa              |Hutton              |SA_REP      |      132000
        114|Den                 |Raphaely            |PU_MAN      |      132000
        163|Danielle            |Greene              |SA_REP      |      131100
        159|Lindsey             |Smith               |SA_REP      |      124800
        176|Jonathon            |Taylor              |SA_REP      |      123840
        177|Jack                |Livingston          |SA_REP      |      120960
        204|Hermann             |Baer                |PR_REP      |      120000
        160|Louise              |Doran               |SA_REP      |      117000
        153|Christopher         |Olsen               |SA_REP      |      115200
        154|Nanette             |Cambrault           |SA_REP      |      108000
        109|Daniel              |Faviet              |FI_ACCOUNT  |      108000
        103|Alexander           |Hunold              |IT_PROG     |      108000
        161|Sarath              |Sewall              |SA_REP      |      105000
        171|William             |Smith               |SA_REP      |      102120
        172|Elizabeth           |Bates               |SA_REP      |      100740

31 rows selected.

SQL>
SQL> -- Verificação do plano: esperado INDEX RANGE SCAN em IDX_EMP_ANNUAL_TOTAL_VC
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8nzh5710cm5xc, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, first_name,
last_name, job_id, annual_total FROM hr.employees WHERE annual_total
BETWEEN 100000 AND 180000 ORDER BY annual_total DESC

Plan hash value: 2439834228

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |      1 |        |       |     2 (100)|          |     31 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES               |      1 |      1 |    41 |     2   (0)| 00:00:01 |     31 |00:00:00.01 |      13 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_EMP_ANNUAL_TOTAL_VC |      1 |      1 |       |     1   (0)| 00:00:01 |     31 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("ANNUAL_TOTAL"<=180000 AND "ANNUAL_TOTAL">=100000)


21 rows selected.

SQL>
SQL> DROP INDEX hr.idx_emp_annual_total_vc;

Index dropped.

SQL> ALTER TABLE hr.employees DROP COLUMN annual_total;

Table altered.

SQL>

A coluna virtual evita repetir a expressão e, com o IDX_EMP_ANNUAL_TOTAL_VC, o Oracle usa INDEX RANGE SCAN para filtrar por annual_total.

Caso 4 > função Customizada Determinística + INDEX

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Criação da função determinística
SQL> --------------------------------------------------------------------------------
SQL> CREATE OR REPLACE FUNCTION hr.norm_job(p_job_id VARCHAR2)
  2    RETURN VARCHAR2 DETERMINISTIC IS
  3  BEGIN
  4    RETURN SUBSTR(p_job_id, 1, 2);
  5  END;
  6  /

Function created.

SQL> SHOW ERRORS;
No errors.
SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Criação do INDEX baseado na função
SQL> --------------------------------------------------------------------------------
SQL> CREATE INDEX hr.idx_emp_norm_job
  2    ON hr.employees ( hr.norm_job(job_id) );

Index created.

SQL>
SQL> --------------------------------------------------------------------------------
SQL> -- Consulta utilizando a função
SQL> --------------------------------------------------------------------------------
SQL> SELECT /*+ gather_plan_statistics */ employee_id, job_id
  2  FROM hr.employees
  3  WHERE hr.norm_job(job_id) = 'SA';

EMPLOYEE_ID|JOB_ID
-----------|------------
        145|SA_MAN
        146|SA_MAN
        147|SA_MAN
        148|SA_MAN
        149|SA_MAN
        150|SA_REP
        151|SA_REP
        152|SA_REP
        153|SA_REP
        154|SA_REP
        155|SA_REP
        156|SA_REP
        157|SA_REP
        158|SA_REP
        159|SA_REP
        160|SA_REP
        161|SA_REP
        162|SA_REP
        163|SA_REP
        164|SA_REP
        165|SA_REP
        166|SA_REP
        167|SA_REP
        168|SA_REP
        169|SA_REP
        170|SA_REP
        171|SA_REP
        172|SA_REP
        173|SA_REP
        174|SA_REP
        175|SA_REP
        176|SA_REP
        177|SA_REP
        178|SA_REP
        179|SA_REP

35 rows selected.

SQL>
SQL> -- Verificação do plano: esperado INDEX RANGE SCAN em IDX_EMP_NORM_JOB
SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dv4mv2fhh7djv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ employee_id, job_id FROM
hr.employees WHERE hr.norm_job(job_id) = 'SA'

Plan hash value: 2322374073

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |       |     2 (100)|          |     35 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |      1 |      1 |    69 |     2   (0)| 00:00:01 |     35 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN                  | IDX_EMP_NORM_JOB |      1 |      1 |       |     1   (0)| 00:00:01 |     35 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEES"."SYS_NC00012$"='SA')


20 rows selected.

SQL>
SQL> DROP INDEX hr.idx_emp_norm_job;

Index dropped.

SQL> DROP FUNCTION hr.norm_job;

Function dropped.

SQL>

A função NORM_JOB padroniza o JOB_ID. Com o IDX_EMP_NORM_JOB, a consulta é resolvida com INDEX RANGE SCAN em cima da função.

Contrapontos

  • Impacto em DML: cada INSERT, UPDATE ou DELETE precisa atualizar o FBI.
  • Espaço adicional: cada FBI ocupa armazenamento extra.
  • Dependência de expressão idêntica: só funciona se a consulta usar exatamente a mesma expressão.
  • Funções não determinísticas: SYSDATE, DBMS_RANDOM e similares não podem ser usados.
  • Complexidade: excesso de FBIs pode dificultar manutenção e troubleshooting.

e zas

#20250923 #DBASobrinho #GuinaNãoTinhaDó #BóBó #CaceteDeAgulha #OracleACE

Compartilhe

Facebook
Twitter
LinkedIn
WhatsApp
Email
Print

Pesquisar

Roberto Sobrinho

Categorias

Categorias

Tags

Tags

Function-Based Index no Oracle: exemplos, benefícios e contrapontos

AUTHID CURRENT_USER no Oracle PL/SQL explicado com exemplos

Instalando rlwrap no Linux 7

Debug no PL/SQL Developer travando evento Pipe Get em ambiente Oracle RAC?

Function-Based Index no Oracle: exemplos, benefícios e contrapontos