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;
--------------------------------------------------------------------------
CREATE INDEX "SYS"."TEST2_IDX" ON "SYS"."TEST2" ("ID"*1)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM";