oracle 谓词是什么意思,各位有没关于过滤器谓词和访问谓词的介绍啊。

好的 ，执行计划是这样的。表里我造了1W条数据测试。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE        11.1.0.6.0        Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> explain plan for select * from tmp1  left join tmp2 on tmp1.id=tmp2.id and fee =20;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2592321047

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    39 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN OUTER   |      |     1 |    39 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TMP1 |     1 |    13 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| TMP2 |     1 |    26 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - access("TMP1"."ID"="TMP2"."ID"(+))

3 - filter("FEE"(+)=20)

16 rows selected

SQL> explain plan for select * from tmp1  left join tmp2 on tmp1.id=tmp2.id and fee in (20,30);

Explained

SQL> select * from table(dbms_xplan.display)

2  ;

PLAN_TABLE_OUTPUT

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

Plan hash value: 1135981853

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

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

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

|   0 | SELECT STATEMENT    |      |     1 |    39 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER |      |     1 |    39 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | TMP1 |     1 |    13 |     2   (0)| 00:00:01 |

|   3 |   VIEW              |      |     1 |    26 |     2   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| TMP2 |     1 |    26 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("TMP1"."ID"="TMP2"."ID" AND ("FEE"=20 OR "FEE"=30))

16 rows selected

SQL> explain plan for select * from tmp1  left join tmp2 on tmp1.id=tmp2.id or fee =20;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1135981853

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

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

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

|   0 | SELECT STATEMENT    |      |     1 |    39 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER |      |     1 |    39 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | TMP1 |     1 |    13 |     2   (0)| 00:00:01 |

|   3 |   VIEW              |      |     1 |    26 |     2   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL| TMP2 |     1 |    26 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - filter("TMP1"."ID"="TMP2"."ID" OR "FEE"=20)

16 rows selected