尽量减少union all 的使用

        通过我自己做报表,以及和大家的交流,我发现大家在报表中使用union all 还是比较多的. 一般情况下,因为需要不同的条件,关联不同的表,所以写起union all 语句来还是很方便,但是想把这些逻辑都在一个语句中实现却有些困难. 而且,大家普遍认为,因为union all 中是有条件的,因此执行时,并不是整个union all 语句在执行,而是 union all 的部分语句在执行而已.

        我认为这个其实是个错误的概念: 因为 所谓的条件也是在sql 语句中的一个条件, oralce 没法根据某个where 条件来判断是否不执行一段语句, 实际上,oracle 会完全按照所得出的执行计划来执行,控制的条件肯定会被执行到的,但是可能之前已经执行了很多没必要的操作,即使在所在的语句块中,控制条件能被早先执行到,如果控制的条件不是返回false,后面的条件仍然会被执行,这个时候,表仍然在被扫描,嵌套仍然在继续, 所以这种重复执行的代价是非常大的,因此 建议尽量减少使用union all 的使用,而是尽可能的把语句简单化, 可以采用如下的写法来避免union all 的过多使用.


(  ( constant_condition1  and  condition1 )
   or
   (  constant_condition2  and  condition2)
)

constant_condition1 和 constant_condition2 是 常量表达式,这里即为我们的控制条件;
condition1 和condition2 分别为 constant_condition1 和 constant_condition2 常量条件下的 条件.
一般来说,几个常量表达式的合集应该是个全集.


比如,为了控制 科目明细帐的单币种和多币种逻辑, 我只需要 加入这样的判断即可,而不用 写两个语句来union all. 其中 cp_currency_code_flag 是我定义的一个占位列,我在before report 中会根据逻辑来给这个变量来赋值.

 and (
               ( :cp_currency_code_flag = 'SINGLE-CODE' and gjh.currency_code = :p_currency_code )
                OR
                ( :cp_currency_code_flag != 'SINGLE-CODE'  )
    )






附录:

如下的例子大致反映出union all 重复执行的代价.

我做了个对比,下面两个语句的逻辑意思实际上是一样的,但是用了union all 之后,要多访问一次表, 结果逻辑读的代价是第一个语句的2倍. 解释union all 的下半句返回为空.




SQL> select count(*)  from  test2 where object_name like 'F%';

 COUNT(*)
---------
       34


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST2'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        134  consistent gets
          0  physical reads
          0  redo size
        182  bytes sent via SQL*Net to client
        251  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(*)  from test2 where object_name like 'F%' and object_id <= 300000
  2  union all
  3  select count(*)  from test2 where object_name like 'F%' and object_id > 300000  

 COUNT(*)
---------
       34
        0


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   UNION-ALL
   2    1     SORT (AGGREGATE)
   3    2       TABLE ACCESS (FULL) OF 'TEST2'
   4    1     SORT (AGGREGATE)
   5    4       TABLE ACCESS (FULL) OF 'TEST2'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        268  consistent gets
          0  physical reads
          0  redo size
        195  bytes sent via SQL*Net to client
        251  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>