Hello

Have one SELECT query joining (LEFT JOIN) two column tables (2.5 and 56 million rows) without any predicates (full scans) generating resultset of 125 million rows and consume ~50GB in tempspace on runtime.

Next is explain plan output :
Code:
Access Plan:
-----------
        Total Cost:             89986.2
        Query Degree:           16

                  Rows
                 RETURN
                 (   1)
                  Cost
                   I/O
                   |
               2.58055e+06
                 LMTQ
                 (   2)
                 89986.2
                  23707
                   |
               2.58055e+06
                 TBSCAN
                 (   3)
                 84018.3
                  23707
                   |
               2.58055e+06
                 SORT
                 (   4)
                 83865.9
                  23707
                   |
               2.58055e+06
                 CTQ
                 (   5)
                 54399.2
                  23707
                   |
               2.58055e+06
                 GRPBY
                 (   6)
                 53889.1
                  23707
                   |
               2.58055e+06
                 HSJOIN<
                 (   7)
                 53724.6
                  23707
           /-------+-------\
     5.6604e+07          2.58055e+06
       TBSCAN              TBSCAN
       (   8)              (   9)
       46965.6             5301.62
        21164               2543
         |                   |
     5.6604e+07          2.58055e+06
 CO-TABLE: DB2INST1  CO-TABLE: DB2INST1
     AZMMIC_O500         AZMMIC_O100
         Q1                  Q2
This query runtime is 1500-1600 seconds and 2/3 of the exectime spent on doing SORT on ROW-engine (after CTQ operator in plan)

Same query runtime on compressed row-tables is ~500 second with next plan :
Code:
Access Plan:
-----------
        Total Cost:             199024
        Query Degree:           16

              Rows
             RETURN
             (   1)
              Cost
               I/O
               |
           2.58055e+06
             LMTQ
             (   2)
             199024
              60510
               |
           2.58055e+06
             GRPBY
             (   3)
             192785
              60510
               |
           2.58055e+06
             TBSCAN
             (   4)
             192501
              60510
               |
           2.58055e+06
             SORT
             (   5)
             192349
              60510
               |
           2.58055e+06
             HSJOIN<
             (   6)
             162882
              60510
         /-----+------\
   5.66175e+07      2.58055e+06
     TBSCAN           TBSCAN
     (   7)           (   8)
     136910           13400.6
      54584            5926
       |                |
   5.66175e+07      2.58055e+06
 TABLE: DB2INST1  TABLE: DB2INST1
  AZMMIC_O500_R    AZMMIC_O100_R
       Q1               Q2
Is there any method to place SORT under column-engine ?

Env: DB2 10.5.4 on SLES 11SP3, bufferpool 3.2GB, SHEAPTRHS_SHR=8GB, SORTHEAP=4GB

Thank's