BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter