下面这个sql能不能优化啊等高手解答解决办法

下面这个sql能不能优化啊，急等高手解答
计算CY closing day 前10日, 每日的累计交柜量的百分比的规律(D-10包括10天以前)？

select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code 
  and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-27','yyyy/mm/dd HH:MI:SS')  
union all  
select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code 
  and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-26','yyyy/mm/dd HH:MI:SS') 
  union all 
  select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code 
  and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-25','yyyy/mm/dd HH:MI:SS')  
  union all 
  select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code 
  and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-24','yyyy/mm/dd HH:MI:SS')  
  union all
  select count(*) from vessel_move_log a , voyage_member b where a.voyid_company_code = b.voyid_company_code and a.voyid_vessel_code = b.voyid_vessel_code 
  and a.voyid_voyage_code = b.voyid_voyage_code and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2' and a.service_code ='LPAW' and b.ob_cntr_close_datetime <=to_date('2011-09-23','yyyy/mm/dd HH:MI:SS') 
union all .....

[解决办法]
这样的写法怎么执行效率会高呢？
第一种：create table #temp ---临时表
如果在应用程序用的话最好还是采取这种方式效率还是比较高的
然后把查询的结果放在临时表中，在应用程序中调用临时表中的数据，这样的效率会高些的。


如果单纯的查询时间段的数据就可以无需用union all

[解决办法]

select count(*)
from vessel_move_log a, voyage_member b
 where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-27', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
 where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-26', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
 where a.voyid_company_code = b.voyid_company_code

and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-25', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
 where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-24', 'yyyy/mm/dd HH:MI:SS')
union all
select count(*)
from vessel_move_log a, voyage_member b
 where a.voyid_company_code = b.voyid_company_code
and a.voyid_vessel_code = b.voyid_vessel_code
and a.voyid_voyage_code = b.voyid_voyage_code
and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'
and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'
and a.service_code = 'LPAW'
and b.ob_cntr_close_datetime <=
to_date('2011-09-23', 'yyyy/mm/dd HH:MI:SS')
除了索引以外，其它没什么好办法来优化了。
[解决办法]
不是很明白这条SQL是完成什么功能的，简单分析了一下，得到的结果集是27日的数据每条出现1次，26日的数据每条出现2次，25日的数据每条出现3次，后面的日期以此类推。。。。。。

如果楼主的需求就是如此，那么这条SQL没有优化的可能。如

楼主提到要统计每日交柜量百分比，这个百分比的分母是什么？

最后，提供给楼主1个可以统计每日数量的SQL以供参考。

SQL code

select count(*)from (  select trunc(b.ob_cntr_close_datetime) D1   from vessel_move_log a , voyage_member b   where a.voyid_company_code = b.voyid_company_code   and a.voyid_vessel_code = b.voyid_vessel_code      and a.voyid_voyage_code = b.voyid_voyage_code     and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'    and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'     and a.service_code ='LPAW'     and b.ob_cntr_close_datetime <= SYSDATE - 10 )group by D1 ;
[解决办法]
上面的SQL有错误，在此更正。Ps:SQL没有经过调试，楼主使用前自己调试下。
SQL code
select count(*)from (  select trunc(b.ob_cntr_close_datetime) D1   from vessel_move_log a , voyage_member b   where a.voyid_company_code = b.voyid_company_code   and a.voyid_vessel_code = b.voyid_vessel_code      and a.voyid_voyage_code = b.voyid_voyage_code     and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'    and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'     and a.service_code ='LPAW'     and b.ob_cntr_close_datetime >= trunc(SYSDATE - 10) )group by D1 ;
[解决办法]
SQL code
with t as (select count(*) base_sum from  vessel_move_log a,       voyage_member b where a.voyid_company_code = b.voyid_company_code   and a.voyid_vessel_code = b.voyid_vessel_code   and a.voyid_voyage_code = b.voyid_voyage_code   and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'   and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'   and a.service_code = 'LPAW'   and b.ob_cntr_close_datetime < to_date('2011/09/18', 'yyyy/mm/dd'))select t.base_sum + sum_add_by_dayfrom t,  (select sum(1) over(order by trunc(t.cntr_close_datetime))  from vessel_move_log a,       voyage_member b,       (select to_date('2011/09/17', 'yyyy/mm/dd') + rownum as cntr_close_datetime          from dual        connect by rownum <= 10) t where a.voyid_company_code = b.voyid_company_code   and a.voyid_vessel_code = b.voyid_vessel_code   and a.voyid_voyage_code = b.voyid_voyage_code   and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'   and a.CRANE_MOVE_CONFIRMATION_MODE = 'L2'   and a.service_code = 'LPAW'   and trunc(b.ob_cntr_close_datetime) = trunc(t.cntr_close_datetime));
------解决方案-------------------- 
 
前面那个可能会产生大量重复记录，小做修改如下，代码没有测试。
主要思想就是使用sum() over() 函数进行累加计算
SQL code
with t as (select count(*) base_sum from  vessel_move_log a,       voyage_member b where a.voyid_company_code                               = b.voyid_company_code    and a.voyid_vessel_code                                   = b.voyid_vessel_code    and a.voyid_voyage_code                                  = b.voyid_voyage_code    and a.MOVEMENT_CNTR_INTERCHANGE_MODE       = 'PI'    and a.CRANE_MOVE_CONFIRMATION_MODE          = 'L2'    and a.service_code                                         = 'LPAW'    and b.ob_cntr_close_datetime  < to_date('2011/09/18', 'yyyy/mm/dd'))select t.base_sum + sum_add_by_day  from t,  (select sum(1) over(order by t.cntr_close_datetime)  from vessel_move_log a,       voyage_member b,       (select to_date('2011/09/17', 'yyyy/mm/dd') + rownum as cntr_close_datetime          from dual        connect by rownum <= 10) t where a.voyid_company_code                          = b.voyid_company_code   and a.voyid_vessel_code                              = b.voyid_vessel_code   and a.voyid_voyage_code                              = b.voyid_voyage_code   and a.MOVEMENT_CNTR_INTERCHANGE_MODE = 'PI'   and a.CRANE_MOVE_CONFIRMATION_MODE   = 'L2'   and a.service_code                                     = 'LPAW'   and b.ob_cntr_close_datetime >= t.cntr_close_datetime    and b.ob_cntr_close_datetime < t.cntr_close_datetime + 1);
[解决办法]
探讨

这个sum_add_by_day表示什么意思哇

[解决办法]
修改了下可能的语法错误，私信发给你了