Optimization guidelines and profiles for column-organized tables

Optimization guidelines and profiles are supported on column-organized tables. In general, any table access or join request not involving an index scan is allowed.

SQL0437W with reason code 13 is returned when a guideline that references column-organized tables cannot be satisfied. Also, EXP0035W is displayed in the Extended Diagnostic Information section of db2exfmt command output. The following table illustrates the types of optimization requests that are allowed or not allowed for column-organized tables:

Type of request Whether allowed and messages returned
ACCESS (any table access) Allowed
TBSCAN (table scan) 1 Allowed
IXSCAN (index scan) Not allowed (SQL0437W, reason code 13; EXP0035W)
LPREFETCH (list prefetch) Not allowed (SQL0437W, reason code 13; EXP0035W)
IXAND (index ANDing) Not allowed (SQL0437W, reason code 13; EXP0035W)
IOA (index ORing) Not allowed (SQL0437W, reason code 13; EXP0035W)
JOIN (any join) Allowed
HSJOIN (hash join) 2 Allowed
NLJOIN (nested loop join) Allowed
MSJOIN Allowed
STARJOIN (IXA-ANDing star join) 3 Not allowed (SQL0437W, reason code 13; EXP0035W)
  1. You cannot create an index on a column-organized table, and only table scans can be used. Any table access request that requires an index scan cannot be satisfied.
  2. A column-organized table supports only HSJOIN and NLJOIN requests. Any join request that references column-organized tables can be satisfied by retrieving the data from the tables and performing the join by using row-organized data processing. If the requested join method is HSJOIN or NLJOIN, a plan with HSJOIN or NLJOIN being pushed down to column-organized data processing can also be used to satisfy the request, assuming they are eligible considering the type of join predicates being applied. HSJOIN is only eligible if there is at least one equality join predicate, and NLJOIN is only eligible if there are no equality join predicates.
  3. A star join can contain only row-organized tables.