帮我优化一下这个SQL,

从计划上都是表扫描，尤其还是nest loop join。
就像hczzhong所说，建议把你的标量子查询改写为left join。标量子查询在很大程度上限制了DB2只会选用nljoin。比如改写成这样:

SELECT *
FROM   (SELECT Rownumber() OVER()  AS RowNumber_,
               Row_.*
        FROM   (SELECT a.cbbh,
                       a.zwcm,
                       a.ywcm,
                       a.jcrq,
                       a.zd,
                       a.zjgl,
                       b.cjgzwmc,
                       c.cbzlzwmc,
                       (SELECT DISTINCT jgmc
                        FROM   s_dtxt_xjhsjg
                        WHERE  a.jgdm = jgdm) AS jgmc,
                       d.gjzwmc,
                       (CASE 
                          WHEN hcnhcbz = '1' THEN '海船'
                          ELSE '内河船'
                        END) AS hcnhcbz
                FROM   vw_All_cbjbxx a
                       LEFT JOIN s_ztzd_cjg b
                         ON a.cjgdm = b.cjgdm
                       LEFT JOIN s_ztzd_cbzl c
                         ON a.cbzldm = c.cbzldm
                       LEFT JOIN s_ztzd_gj d
                         ON a.gjdm = d.gjdm
                WHERE  1 = 1) AS Row_) AS temp_
WHERE  RowNumber_ BETWEEN 10 + 1  AND 10 + 10;

另一个例子，我们可以把distinct操作压到join之前完成。

SELECT *
FROM   (SELECT Rownumber() OVER()  AS RowNumber_,
               Row_.*
        FROM   (SELECT a.cbbh,
                       a.zwcm,
                       a.ywcm,
                       a.jcrq,
                       a.zd,
                       a.zjgl,
                       b.cjgzwmc,
                       c.cbzlzwmc,
                       e.jgmc,
                       d.gjzwmc,
                       (CASE 
                          WHEN hcnhcbz = '1' THEN '海船'
                          ELSE '内河船'
                        END) AS hcnhcbz
                FROM   vw_All_cbjbxx a
                       LEFT JOIN s_ztzd_cjg b
                         ON a.cjgdm = b.cjgdm
                       LEFT JOIN s_ztzd_cbzl c
                         ON a.cbzldm = c.cbzldm
                       LEFT JOIN s_ztzd_gj d
                         ON a.gjdm = d.gjdm
                       LEFT JOIN (SELECT   jgmc
                                  FROM     s_dtxt_xjhsjg
                                  GROUP BY jgdm) e
                         ON e.jgdm = a.jgdm
                WHERE  1 = 1) AS Row_) AS temp_
WHERE  RowNumber_ BETWEEN 10 + 1   AND 10 + 10;

你没有给出 db2exfmt的输出，不知道表的数据量和索引情况，但以下这些索引应该会提高查询性能：
S_ZTZD_CJG (CJGDM,CJGZWMC)
S_ZTZD_CBZL(CBZLDM,CBZLZWMC)
S_DTXT_XJHSJG(JGDM,JGMC)
S_ZTZD_GJ(GJDM,GJZWMC)

还有要注意的是在进行多个left join的时候，DB2有时候选择的join order不是最优的，
你可以通过调整SQL语句里的join顺序来引导optimizer。

另外，就像hczzhong所提到的那样，你的row_number没有选择排序字段，
这回导致分页查询的顺序不是有保证的。
在确定了排序字段后，如果子查询的结果集比较大，排序仍可能会导致效率低下，
你可以在排序字段上建一个索引，然后结合optimize for xx rows让DB2去选择索引扫描
而避免排序。收起

1.VW_ALL_CBJBXX如果是表的话，看SQL关联子查询应该都是到维表的关联，定义PK/FK，用left join,如果能够join到多条，原来的SQL就会出错，这也是维表必须加上PK的原因。如果是视图，如何定义FK就不好说了
2.row_number没有任何排序依据，出来的结果没啥保证，可能翻到重复的数据，也可能有的数据永远翻不到。快点慢点也没啥区别收起

Estimated Cost = 484647.343750
Estimated Cardinality = 35355.011719

Access Table Name = DB2INST1.S_DTYW_CBJBXX  ID = 8,115
|  #Columns = 11
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  May participate in Scan Sharing structures
|  Scan may start anywhere and w|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  #Predicates = 1
Nested Loop Join
|  Piped Inner
|  Access Table Name = DB2INST1.S_ZTZD_CBZL  ID = 6,906
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
Nested Loop Join
|  Piped Inner
|  Access Table Name = DB2INST1.S_DTXT_XJHSJG  ID = 6,774
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
|  Insert Into Sorted Temp Table  ID = t1
|  |  #Columns = 1
|  |  #Sort Key Columns = 1
|  |  |  Key 1: (Ascending)
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 4.000000
|  |  |  Row Width = 188
|  |  Piped
|  |  Duplicate Elimination
|  Access Temp Table  ID = t1
|  |  #Columns = 1
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Nested Loop Join
|  Piped Inner
|  Access Table Name = DB2INST1.S_ZTZD_GJ  ID = 6,910
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
Nested Loop Join
|  Piped Inner
|  Access Table Name = DB2INST1.S_ZTZD_CJG  ID = 6,908
|  |  #Columns = 1
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  #Predicates = 1
Residual Predicate(s)
|  #Predicates = 2
Return Data to Application
|  #Columns = 12

End of section收起

SELECT部分的子查询速度是很慢，多慢的程序取决于查询返回的记录条数。
请给出该SQL的执行计划看慢在什么地方？收起

plz give the full execution plan of "rownumber_ between 10 + 1 and 10 + 10" and "rownumber_ <= 10"。收起