Q

How to create a custom index structure

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

QUERY_REWRITE_INTEGRITY = TRUSTED 
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (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.

ENGLISH_DICTIONARY(ENG_STRNG VARCHAR2(100));

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.

CREATE INDEX MY_TO_SPANISH_INDX ON ENGLISH_DICTIONARY(TO_SPANISH(ENG_STRNG));

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.

SELECT ENG_STRNG
FROM ENGLISH_DICTIONARY
WHERE TO_SPANISH(ENG_STRNG) LIKE 'ADIOS%';

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

Example

CREATE OR REPLACE FUNCTION 
       to_Spanish(p_eng_str IN VARCHAR2) RETURN VARCHAR2    DETERMINISTIC IS 
...
...
BEGIN
....
... ...
 RETURN retval;

END;

This was first published in February 2004

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close