IT29458: POSSIBLE PERFORMANCE PROBLEMS FOR INSERT STATEMENTS WITH COMMON-TABLE EXPRESSIONS

Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• If an Insert statement references common table expressions,
  either user specified or internally generated by the Db2
  compiler, and the common table expressions reference correlated
  expressions, a TEMP might be introduced in the execution plan.
  This in turn might prevent index access and other optimizations.
  
  Example statement and execution plan:
  
  create table it1(c1 int, c2 int) organize by row;
  create table it2(c1 int, c2 int) organize by row;
  create table itgt(c1 int, c2 int) organize by row;
  create index xit1 on it1 (c2);
  
  create view iv1 (c1, c2) as
  select c1, c2 from it1 where not exists (select * from it2 where
  it1.c1 = it2.c1);
  
  insert into itgt(c1) select (select c1 from iv1) from iv1 where
  c2 < 3;
  
  
                             Rows
                            RETURN
                            (   1)
                             Cost
                              I/O
                              |
                          0.00231481
                            INSERT
                            (   2)
                            97.8006
                               6
                         /----+----\
                   0.00231481        144
                     NLJOIN    TABLE: URSU
                     (   3)         ITGT
                     90.4978         Q7
                        5
                   /---+---\
                  1      0.00231481
               TBSCAN      TBSCAN
               (   4)      (   9)
               41.7894     41.7895
                  2           2
                 |           |
             0.00694444  0.00694444
               TEMP        TEMP
               (   5)      (   5)
               40.5222     40.5222
                  2           2
                 |
             0.00694444
               HSJOINx
               (   6)
               40.0641
                  2
           /-----+------\
         144              144
       TBSCAN           TBSCAN
       (   7)           (   8)
       18.7763          18.7763
          1                1
         |                |
         144              144
  TABLE: URSU      TABLE: URSU
         IT2              IT1
         Q2               Q1
  

Local fix

• To allow replication of common table expressions for Insert
  statements, apply the following guideline:
  
  insert into itgt(c1)
  select (select c1 from iv1)
  from iv1 where c2 < 3
  /* <OPTGUIDELINES>
     <REGISTRY>
          <OPTION NAME='DB2_EXTENDED_OPTIMIZATION'
  VALUE='BREAK_CORR_CSE ON'/>
     </REGISTRY>
  </OPTGUIDELINES> */
  ;
  
  
  Execution plan after the guideline.
  
                                        Rows
                                       RETURN
                                       (   1)
                                        Cost
                                         I/O
                                         |
                                      0.0208333
                                       INSERT
                                       (   2)
                                       81.2561
                                          5
                                    /----+----\
                               0.0208333        144
                                NLJOIN    TABLE: URSU
                                (   3)         ITGT
                                73.9529         Q10
                                   4
                   /--------------+---------------\
                  1                              0.0208333
               HSJOINx                            HSJOINx
               (   4)                             (   7)
               39.7646                            34.1704
                  2                                  2
           /-----+------\                    /------+-------\
         144              144              144                48
       TBSCAN           TBSCAN           TBSCAN             FETCH
       (   5)           (   6)           (   8)             (   9)
       18.7763          18.7763          18.7763
  13.9712
          1                1                1                  1
         |                |                |
  /---+----\
         144              144              144          48
  144
  TABLE: URSU      TABLE: URSU      TABLE: URSU      IXSCAN
  TABLE: URSU
         IT2              IT1              IT2        (  10)
  IT1
         Q5               Q6               Q1         4.90264
  Q2
                                                         0
                                                        |
                                                        144
                                                  INDEX: URSU
                                                       XIT1
                                                        Q2
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * all                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.1.4.7                                          *
  ****************************************************************
  

Problem conclusion

• Upgrade to 11.1.4.7
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  IT39621

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RB10 PSN

     UP