Index Creation - UNION statement

SQL++
1

Hi Team,
we are running below query in CB 6.5 ,
SELECT meta().id, $MdfdTmstmp FROM poc WHERE $Type = ‘XXXXX’ AND ANY date IN CustDistRltnshps SATISFIES date.RltnshpEndDt < ‘2020-08-06’ END
UNION
SELECT meta().id, $MdfdTmstmp FROM poc WHERE $Type = ‘XXXXX’ AND ANY date IN custDistRltnshps SATISFIES date.RltnshpStrtDt = ‘2020-08-06’ END

How do we create an index for this statement, when we try to get an advise

CREATE INDEX srcCustDistRltnshpsStrtDtIx ON masterdata-source($Type,(distinct (array (date.RltnshpStrtDt) for date in custDistRltnshps end)))
CREATE INDEX adv_custRltnshpDocType_Type_DISTINCT_CustDistRltnshps_RltnshpEndDt ON masterdata-source(custRltnshpDocType,$Type,DISTINCT ARRAY date.RltnshpEndDt FOR date in CustDistRltnshps END)

but it is not taking both the index, it is taking either one of the index. How do we create index for this query?

2 
SELECT meta().id, `$MdfdTmstmp`
FROM poc
WHERE `$Type` = "XXXXX" AND ANY v IN CustDistRltnshps SATISFIES v.RltnshpEndDt < "2020-08-06" END
UNION
SELECT meta().id, `$MdfdTmstmp`
FROM poc
WHERE `$Type` = "XXXXX" AND ANY v IN CustDistRltnshps SATISFIES v.RltnshpStrtDt = "2020-08-06" END

CREATE INDEX ix1 ON poc(DISTINCT ARRAY v.RltnshpStrtDt FOR v IN custDistRltnshps END,`$MdfdTmstmp`) WHERE `$Type` = "XXXXX";
CREATE INDEX ix2 ON poc(DISTINCT ARRAY v.RltnshpEndDt FOR v IN custDistRltnshps END,`$MdfdTmstmp`) WHERE `$Type` = "XXXXX";
3

Great, this works fine. Is there a way we can combine these 2 queries into 1? we tried and it didn’t take correct index in that scenario.

4 
SELECT meta().id, `$MdfdTmstmp`
FROM poc
WHERE `$Type` = "XXXXX" AND (ANY v IN CustDistRltnshps SATISFIES v.RltnshpEndDt < "2020-08-06" END
 OR  ANY v IN CustDistRltnshps SATISFIES v.RltnshpStrtDt = "2020-08-06" END)

This makes query not covered

5

Hi vsr1,
“This makes query not covered” meaning we can’t create index for this?

6

Same query uses two indexes it will use covering index. You can try