Check functional based and expression index in Oracle

Function based index is the index having expression value while creating it.

List functional based index in schema

select di.table_name,di.index_name,di.index_type,di.status from dba_indexes di
where di.owner = 'SCOTT' and di.index_type like '%FUNCTION-BASED%';


List the functional base index present in tablespace name

select di.index_name,di.index_type,di.status from dba_indexes di , dba_tables dt
where di.tablespace_name = 'SYSAUX'
and dt.table_name = di.table_name
and di.index_type like '%FUNCTION-BASED%'
order by 1 asc


Example of checking the function based index and expression as follows:

1. Create a table


SQL> create table test2 (id number);

Table created.

2. Create an expression index or function based index on table.

SQL> create index test2_idx on test2(id*1);

Index created.


3. Check the function based index present in database.

col owner for a8
col index_name for a15
col index_type for a21
select owner, index_name, index_type
from dba_indexes
where index_type like 'FUNCTION-BASED%' and index_name like 'TEST%';

OWNER    INDEX_NAME      INDEX_TYPE
-------- --------------- ---------------------
SYS      TEST2_IDX       FUNCTION-BASED NORMAL

4. Find the column expression used in function based index

col table_name for a10
col index_name for a10
col column_expression for a25
select table_name,index_name,column_expression
from dba_ind_expressions
where index_name ='TEST2_IDX';

TABLE_NAME INDEX_NAME COLUMN_EXPRESSION
---------- ---------- ------------------
TEST2      TEST2_IDX  "ID"*1

5. Get the DDL for the index with expression.


set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('INDEX','TEST2_IDX','SYS') from dual;
--------------------------------------------------------------------------