Explain information for column-organized tables

Explain information is captured to support column-organized tables. You can use this information to determine how your application performs when it uses this functionality.

The CTQ plan operator represents the transition between column-organized data processing and row-organized data processing.

The steps that you use to capture the explain information for column-organized tables are the same steps that you use for running queries against row-organized tables.

Set the EXPLAIN mode on by using the CURRENT EXPLAIN MODE special register as follows:
```
db2 SET CURRENT EXPLAIN MODE YES
```
Issue your query against column-organized tables.
Issue the db2exfmt command to format the contents of the explain tables and obtain the access plan. The following example shows you how to use this command against the SAMPLE database:
```
db2exfmt -d sample -1 -o output.exfmt
```

Improving the performance of queries on column-organized tables by using indexes

Some queries that have selective search conditions might run faster when accessing column-organized tables if indexes are used. Unique indexes are implicitly created to support enforced primary and unique key constraints. Starting in Db2® version 11.1 Mod Pack 3 and Fix Pack 3 (11.1.3.3), indexes can also be explicitly created by using the CREATE INDEX statement. Before Db2 11.1.3.3, unique indexes were used for select, update, or delete operations that affect only one row in a column-organized table. An example is shown in the following index access plan:

             Rows 
            RETURN
            (   1)
             Cost 
              I/O 
              |
               1 
            CTQ   
            (   2)
            41.3466 
               6 
              |
               1 
            NLJOIN
            (   3)
            41.3449 
               6 
         /----+-----\
        1              1 
     CTQ            TBSCAN
     (   4)         (   6)
     6.91242        34.4325 
        1              5 
       |              |
        1            98168 
     IXSCAN   CO-TABLE: VICCHANG
     (   5)      /BIC/SZCCUST
     6.91242          Q1
        1 
       |
      98168 
 INDEX: VICCHANG
 /BIC/SZCCUST~0
       Q1

This plan is equivalent to a FETCH-IXSCAN combination that is used to access row-organized data. For index access to column-organized data, row-organized data processing retrieves the rowid from the index by using IXSCAN(5) and passes it to column-organized data processing using CTQ(4). CTQ(4) represents a column-organized table queue that passes data from row-organized data processing to column-organized data processing. TBSCAN(6) locates the columns that are identified by the rowid. TBSCAN(6) might apply additional predicates if necessary, or reapply the IXSCAN predicates in some situations. Specifically, if the table is being accessed under the UR isolation level, or the access is in support of an update or delete operation, the TBSCAN needs to apply only those predicates that were not already applied by the IXSCAN. Otherwise, the TBSCAN needs to reapply all of the IXSCAN predicates. NLJOIN(3) represents the process of retrieving the rowid from row-organized data processing and passing it to the column-organized TBSCAN.

Starting in Db2 11.1.3.3, the FETCH operator for column-organized index scans replaces the use of the previous nested-loop join method for isolation level CS, when a modification state index exists for the table. The nested-loop join method is still used for isolation level UR, if the following conditions are met:

The result of the index access is joined to another column-organized table
The index scan returns at most one row

Otherwise, the FETCH operator is used. A column-organized FETCH can process any number of rows, while the nested-loop join representation of a fetch operation is limited to processing no more than one row. A column-organized FETCH can apply search argument predicates (sargable) and residual predicates just like a row-organized FETCH. A column-organized FETCH can also be used in all of the same contexts as a row-organized FETCH, including the inner loop of a nested-loop join and in correlated subselect queries.

The FETCH operator runs by using row-organized processing even though it is accessing column-organized data. Data that is returned cannot be used for subsequent column-organized processing. The Db2 query optimizer also considers accessing the column-organized table by using a table scan (TBSCAN operator), if that is a less expensive option for processing such as:

Joins
Aggregation
Removal of duplicate rows
Sorting by using column-organized processing

The explain representation for a column-organized FETCH is similar to that of a row-organized FETCH, except for arguments that are not applicable to column-organized processing. The example that is shown previously for a query that uses an index before Db2 11.1.3.3 would appear as the following when a FETCH operator is used:

            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
              1
           FETCH
           (   2)
           17.0787
              1
        /----+----\
       1           98168
    IXSCAN  CO-TABLE: VICCHANG
    (   3)     /BIC/SZCCUST
    6.91242          Q1
       1
      |
     98168
INDEX: VICCHANG
/BIC/SZCCUST~0
      Q1

Indexes on column-organized tables are not supported for the following index operations:

