I have a query with sub-query that should narrow down a search of triples:
DELETE FROM XPROCESS_TPL t WHERE t.triple.rdf_s_id
IN
( SELECT r.value_id FROM mdsys.rdf_value$ r
WHERE r.value_type='UR' AND r.vname_suffix LIKE 'ProcessAggregate_Lens Fab%' )
the inner SELECT returns 122707 rows in 41.914 seconds
However, the outer query takes ~6 hours all together...
What I thought would happen was it should first do the sub-query,get all value_id's and then use index to find data in XPROCESS_TPL
Why is that not happening and how can I optimize this query?
I created index on rdf_s_id:
CREATE INDEX "SEMANTIC"."XPROCESS_SUB_IDX" ON "SEMANTIC"."XPROCESS_TPL" ("TRIPLE"."RDF_S_ID")