CONCATENATION 引发的性能问题

背景是在一台11gR2的机器上，开发反映一个批处理比以前慢了3倍。经过仔细查看该SQL的执行计划，发现由于SQL中使用了or，导致CBO走出了一个非常糟糕的CONCATENATION路径。

no_expand提示的说明是 

The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, 

the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it.

use_concat提示的说明是

The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. 

Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.

 
为了重现这个问题，必须使用/*+ use_concat */来模拟。

explain plan for SELECT  /*+ use_concat */
           20130620,
           B.mgr_code   ,
           B.mgr_name   ,