I am looking into the sparql function orardf:textContains, and I see it's optimization and use of indexes over the RDF_VALUE$ table over regex. However, I am looking at using textContains for it's search capabilities over all of the values in the model.
The sparql/query that I am attempting to execute is taking about 3 minutes to return (we have about 65million triples in the model). I am attempting to get the results from the orardf:textContains to drive the triple t0, and then get the displayLabel of ?s with the triple t1. I also have PSCM, PCSM, and CM (thought being the CANON_END_NODE_ID is coming back in ?o) indexes on the model that we are querying against.
I think the problem is with the ?s ?p ?o triple pattern.
The default PCSM and PSCM indexes are not the best for an unbound predicate, so the join with t0 after getting values for ?o from RDF_VALUE$ is probably a hash join instead of a nested loop join. You have the right idea with the CM index, but we will need all the columns to make the index-based NLJ possible (otherwise we have to do a table access to pick up values for S and P), so I would suggest that you try a CPSM index. Also, remember to rebuild the index with SEM_APIS.ALTER_SEM_INDEX_ON_MODEL after you create it so that it is usable.
The HINT0 hint is exactly what it should be, so you can leave this as it is.