How to optimize my Query

[david97]

SELECT rt.content.code
FROM rxpTransaction rt
WHERE rt.type=‘financialExport’
AND ANY d IN (
SELECT DISTINCT RAW b.content.code
FROM rxpData b
WHERE b.type=‘offer’
AND b.content.creationDate BETWEEN DATE_ADD_MILLIS(NOW_MILLIS(), -120, ‘day’) AND NOW_MILLIS()) SATISFIES rt.content.code LIKE d||“%" END
AND ANY e IN (
SELECT DISTINCT RAW b.content.code
FROM rxpData b
WHERE b.type=‘mandate’
AND b.content.creationDate BETWEEN DATE_ADD_MILLIS(NOW_MILLIS(), -120, ‘day’) AND NOW_MILLIS()) SATISFIES rt.content.code LIKE "%”||e||“_%” END

[mreiche]

The Index Advisor can recommend indexes for the query.

[vsr1]

WITH offserCodes AS (SELECT DISTINCT RAW b.content.code
               FROM rxpData b
               WHERE b.type ="offer"
               AND b.content.creationDate BETWEEN DATE_ADD_MILLIS(NOW_MILLIS(), -120, ‘day’) AND NOW_MILLIS()),

     mandateCodes AS (SELECT DISTINCT RAW b.content.code
               FROM rxpData b
               WHERE b.type ="mandate"
               AND b.content.creationDate BETWEEN DATE_ADD_MILLIS(NOW_MILLIS(), -120, ‘day’) AND NOW_MILLIS())
SELECT rt.content.code
FROM rxpTransaction rt
WHERE rt.type = "financialExport"
      AND ANY d IN offserCodes SATISFIES rt.content.code LIKE d||“%" END
      AND ANY m IN mandateCodes SATISFIES rt.content.code LIKE m||“%" END;

CREATE INDEX ix1 ON rxpTransaction(type, content.creationDate,  content.code);