|
 |
|
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. |
 |
|