Supongamos que quisieramos evitar duplicidad de valores de una columna por una mal chequeo de formularios de un programa que termina permitiendo agregar valores que ya existen, con la única diferencia que le agrega espacios en blanco o cambia alguna letra a mayúscula o minúscula.
Un índice basado en función no solamente permite agilizar las búsquedas por expresión. Recomiendo este video para una breve explicación:
Un índice basado en función también puede usarse como un tipo constraint. Dado el caso explicado al principio, podríamos crear un índice de tipo "UNIQUE" para prevenir duplicidad con una función que limpia el valor a insertar y chequea si el valor limpiado es único:
create unique index INDEX_NAME on TABLE (lower(trim(COL_1)));
Una vez agregado el índice a la tabla, cualquier inserción de un valor duplicado (que tiene espacios o letras cambiadas a minúscula/mayúscula) lanzará el siguiente error:
SQL Error: ORA-00001: unique constraint (TABLE.INDEX_NAME) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
