How conjunctions (AND) and disjunctions (OR) work with search optimization¶

Search optimization can accelerate queries using conjunctions (AND operator) and disjunctions (OR operator) of supported predicates.

Conjunctions of supported predicates (AND)¶

For queries that use conjunctions of predicates (i.e., AND), query performance can be improved by search optimization if any of the predicates would benefit.

For example, suppose that a query has:

where condition_x and condition_y

Search optimization can improve performance if either condition separately returns a few rows (i.e., condition_x returns a few rows or condition_y returns a few rows).

If condition_x returns a few rows but condition_y returns many rows, the query performance can still benefit from search optimization.

Examples¶

If predicates are individually supported by the search optimization service, then they can be joined by the conjunction AND and still be supported by the search optimization service:

select id, c1, c2, c3
    from test_table
    where c1 = 1
       and
          c3 = TO_DATE('2004-03-09')
    order by id;

Copy

DELETE and UPDATE (and MERGE) can also use the search optimization service:

delete from test_table where id = 3;

Copy

update test_table set c1 = 99 where id = 4;

Copy

Disjunctions of supported predicates (OR)¶

For queries that use disjunctions of predicates (i.e., OR), query performance can be improved by search optimization if all predicates would benefit.

For example, suppose that a query has:

where condition_x or condition_y

Search optimization can improve performance if each condition separately returns a few rows (i.e., condition_x returns a few rows and condition_y returns a few rows).

If condition_x returns a few rows but condition_y returns many rows, the query performance does not benefit from search optimization.

In the case of disjunctions, each predicate in isolation is not decisive in the query. All predicates must be evaluated to determine whether search optimization can improve performance.