在升级到Oracle 11g之后我无法优化Oracle查询,这个问题开始让我有点生气.

请注意,此问题现已完全编辑,因为在创建简单的测试用例后我有更多信息.原始问题可在此处获取：https://stackoverflow.com/revisions/12304320/1.

这个问题是当连接两个表时,其中一个表在日期列上有一个条件,如果查询连接到远程表,则不会发生绑定查看.

这是一个帮助重现问题的测试用例.首先设置两个源表.第一个是日期列表,是本月的第一个,可追溯到三十年

create table mike_temp_etl_control
as 
select
  add_months(trunc(sysdate,'MM'),1-row_count) as reporting_date
from (
  select level as row_count
  from dual
  connect by level < 360
);

然后一些数据来自dba_objects：

create table mike_temp_dba_objects as
select owner,object_name,subobject_name,object_id,created
from dba_objects
union all
select owner,created
from dba_objects;

然后创建一个空表来运行数据到：

create table mike_temp_1
as
select 
  a.OWNER,a.OBJECT_NAME,a.SUBOBJECT_NAME,a.OBJECT_ID,a.CREATED,b.REPORTING_DATE
from 
  mike_temp_dba_objects a
  join mike_temp_etl_control b on (
      b.reporting_date between add_months(a.created,-24) and a.created)
  where 1=2;

然后运行代码.您可能需要创建更大的版本mike_temp_dba_objects以减慢查询速度(或使用其他方法来获取执行计划).在查询运行时,我通过运行select *从会话中获取执行计划
来自不同会话的表(dbms_xplan.display_cursor(sql_id =>’xxxxxxxxxxx’)).

declare
  pv_report_start_date date := date '2002-01-01';
  v_report_end_date date := date '2012-07-01';

begin

  INSERT /*+ APPEND */
  INTO mike_temp_5
  select 
    a.OWNER,b.REPORTING_DATE
from 
  mike_temp_dba_objects a
  join mike_temp_etl_control b on (
    b.reporting_date between add_months(a.created,-24) and a.created)
  cross join dual@emirrl -- This line causes problems...
where 
  b.reporting_date between add_months(pv_report_start_date,-12) and v_report_end_date;

  rollback;  
end;

通过在查询中使用远程表,mike_temp_etl_control表的基数估计完全错误,并且似乎没有发生绑定偷看.

上述查询的执行计划如下所示：

---------------------------------------------------------------------------------------
| Id  | Operation                | Name                  | Rows  | Bytes | Cost (%cpu)|
---------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                       |       |       |   373 (100)|
|   1 |  LOAD AS SELECT          |                       |       |       |            |
|*  2 |   FILTER                 |                       |       |       |            |
|   3 |    MERGE JOIN            |                       |     5 |   655 |   373  (21)|
|   4 |     SORT JOIN            |                       |  1096 |   130K|   370  (20)|
|   5 |      MERGE JOIN CARTESIAN|                       |  1096 |   130K|   369  (20)|
|   6 |       REMOTE             | DUAL                  |     1 |       |     2   (0)|
|   7 |       BUFFER SORT        |                       |  1096 |   130K|   367  (20)|
|*  8 |        TABLE ACCESS FULL | MIKE_TEMP_DBA_OBJECTS |  1096 |   130K|   367  (20)|
|*  9 |     FILTER               |                       |       |       |            |
|* 10 |      SORT JOIN           |                       |     2 |    18 |     3  (34)|
|* 11 |       TABLE ACCESS FULL  | MIKE_TEMP_ETL_CONTROL |     2 |    18 |     2   (0)|
---------------------------------------------------------------------------------------

如果我然后用本地版本替换远程双,我得到正确的基数(139而不是2)：

-------------------------------------------------------------------------------------
| Id  | Operation              | Name                  | Rows  | Bytes | Cost (%cpu)|
-------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |                       |       |       | 10682 (100)|
|   1 |  LOAD AS SELECT        |                       |       |       |            |
|*  2 |   FILTER               |                       |       |       |            |
|   3 |    MERGE JOIN          |                       |   152K|    19M| 10682   (3)|
|   4 |     SORT JOIN          |                       |   438K|    51M| 10632   (2)|
|   5 |      NESTED LOOPS      |                       |   438K|    51M|   369  (20)|
|   6 |       FAST DUAL        |                       |     1 |       |     2   (0)|
|*  7 |       TABLE ACCESS FULL| MIKE_TEMP_DBA_OBJECTS |   438K|    51M|   367  (20)|
|*  8 |     FILTER             |                       |       |       |            |
|*  9 |      SORT JOIN         |                       |   139 |  1251 |     3  (34)|
|* 10 |       TABLE ACCESS FULL| MIKE_TEMP_ETL_CONTROL |   139 |  1251 |     2   (0)|
-------------------------------------------------------------------------------------

所以,我想问题是如何才能得到正确的基数？这是Oracle的错误还是预期的行为？