Jump scans
Deferred fetch index plans (index ANDing, ORing, and list prefetch)
Star join and zigzag join
Scan Sharing

Intra-partition parallel index scans are not supported for column-organized tables.

Update and delete operations that use an index scan on a column-organized table are not supported by the FETCH operator. Update and delete operations that affect only a single row are supported by using either index-only access or the nested-loop fetch approach.

Common table expression

A common table expression defines the result of a table that you can specify in the FROM clause of an SQL Statement. Statements with common table expressions against column-organized tables can have more efficient execution plans.

Consider the following query against a column-organized table T1 that has two columns, C1 and C2:

WITH cse(c1,c2) AS (SELECT  t1.c1, MAX(c2) FROM t1 GROUP BY t1.c1)
  SELECT a.c2 FROM cse a, cse b
    WHERE a.c1 = b.c2;

The following sample execution plan correspond to that query.

           Rows        
          RETURN       
          (   1)       
           Cost        
           I/O 
            | 
            25 
           LTQ          
          (   2)   
          2078.07      
            30         
            |          
            25         
           CTQ          
          (   3)       
           2074        
            30         
            |          
            25         
         ^HSJOIN      
          (   4)       
          2073.79      
             30         
           /-+--\       
         25       25    
       TBSCAN   TBSCAN  
       (   5)   (   9)  
       1036.85  1036.85 
         15       15    
         |        |     
         25       25    
        TEMP     TEMP    
       (   6)   (   6)  
       1033.24  1033.24 
         15       15    
         |               
         25            
       GRPBY           
       (   7)          
       1032.45         
         15             
         |           
       50000           
       TBSCAN          
       (   8)
       792.149
         15
         |
        50000
 CO-TABLE: URSU
         T1
         Q1

The execution plan includes the TEMP(6) operator, which materializes the results of the common table expression during column-organized data processing. Operators TBSCAN(5) and TBSCAN(9) scan the output of the TEMP(6) operator and send the data to the HSJN(4) operator. Afterward, the CTQ(3) operator sends the results of the join operation from column-organized data processing to row-organized data processing.

Column-organized sorts

The query optimizer determines where SORT operators are placed in the access plan based on query semantics and costing. Column-organized sorting will be done to satisfy ORDER BY requirements on sub-selects and within OLAP specifications. They will also be used to partition data for OLAP specifications that include the PARTITION BY clause, to allow the OLAP function to be computed in parallel using multiple database agents. For example:

SELECT * FROM tc1 ORDER BY c1,c2
SELECT rank() OVER (PARTITION BY c1 ORDER BY c2) AS rnk, c2 FROM tc1

A column-organized sort is typically executed in parallel using multiple database agents and can use different methods to distribute the data among the agents, depending on the semantics of the SQL statement. The type of parallel sorting method is indicated by the SORTTYPE argument of the SORT operator along with the sort key columns and the sort partitioning columns. The SORTTYPE argument can have the values GLOBAL, PARTITIONED, or MERGE for a column-organized SORT.

A global sort is used when the SQL statement semantics require that the data be globally ordered to satisfy an ORDER BY request, for example. The sorting will be performed by multiple database agents but the final result will be produced by a single agent. For example, the following query has an ORDER BY on columns C1 and C2.

SELECT * FROM tc1 ORDER BY c1, c2

The access plan for this query has one SORT operator:

       Rows
      RETURN
      (   1)
       Cost
       I/O
        |
       1000
       LMTQ 
      (   2)
      351.462
        10
        |
       1000
       CTQ  
      (   3)
      289.471
        10
        |
       1000
      TBSCAN
      (   4)
      288.271
        10
        |
       1000
      SORT 
      (   5)
      278.209
        10
        |
       1000
      TBSCAN
      (   6)
      85.5805
        10
        |
       1000
CO-TABLE: DB2USER
        TC1
        Q1

The SORT operator arguments have SORTTYPE GLOBAL, indicating that it will produce a single stream of sorted data. (Only arguments relevant to this discussion are shown):

	Arguments:
	---------
       SORTKEY : (Sort Key column)
           1: Q1.C1(A)
           2: Q1.C2(A)
       SORTTYPE: (Intra-Partition parallelism sort type)
           GLOBAL

A partitioned sort is used when the sorted data can be used by multiple SQL operations, such as an ORDER BY request and OLAP functions that require partitioned or ordered data. For example, the following query contains 2 OLAP functions that require the data to be partitioned by (C2) and (C2,C3) and the query also has an ORDER BY clause.

	SELECT
	    c1,
	    c2,
	    c3,
	    MAX(c1) OVER (PARTITION BY c2),
	    MAX(c1) OVER (PARTITION BY c2, c3)
	FROM
	    tc1
	ORDER BY
	    c2

