gemfirespring-data-gemfireoql

IN vs OR in OQL pivotal gemfire

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]

Solution

  • "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.