N1QL query - Index Creation

SQL++
1

Hi ,
We have a below document and we would like to create an index for this, we want only the active CustRteRltnshps records.

{
“Id”: “XXXX::1000003_157_01”,
“$Type”: “XXXX”,
“CtryCd”: “mmm”,
“CustRteRltnshps”: [
{
“CtryCd”: “YYY”,
“RteDocId”: “ZZZZ::YYYYY_XXX”,
“RltnshpTypCdv”: “01”,
“RteID”: “XXXX”,
“EffDtRnge”: {
“EndDt”: “9999-12-25”,
“StrtDt”: “2012-03-11”
}
}
]
}

Our Current query is,
select a.CtryCd, rte.RteID, rte.CtryCd as rtctrycd, rte.EffDtRnge.StrtDt, rte.EffDtRnge.EndDt FROM masterdata-target a unnest CustRteRltnshps rte
WHERE a.$Type = ‘Customer’
AND rte.EffDtRnge.StrtDt <= CLOCK_LOCAL()
AND rte.EffDtRnge.EndDt >= CLOCK_LOCAL()

Explain gives only $Type index creation. Any possible solution?

Thanks

2

“Explain” tells you what index(es) the query is using, it doesn’t tell you what index you should create. “Advise” is probably what you are looking for. For example, when I run “Advise” on that query in 6.6 Enterprise (or via the web-based query advisor), it recommends:

Covering Index Recommendations

CREATE INDEX adv_ALL_CustRteRltnshps_Type_CtryCd ON `masterdata-target` (ALL `CustRteRltnshps`,`$Type`,`CtryCd`)

Index Recommendations

CREATE INDEX adv_ALL_CustRteRltnshps_Type ON `masterdata-target`(ALL `CustRteRltnshps`,`$Type`)