Creating a Function-Based Index

You must set the initialization parameter QUERY_REWRITE_ENABLED to true in order to take advantage of function-based indexes.

show parameter QUERY_REWRITE_ENABLED;
if false then
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;

statement :

 CREATE INDEX INDEX_Name
 ON table(func(table.column));

ex. 
 CREATE INDEX QUEUE_TABLE_ID01
   ON QUEUE_TABLE(MOD(NO,2));   

Drop Index
DROP INDEX INDEX_NAME;