Welcome aboard. I want to create a custom index structure and I am not sure how to do it. I haven't used data cartridges before, which may be a candidate, but maybe you can give me some insight.

Here is an example of what I'd like to do. I want my index organized on the result of passing a string through a function, but I want to only store the value of the string before it goes through the function in the index.

For instance, say I have a function that converts English words to Spanish. I want the index structure to be based on the Spanish translation of words, but to only store the English words.

When I have a where clause like "WHERE word like 'adios%'," I would like to go through my Btree index performing the to_spanish() function on each node as I traverse the tree. Because the index is structured based on the to_spanish() result, I should easily be able to traverse the index as normal to get my result set, performing the to_spanish() function as I traverse.

This is a made-up example, of course, but I wonder if there is any way to accomplish this?

    Requires Free Membership to View

I am not sure if you mean you only want to store the value in the table or in the index.

You can use a function-based index but the function-based index will store the computed value in the index. However, the original value (English string in your example) will be stored in the table. Nevertheless, in order to create function-based indexes, the following prerequisites must be satisfied. You can create either B+Tree or Bitmap function based index. Your DBA needs to make sure that in the Oracle parameter file (init.ora) the following parameters are set as follows.

COMPATIBLE = (or higher)

If they are not set, your DBA needs to make sure the init file is updated (so, subsequent database shutdown and startup maintains the same values for the parameters). If the database is already running, they can execute alter system commands to set the above parameters. To create function-based indexes the user must be granted CREATE INDEX and QUERY REWRITE privileges, or alternatively be granted CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges (But DBAs frown on giving this privilege).

The index owner must also have EXECUTE access on the function used for the index with grant option. If execute access is revoked then the function-based index will be "disabled."

If all prerequisites are satisfied, then you can create a function-based index.

Say you have a table of all English words stored in a table with the following structure and stores the string in English only.


In addition you have created a function.

to_Spanish(p_eng_str IN VARCHAR2) RETURNS VARCHAR2;

Now, you need to create a function-based index.


Once the function is created you can use the following query that will use the index, yet you are only storing the English words in your table.


If you get an error like FUNCTION is non-deterministic, then please ensure you specify the keyword DETERMINISTIC in your function creation statement.


       to_Spanish(p_eng_str IN VARCHAR2) RETURN VARCHAR2    DETERMINISTIC IS 
... ...
 RETURN retval;


This was first published in February 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: