oracle 谓词推入失效,谓词推入 - Oracle开发 - ITPUB论坛－中国专业的IT技术社区

例子：

create table t as select * from all_objects;

create index tx on t(object_id);

create view vw_test as select t1.object_id,t2.object_name from t t1,t t2 where t1.object_id=t2.object_id;

select * from vw_test where object_id between 100 and 20000;

Execution Plan

----------------------------------------------------------

Plan hash value: 1825706895

---------------------------------------------------------------------------------------------

| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |      | 18049 |   634K|   399   (0)| 00:00:01 |

|*  1 |  HASH JOIN                           |      | 18049 |   634K|   399   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                   | TX   | 18049 | 90245 |    42   (0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    | 18049 |   546K|   357   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN                  | TX   | 18049 |       |    42   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

2 - access("T1"."OBJECT_ID">=100 AND "T1"."OBJECT_ID"<=20000)

4 - access("T2"."OBJECT_ID">=100 AND "T2"."OBJECT_ID"<=20000)

此时谓词被推进但最后还是用了HASH JOIN。