Specific situations will have performance impacts, including queries with multiple
invariant predicates, indexes, and row IDs.
- Order of Predicate Execution – when a query has multiple invariant predicates, the order in
which the predicates are executed on the data in the RLV store depends on the
usefulness of the predicates. The order of predicate execution in the RLV store
could differ from the order of execution in the IQ main store.
- Lack of Indexes – unlike the IQ main store, the RLV store does not have the
capability to execute predicates using the best possible index when present.
Therefore, there could be a difference in the query plans, depending on whether
the query is executed on data that is in the IQ main store only, or on the same
data in the RLV store. Furthermore, some predicates that require special support
from specific indexes may result in an error when run on RLV-enabled tables. For
example, contains predicate requires support from TEXT or WORD indexes, neither
of which are supported in RLV-enabled tables. In order to avoid the performance
degradation due to lack of indexes on the RLV side, SAP Sybase IQ may create an ad hoc hash index when these queries
are detected. The query plan will indicate that the store is using the hash
index.
- No DQP support – querying RLV-enabled tables is supported in simplex configurations only.
Note:
Do not make use of a specific row ID when querying. If you make use of a
specific row ID (for doing a join) and select a row ID from another table, the
row ID may not remain consistent.