Question

SQL

Select x,y,
  from tiger
 where mandt=A0
   AND ( "ERADT" between A1 and A2 and "AUTYP"=A3
      or "AEDAT" between A4 and A5 and "AUTYP"=A6)

In my case Mandt is a Primary Key. ERADT & AEDAT Columns are Date field. Here we specify both the date fields as ranges with an “OR” condition. For the Above SQL System is picking up the right index and CBO use MANDT as Access predicates Rest as Filter. Is this expected ?

Answer 1

This is absolutely expected.

Using the PK as access predicates narrows the search already down to a single row. Applying the filter predicates to that single row is hardly going to be a performance issue.

Using an index for the OR expression is tricky, however. You might define it like that:

  AUTYP, ERADT, AEDAT
or
  AUTYP, AEDAT, ERADT

However, using the range condition BETWEEN causes the last column to be useable as filter predicate only (see: Greater, Less and Between).

Using two indexes, on the other hand, is also trouble some, because it would mean to access two indexes, and merge the result (see: Index Merge).

The primary key does not have these issues, because it is used in an unconditional part of the where clause.