This topic lists the system functions that can be directly or conditionally used for function-based indexes.
System functions without requirements on the data type
| TRIM | ACOS | REMAINDER | FLOOR |
| ATAN | SUBSTRB | CEIL | ATAN2 |
| WIDTH_BUCKET | RPAD | COS | TRANSLATE |
| NULLIFb | TAN | TO_BINARY_FLOAT | PAD |
| SIN | TO_BINARY_DOUBLE | POSITION | IS_JSON |
| VSIZE | LTRIM | POWER | ORAHASH |
| RTRIM | LENGTHC | EMPTY_BLOB | LPAD |
| INSTRC | TO_BLOB | SQRT | ASCIISTR |
| NLSSORT | EXP | ORA_TRUNC | TREAT |
| LN | TO_NUMBER | SINH | LOG |
| HEXTORAW | COSH | ASIN | FROM_TZ |
| TANH | TO_CHAR | TO_NCHAR | - |
| TANH | TO_CHAR | TO_NCHAR | - |
System functions with requirements on the data type
The following system functions are prohibited in function-based indexes or generated columns when the data type is string and the default data format is used. If the time format is not the default one, there are requirements on the format parameter. For example, TO_DATE(expr, fmt) requires that the value of fmt contain the date.
| NEXT_DAY | TO_DATE | LAST_DAY | ADD_MONTHS |
| TO_TIMESTAMP | TIMESTAMP_NVL | MONTHS_BETWEEN | TO_TIMESTAMP_TZ |
| SYS_EXTRACT_UTC | - | - | - |
JSON functions
The following functions can be used in function-based indexes.
| SOUNDEX | COALESCE | ASCII | NVL |
| INSTR | GREATEST | LEAST | UPPER |
| LOWER | ROUND | LENGTHB | RAWTOHEX |
| UNISTR | RAWTONHEX | CHR | TZ_OFFSET |
| INITCAP | INSTRB | NANVL | TO_SINGLE_BYTE |
| TO_MULTI_BYTE | TO_CLOB | EMPTY_CLOB | NLS_LOWER |
| NLS_UPPER | NVL2 | TO_DSINTERVAL | TO_YMINTERVAL |
| NUMTODSINTERVAL | NUMTOYMINTERVAL | - | - |
At present, the following function can be used only in predicates.
| JSON_EXISTS |
CAST expressions
When the target or source type of a CAST expression is a time type, the CAST expression cannot be used in function-based indexes. The following table lists the detailed scenarios.
Source type |
Target type |
Description |
|---|---|---|
| TIMESTAMP/DATE | TIMESTAMP WITH TIME ZONE/TIMESTAMP WITH LOCAL TIME ZONE | Prohibited in function-based indexes |
| string | Time type | Prohibited in function-based indexes |
| Time type | string | Prohibited in function-based indexes |