DB2 10.5 for Linux, UNIX, and Windows

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)
Note:
  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 requests. Any join request that references column-organized tables can be satisfied by retrieving the data from the column-organized tables and performing the join by using row-organized data processing. If the requested join method is HSJOIN, a plan with HSJOIN being pushed down to column-organized data processing can also be used to satisfy the request.
  3. A star join can contain only row-organized tables.