通过我自己做报表,以及和大家的交流,我发现大家在报表中使用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>
我认为这个其实是个错误的概念: 因为 所谓的条件也是在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>