Sample Header Ad - 728x90

Oracle - function based index not being used

0 votes
0 answers
28 views
On an Oracle Database 12c Standard Edition Release 12.2.0.1.0, I have a table that contains postal codes with corresponding names, which aren't normalized. We have a DETERMINISTIC function that returns a normalized text (e.g. 'Fernão Ferro' -> 'FERNAO FERRO') so we created an index for the description column for search purposes and gathered table statistics after.
CREATE INDEX IX_POSTAL_CODES_1 ON POSTAL_CODES (NORMALIZAR_TEXTO(DESCRICAO));

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
      ownname => 'XXXXXXXX',
      tabname => 'POSTAL_CODES',
      cascade => TRUE
   );
END;
/
We have used this function successfully on other tables but, unless we apply an hint /*+ INDEX(POSTAL_CODES IX_POSTAL_CODES_1) */ it never uses the index on this one. A simple count takes ~5s, but with the hint it takes ~9ms.
SELECT
    /*+ INDEX(POSTAL_CODES IX_POSTAL_CODES_1) */
    COUNT(*)
FROM POSTAL_CODES
WHERE
    NORMALIZAR_TEXTO(DESCRICAO) LIKE '%FERNAO%FERRO%'
The execution plan without the hint:
Explained.


PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 1234747403
 
-------------------------------------------
| Id  | Operation          | Name         |
-------------------------------------------
|   0 | SELECT STATEMENT   |              |
|   1 |  SORT AGGREGATE    |              |
|*  2 |   TABLE ACCESS FULL| POSTAL_CODES |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") LIKE 
              '%FERNAO%FERRO%')
 
Note
-----
   - rule based optimizer used (consider using cbo)

19 rows selected.
The execution plan with the hint:
Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1574285503
 
--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    12 |   682   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                   |     1 |    12 |            |          |
|*  2 |   INDEX FULL SCAN| IX_POSTAL_CODES_1 | 10979 |   128K|   682   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") LIKE 
              '%FERNAO%FERRO%' AND "XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") IS NOT NULL)

15 rows selected.
I already cloned the table to a new one, created all the same indexes and the behavior is always the same.
Asked by João Simões (101 rep)
Jun 24, 2025, 11:30 AM