The following access plan containing 2 SORT operators is chosen:

       Rows
      RETURN
      (   1)
       Cost
        I/O
        |
       1000
      LMTQ 
      (   2)
      466.38
        10
        |
       1000
      CTQ  
      (   3)
      411.188
        10
        |
       1000
      TBSCAN
      (   4)
      409.588
        10
        |
       1000
      SORT 
      (   5)
      399.527
        10
        |
       1000
      TBSCAN
      (   6)
      288.271
        10
        |
       1000
      SORT 
      (   7)
      278.209
        10
        |
       1000
      TBSCAN
      (   8)
      85.5805
        10
        |
       1000
CO-TABLE: DB2USER
        TC1
        Q1

SORT(7) is executed first and it has the following explain arguments:

       Arguments:
       ---------
       PARTCOLS: (Table partitioning columns)
           1: Q2.C2
       SORTKEY : (Sort Key column)
           1: Q2.C2(A)
           2: Q2.C3(A)
       SORTTYPE: (Intra-Partition parallelism sort type)
           PARTITIONED

SORT(7) is partitioned by ranges of values of C2. Within each sort partition, the data is sorted by columns C2 and C3 This allows both MAX functions to be computed in parallel by multiple agents. The parallel streams with the MAX results are able to maintain order on C2, allowing the ORDER BY C2 to be satisfied by simply merging the parallel streams. SORT(5) merges the streams from each agent to produce one ordered stream, rather than performing a global sort of the input streams. This is indicated by SORTTYPE MERGE in the explain arguments for SORT(5):

       Arguments:
       ---------
       SORTKEY : (Sort Key column)
           1: Q3.C2(A)
       SORTTYPE: (Intra-Partition parallelism sort type)
           MERGE

If this same query didn't have an ORDER BY, a different type of parallel sort method is used.

	SELECT
	    c1,
	    c2,
	    c3,
	    MAX(c1) OVER (PARTITION BY c2),
	    MAX(c1) OVER (PARTITION BY c2, c3)
	FROM
	    tc1

The following access plan with a single SORT is chosen:

       Rows
      RETURN
      (   1)
       Cost
        I/O
        |
       1000
      LTQ  
      (   2)
      421.526
        10
        |
       1000
      CTQ  
      (   3)
      383.134
        10
        |
       1000
      TBSCAN
      (   4)
      288.271
        10
        |
       1000
      SORT 
      (   5)
      278.209
        10
        |
       1000
      TBSCAN
      (   6)
      85.5805
        10
        |
       1000
CO-TABLE: DB2USER
        TC1
        Q1

The explain arguments for SORT(5) indicate that it is a partitioned sort, however the partitioning is done differently than the previous example:

       Arguments:
       ---------
       PARTCOLS: (Table partitioning columns)
           1: Q2.C2
       SORTKEY : (Sort Key column)
           1: Q2.C2(R)
           2: Q2.C3(A)
       SORTTYPE: (Intra-Partition parallelism sort type)
           PARTITIONED

Each sort output stream read by each agent contains a range of values for C2, but the data is not ordered on C2 within each stream. Instead, the data is ordered on C3 within each distinct value of C2. For example:

Table 1. Pertitioned SORT output
Agent 1	Agent 1	Agent 1	Agent 1
C2	C3	C2	C3
5	1	8	6
5	1	8	6
5	2	8	7
5	2	8	7
1	4	2	1
1	5	2	2

Since the window specification for both OLAP functions is PARTITION BY rather than ORDER BY, and the outer sub-select doesn't have an ORDER BY clause, strict order on C2 and C3 does not need to be produced by the SORT. Distinct values of C2 just need to be processed by the same database agent. For example, all rows with values C2 = 5 must be processed by the same agent in order to properly determine the maximum value of C1 for that group of values. Limiting the sorting to values of C3 within distinct values of C2 reduces the memory required to perform the sort. Performance might also be improved because partitions can be emitted out of sequence, avoiding the synchronization overhead to emit them in order.

Since the data is not strictly ordered on C2, the order indicator is set to "R" indicating that it is randomly ordered.

A single SORT operation can provide the partitioning needed for multiple OLAP functions if the partitioning specifications are identical or are proper subsets of each other. When this is the case, the SORT will be partitioned on the the smallest set of columns that is a proper subset of all other OLAP partitioning specifications.