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
QUERY_REWRITE_INTEGRITY = TRUSTED QUERY_REWRITE_ENABLED = TRUE COMPATIBLE = 22.214.171.124.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.
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.
CREATE OR REPLACE FUNCTION to_Spanish(p_eng_str IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS ... ... BEGIN .... ... ... RETURN retval; END;
Dig deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.