Using Oracle Function-Based Indexes Oracle Tips by Burleson Consulting

Oracle8i also introduced the concept of a function-based index. In previous releases of Oracle, if we wanted to have a column that was always searched uppercase (for example, a last name that could have mixed-case, such as McClellum), we had to place the returned value with its mixed-case letters in one column and add a second column that was uppercased to index and use in searches. The double storage of columns required for this type of searching led to the doubling of size requirements for some application fields. The cases where more complex requirements such as the use of SOUNDEX and other functions would also have required the use of a second column. This is not the case with Oracle releases later than and including Oracle8i; Oracle-provided functions, user-defined functions, as well as methods, can be used in indexes. Let’s look at a simple example using the UPPER function.

CREATE INDEX tele_Remote DBA.up1_clientsv8i
ON tele_Remote DBA.clientsv8i(UPPER(customer_name))
TABLESPACE tele_index
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

In many applications, a column may store a numeric value that translates to a minimal set of text values; for example, a user code that designates functions such as ‘Manager’, ‘Clerk’, or ‘General User’. In previous versions of Oracle, you had to perform a join between a lookup table and the main table to search for all ‘Manager’ records. With function indexes, the DECODE function can be used to eliminate this type of join.

CREATE INDEX tele_Remote DBA.dec_clientsv8i
ON tele_Remote DBA.clientsv8i(DECODE(user_code,
1,'MANAGER',2,'CLERK',3,'GENERAL USER'))
TABLESPACE tele_index
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

A query against the clientsv8i table that would use the above index would look like:

SELECT customer_name FROM tele_Remote DBA.clientsv8i
WHERE DECODE(user_code,
1,'MANAGER',2,'CLERK',3,'GENERAL USER')='MANAGER';

The execution plan for the above query shows that the index will be used to execute the query:

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT customer_name FROM tele_Remote DBA.clientsv8i
  2  WHERE DECODE(user_code,
  3* 1,'MANAGER',2,'CLERK',3,'GENERAL USER') = 'MANAGER'
 no rows selected

Execution Plan
---------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=526)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CLIENTSV8i' (Cost=1 Card=1 Bytes=526)
   2    1     INDEX (RANGE SCAN) OF 'DEC_CLIENTSV8i' (NON-UNIQUE) (Cost=1 Card=1)

The table using function-based indexes must be analyzed, and the optimizer mode must be set to CHOOSE, or the function-based indexes will not be used. The RULE-based optimizer cannot use function-based indexes. Function-based indexes are only available in the Enterprise and Personal Oracle releases, not in Standard Oracle.

If the function-based index is built using a user-defined function, any alteration or invalidation of the user function will invalidate the index. Any user-built functions must not contain aggregate functions and must be deterministic in nature. A deterministic function is one that is built using the DETERMINISTIC keyword in the CREATE FUNCTION, CREATE PACKAGE, or CREATE TYPE commands. As stated earlier, a deterministic function is defined as one that always returns the same set value, given the same input, no matter where the function is executed from within your application. As of 9.0.1, the validity of the DETERMINISTIC keyword usage has not been verified, so it is left up to the programmer to ensure that it is used properly. A function-based index cannot be created on a LOB, REF, or nested table column, or against an object type that contains a LOB, REF, or nested table. A FUNCTION return value may be cached and reused if the call to the FUNCTION looks identical to the optimizer; therefore, if for some reason it will not return the same value ( if you use internal package variables for instance), then the changes may not be reliably reported. Let’s look at an example of a user-defined type (UDT) method:

CREATE TYPE room_t AS OBJECT(
lngth NUMBER,
width NUMBER,
MEMBER FUNCTION SQUARE_FOOT
RETURN NUMBER DETERMINISTIC);
/
CREATE TYPE BODY room_t AS
  MEMBER FUNCTION SQUARE_FOOT
  RETURN NUMBER IS
  area NUMBER;
  BEGIN
   AREA:=lngth*width;
    RETURN area;
  END;
END;
/
CREATE TABLE rooms OF room_t
TABLESPACE test_data
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0); 

CREATE INDEX area_idx ON rooms r (r.square_foot());

Note: The preceding example is based on those given in the Oracle manuals; when tested on 8.1.3, the DETERMINISTIC keyword caused an error, and dropping the DETERMINISTIC keyword allowed the type to be created. However, the attempted index creation failed on the alias specification. In 8.1.3, the keyword is REPEATABLE, instead of DETERMINISTIC; however, even when specified with the REPEATABLE keyword, the attempt to create the index failed on the alias. On both an 8.1.7 and 9.0.1 instance, all statements in the example work satisfactorily using the DETERMINISTIC keyword (remember that the user that creates the function-based index must have QUERY REWRITE or GLOBAL QUERY REWRITE privilege).

A function-based index either may be a normal B-tree index or be mapped into a bitmapped format.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.