A function-based index indexes a funciton’s return value.
It can be a:

• Built-in SQL function
• PL/SQL function
• A user-written function

It relieves the server from having to invoke the function for every key value as it performs a search on the indxed expression.

For example:

CREATE INDEX table1_ix ON TABLE1(my_function(column1, column2));

Now any query that contains the expression my_function(column1, column2) in the WHERE clause may be able to take advantage of this index (otherwise, it would force a full table scan)

In a B-tree index on a nonpartitioned table:

• Key value are repeated if there are multiple rows that have the same key value unless the index is compressed
• There is no index entry corresponding to a row that has all key columns that are NULL. Therefore, a WHERE clause specifying NULL will always result in a full table scan.
• Restricted ROWID is used to point to the rows of the table because all rows belong to the same segment.