0

When dealing with content spanned across multiple tables (regions in terms of gemfire) on different nodes in a cluster, which operator provides faster results.

Let's say, for now my search OQL query looks like following:

select * from /content_region where content_type = 'xyz' AND (shared_with.contains('john') OR (shared_with.contains('michael') OR (shared_with.contains('peter')))

Consider 'shared_with' is List.

References:

IN vs OR in the SQL WHERE Clause

SQL performance tuning for Oracle Many OR vs IN () [duplicate]

Community
  • 1
  • 1
ms_27
  • 1,484
  • 15
  • 22

1 Answers1

1

"IN" on an indexed field will be extremely more responsive than "OR" as a direct answer but there are exceptions.

Some comments on your example:

select * from /content_region where content_type = 'xyz' AND (shared_with.contains('john') OR (shared_with.contains('michael') OR (shared_with.contains('peter')))

You want to have that "content_type = 'xyz' AND " in front of your "OR" statements because GemFire will first execute that statement and with the smaller result set, apply the "contains" operation in-memory with the limited result set.

In the example that you provided, an "IN" clause cannot be applied with contains.

Before I leave this answer, I do use IN frequently with keys from another result set. If the attribute is indexed, it is very fast.

Wes Williams
  • 266
  • 1
  • 5
  • Thanks @Wes. Yes, we are using gemfire map based indexes and as of now, OR is performing better than IN for direct equality searches. And, for 'contains' feature, "IN" clause doesn't even support regex search e.g. '%john%' to fetch content shared with 'john frey', 'john geller', etc. So, we headed with OR clause with 'contains' method. – ms_27 Aug 23 '16 at 07:15
  • Sorry my bad. `contains()` is on list here, so it also won't support partial searches with '%'. – ms_27 Aug 23 '16 at 08:35