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