406/350
select * from (
select rownumber() over() as rownumber_, row_.*
from (
select distinct alias_sp_cms_info.c_node_id as id, alias_file.c_type_id as type,                 

alias_sp_cms_info.c_prop_top_level_s as top_level, alias_sp_cms_info.c_prop_order_num_s as order_num
from t_file_permission_split alias_fp, t_file_cms_info_s alias_sp_cms_info, t_file  alias_file
where (
     (
     (
     (
     (
     (
     (
     (
       (alias_sp_cms_info.c_path like ?)
and (alias_sp_cms_info.c_prop_validate_time_s <= ?)
     )
and  (
    (alias_sp_cms_info.c_prop_invalidate_time_s is null)
or (alias_sp_cms_info.c_prop_invalidate_time_s > ?)
      )
        )
and (alias_sp_cms_info.c_prop_character_s <> ?)
   )  
and ((alias_file.c_status = ?)
or (alias_file.c_status = ?))
   )
and ((alias_sp_cms_info.c_prop_info_test_s is null)
or (alias_sp_cms_info.c_prop_info_test_s = ?))
   )
and (alias_sp_cms_info.c_prop_character_s = alias_sp_cms_info.c_prop_character_s)
   )
and (alias_sp_cms_info.c_node_id = alias_file.c_id)
   )
and
   (
    (alias_sp_cms_info.c_node_id = alias_fp.c_file_id)
and exists
   (select c_permission_id from t_permission_set where c_permission_id = alias_fp.C_PERMISSION_ID and c_allow_type=0 and

((c_owner_type = 'user' and c_owner_id = 2)
or (c_owner_type = 'group' and c_owner_id in (937, 938))))
and not exists (select c_permission_id from t_permission_set where c_permission_id = alias_fp.C_PERMISSION_ID and

c_allow_type=1 and alias_fp.C_PERMISSION_TYPE = 1 and ((c_owner_type = 'user' and c_owner_id = 2) or (c_owner_type = 'group'

and c_owner_id in (937, 938)))))) order by alias_sp_cms_info.c_prop_top_level_s desc, alias_sp_cms_info.c_prop_order_num_s

desc ) as row_ ) as temp_ where rownumber_ <= ?

不知道这条语句与什么优化的空间没有

建议你把执行路径和索引情况分析分析
$ db2expln -d dbname -u db2inst1 password -t -g -f select.sql > explain.out

谢谢.好的

复杂的东西建议分几步做。

分析执行计划db2expln
建立合适的索引db2advis
如果是存储过程，建议分开几步来做

太复杂的,可使用存储过程.

其实很简单的一句话．就是where 条件复杂点