This topic describes the system functions that can be used for function-based indexes.
System functions with no requirement for parameter types
| 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 | ORA_HASH |
| RTRIM | LENGTHC | EMPTY_BLOB | LPAD |
| INSTRC | TO_BLOB | SQRT | ASCIISTR |
| NLSSORT | EXP | TRUNC | TREAT |
| LN | TO_NUMBER | SINH | LOG |
| HEXTORAW | COSH | ASIN | FROM_TZ |
| TANH | TO_CHAR | TO_NCHAR | - |
IS JSON is a condition rather than a regular function.
System functions with requirements on the data type
The following system functions are prohibited from being used in function-based indexes or for generating columns when their parameters are of the string type and the format is default. Additionally, when the time format is not default, there are requirements for the format parameters. For example, for TO_DATE(expr, fmt), fmt must contain a date.
| NEXT_DAY | TO_DATE | LAST_DAY | ADD_MONTHS |
| TO_TIMESTAMP | TIMESTAMP_NVL | MONTHS_BETWEEN | TO_TIMESTAMP_TZ |
| SYS_EXTRACT_UTC | - | - | - |
JSON-related functions
The following functions can be used for 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 | - | - |
Furthermore, the following functions are currently only allowed to be used in predicates.
| JSON_EXISTS |
CAST expressions
CAST expressions are special. When their target or source type is a time type, they are prohibited from being used as part of a function-based index. The specific scenarios are shown in the following table.
Source Type |
Target Type |
Description |
|---|---|---|
| TIMESTAMP/DATE | TIMESTAMP WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE | Prohibited as a function index |
| string | Time type | Prohibited as a function index |
| Time type | string | Prohibited as a function index |