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