how do I create a function index for to sql commands

I am trying to improve the performance on a query I am running and it was suggested that I try a function index for the two sql commands. They are the following:

regexp_substr(DOCUMENT_ID,'[^/]+$')

and

site||'-'||pdf_name

The query is as follows:

select folder_id from dcs_rule where regexp_substr(DOCUMENT_ID,'[^/]+$') in (select site||'-'||pdf_name from holdings where publication_date >='01-JAN-10' and publication_date<='31-DEC-10');

Now the text that is left after all content up until the forward slash in the dcs_rule table is the equivalent of two fields in the holdings table. They are the site and pdf_name fields. So for example:

In the dcs_rule table the value of the document_ID field would look like this: RDBAFM_ORA10://cpia_lib/CPIA-1995-0525

In the query the REGEXP_SUBSTR(DOCUMENT_ID,'[^/]+$') command would strip everything and leave the following: CPIA-1995-0525

In the holdings table CPIA would be the site value and 1995-0525 would be the pdf_name value

I am trying to get the records that are in both records. What would be the syntax for creating a function index for both sql commands?

and you'll note, the syntax I created the index with and the syntax I queryed with are not identical but both are canonicalized to the same format

REGEXP_SUBSTR ("DOCUMENT_ID",'[^/]+$')

you should check this with your own version though, slightwv is correct that not all versions have been successful. I haven't had any problems with this since 10.2.0.4 but definitely did have problems in 9i.

Try hinting your SQL like this for the execution plan to be more readable:

select /*+NOPARALLEL*/ folder_id from dcs_rule where regexp_substr(DOCUMENT_ID,'[^/]+$') in (select site||'-'||pdf_name from holdings where publication_date >='01-JAN-10' and publication_date<='31-DEC-10');

What's the name of your new function-based index? Note that you only need one index, on regexp_substr(...)

Exception: If your "holdings" between 01-JAN-10 and 31-DEC-10 are a small percentage of the table, you *could* create another function-based index on (publication_date, "SITE"||'-'||"PDF_NAME"), so that the subquery is entirely solved in the index.