QP Output Details for RLV Tables

The Optimization Note, Condition 1 RLV Index and Output 1 RLV Index fields of query plan output details give insight as to how querying works with row-level versioning.

In the query plan output details for the leaf node:

Optimization Note – indicates if an on-demand hash index was created for the data in the RLV store.
Condition 1 RLV Index – describes which index was used for predicate execution.
Output 1 RLV Index – lists the RLV indexes present on a column.

Query that Uses Flat FP Index for Execution

SELECT * from test_char 
WHERE c1>1;

If this query were run, in the query plan output details, the Condition 1 RLV Index field would display FP, indicating that the query used a Flat FP index for predicate execution. The Output 1 RLV Index field would also display FP, indicating that only an FP index was present on c1.

Query that Creates Hash Index During Execution

SELECT * from R1KD100 
WHERE R1KD100.c1 in (SELECT R100D100.c1 FROM R100D100 WHERE R1KD100.c1)
      = R100D100.c1)

If this query were run, in the query plan output details, the Optimization Note field would indicate that a hash index was created for RLV data. The Output 1 RLV Index field would display FP, Hash, indicating that there were two indexes present on c1.

Query that Uses Previously-Created Hash Index for Execution

SELECT * from R100D100
        WHERE c1 = 1;

If this query were run after the previous query, and the hash index still existed on R100D100.c1, in the query plan output details, the Condition 1 RLV Index field would display Hash. ( Whenever a hash index exists on a column, it is always preferred over a Flat FP Index for predicate execution on the that column). The Output 1 RLV Index field would display FP, Hash, indicating that there were two indexes present on c1.

Query that Creates Partitioned Hash Index During Execution

SELECT * from hash1, hash2
WHERE hash1.c1 = hash2.c1;    
// hash1 and hash2 are hash partitioned on c1

Suppose tables hash1 and hash2 are equi-partitioned tables (both hash partitioned on column c1), and the join condition is on column c1. If this query were run, the IQ query optimizer would create a partitioned index on both the tables. In the query plan output details for each leaf, the Optimization Note field would indicate that a partitioned-index was created for the RLV store data.