Check functional based and expression index in Oracle

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";

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.