Yes. You are right. OR clause worked by tweaking the indexes little bit. But now, i got another problem.

**Query:**

select * from books

USE INDEX (adv_DISTINCT_field1_class,

adv_DISTINCT_field2_class,

adv_DISTINCT_field3_field4_class USING GSI)

WHERE _class = ‘com.org.One’

AND (ANY t IN field1 SATISFIES t IN [“a”, “b”] END

OR ANY t IN field2 SATISFIES t IN [“c”, “d”] END)

AND ANY t IN ARRAY_CONCAT(IFMISSINGORNULL(field3, ),

IFMISSINGORNULL(field4, )) SATISFIES t IN [‘hvhv’,‘hvh’] END

**Data structure:**

{

“_class” : “com.org.One”,

“field1” : [“a”, “b”],

“field2” : [“a”, “b”],

“field3” : [“a”, “b”],

“field4” : [“a”, “b”],

}

**Indexes:**

Index1 → `CREATE INDEX adv_DISTINCT_field1_class ON `

books`(DISTINCT ARRAY`

t`FOR t in`

field1`END) WHERE`

_class` = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};`

Index2 → `CREATE INDEX adv_DISTINCT_field2_class ON `

books`(DISTINCT ARRAY`

t`FOR t in`

field2`END) WHERE`

_class` = ‘com.org.One’ using GSI with {‘num_replica’:1,‘num_partition’:6};`

Index3 → CREATE INDEX `adv_DISTINCT_field3_field4_class`

ON books (DISTINCT ARRAY t FOR t IN ARRAY_CONCAT( IFMISSINGORNULL(field3, ),

IFMISSINGORNULL(field4,)) END)

PARTITION BY HASH(aggregateId)

WHERE `_class`

= ‘com.org.One’

using GSI with {‘num_replica’:1,‘num_partition’:6};

In above query, all 3 indexes picked in 6.0.2 but only index3 picked in 6.6.1. Explain plans are given below:

**Cluster version - Enterprise Edition 6.0.2 build 2413**

```
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "adv_DISTINCT_field1_class",
"index_id": "ea340b5aa8a678e9",
"index_projection": {
"primary_key": true
},
"keyspace": "books",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"a\"",
"inclusion": 3,
"low": "\"a\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"b\"",
"inclusion": 3,
"low": "\"b\""
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "adv_DISTINCT_field3_field4_class",
"index_id": "cf135d11c8d1fac1",
"index_projection": {
"primary_key": true
},
"keyspace": "books",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"hvh\"",
"inclusion": 3,
"low": "\"hvh\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"hvhv\"",
"inclusion": 3,
"low": "\"hvhv\""
}
]
}
],
"using": "gsi"
}
}
]
},
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "adv_DISTINCT_field2_class",
"index_id": "4651370cfb1ee060",
"index_projection": {
"primary_key": true
},
"keyspace": "books",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"c\"",
"inclusion": 3,
"low": "\"c\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"d\"",
"inclusion": 3,
"low": "\"d\""
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "adv_DISTINCT_field3_field4_class",
"index_id": "cf135d11c8d1fac1",
"index_projection": {
"primary_key": true
},
"keyspace": "books",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"hvh\"",
"inclusion": 3,
"low": "\"hvh\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"hvhv\"",
"inclusion": 3,
"low": "\"hvhv\""
}
]
}
],
"using": "gsi"
}
}
]
}
]
},
{
"#operator": "Fetch",
"keyspace": "books",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`books`.`_class`) = \"com.org.One.\") and (any `t` in (`books`.`field1`) satisfies (`t` in [\"a\", \"b\"]) end or any `t` in (`books`.`field2`) satisfies (`t` in [\"c\", \"d\"]) end)) and any `t` in array_concat(ifmissingornull(books.field3, []), ifmissingornull(books.field4, [])) satisfies (`t` in [\"hvhv\", \"hvh\"]) end)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "select * from books
USE INDEX (adv_DISTINCT_field1_class,
adv_DISTINCT_field2_class,
adv_DISTINCT_field3_field4_class USING GSI)
WHERE _class = 'com.org.One'
AND (ANY t IN field1 SATISFIES t IN ["a", "b"] END
OR ANY t IN field2 SATISFIES t IN ["c", "d"] END)
AND ANY t IN ARRAY_CONCAT(IFMISSINGORNULL(field3, []),
IFMISSINGORNULL(field4, [])) SATISFIES t IN ['hvhv','hvh'] END"
}
```

**Cluster version - Enterprise Edition 6.6.1 build 9213**

```
{
"#operator": "Sequence",
"~children": [
{
"#operator": "DistinctScan",
"scan": {
"#operator": "IndexScan3",
"index": "adv_DISTINCT_field3_field4_class",
"index_id": "7b7044120ce10ac1",
"index_projection": {
"primary_key": true
},
"keyspace": "books",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"hvh\"",
"inclusion": 3,
"low": "\"hvh\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"hvhv\"",
"inclusion": 3,
"low": "\"hvhv\""
}
]
}
],
"using": "gsi"
}
},
{
"#operator": "Fetch",
"keyspace": "books",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`books`.`_class`) = \"com.org.One.\") and (any `t` in (`books`.`field1`) satisfies (`t` in [\"a\", \"b\"]) end or any `t` in (`books`.`field2`) satisfies (`t` in [\"c\", \"d\"]) end)) and any `t` in array_concat(ifmissingornull(books.field3, []), ifmissingornull(books.field4, [])) satisfies (`t` in [\"hvhv\", \"hvh\"]) end)"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
```