你的问题跟闭包传递有关.
a=b and b=c , 实际上可以推到出a=c,可oracle偏偏犯傻.
改写这样:
/* Formatted on 2016/03/16 14:52:58 (QP5 v5.252.13127.32867) */
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
/*left*/
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu' AND a.object_id = c.object_id;
--执行计划就ok了.
Plan hash value: 1432733351
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| | | | |
| 1 | NESTED LOOPS | | 1 | 634 | 11 (10)| 00:00:01 | | | |
| 2 | NESTED LOOPS | | 1 | 414 | 3 (0)| 00:00:01 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 207 | 1 (0)| 00:00:01 | | | |
|* 4 | INDEX RANGE SCAN | IDX_FWEKL | 1 | | 1 (0)| 00:00:01 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T_OTHER_TABLE | 1 | 207 | 2 (0)| 00:00:01 | | | |
|* 6 | INDEX RANGE SCAN | IDX_DKWQEL | 1 | | 1 (0)| 00:00:01 | | | |
|* 7 | VIEW PUSHED PREDICATE | | 1 | 220 | 8 (13)| 00:00:01 | | | |
|* 8 | WINDOW SORT PUSHED RANK | | 877 | 177K| 8 (13)| 00:00:01 | 199K| 199K| |
|* 9 | FILTER | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 877 | 177K| 7 (0)| 00:00:01 | | | |
|* 11 | INDEX RANGE SCAN | IDX_ASKDJE | 351 | | 1 (0)| 00:00:01 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A9674253
3 - SEL$A9674253 / A@SEL$1
4 - SEL$A9674253 / A@SEL$1
5 - SEL$A9674253 / C@SEL$2
6 - SEL$A9674253 / C@SEL$2
7 - SEL$8E13D68A / from$_subquery$_001@SEL$3
8 - SEL$8E13D68A
10 - SEL$8E13D68A / T@SEL$4
11 - SEL$8E13D68A / T@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OWNER"='kudfweu')
6 - access("A"."OBJECT_ID"="C"."OBJECT_ID")
7 - filter("RN"=1)
8 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."OBJECT_ID" ORDER BY INTERNAL_FUNCTION("T"."CREATED") DESC )<=1)
9 - filter("C"."OBJECT_ID"="A"."OBJECT_ID")
11 - access("T"."OBJECT_ID"="A"."OBJECT_ID")