关于HINT调整的问题

toniz

5楼能否贴下你的10053事件。


我重新安装了下面版本的oracle.

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. ----------------------------------------------------------------
   
5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
   
6. PL/SQL Release 10.2.0.1.0 - Production
   
7. CORE        10.2.0.1.0        Production
   
8. TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
   
9. NLSRTL Version 10.2.0.1.0 - Production
   

复制代码

但是发现只要有OR，就无法使用HASH JOIN。而且按照你提供的执行计划，我编写下面的语句执行：

1. select count(*) from tmp1 join tmp2 on tmp1.id(+)=tmp2.id and (tmp2.FEE(+)=20 OR tmp2.FEE(+)=30)

复制代码

提示是ORA-01719错误。

1. Cause: An outer join appears in an or clause.
   
2. Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select where (B)).

复制代码

然后用1楼的用例再看他的10053计划，如附件。第一句依然是不会去检验HASH JOIN的。如附件。所以我两个版本的结果是一致的。

toniz

http://structureddata.org/2008/0 ... -and-lateral-views/

从这文章得到了启发，虽然他只是描述了这几个查询的不同和转换，没有明确说明ORACLE不去做这个判断的原因。


个人认为是：

1. Query block (08C058D4) before join elimination:
   
2. SQL:******* UNPARSED QUERY IS *******
   
3. SELECT "TMP1"."ID" "QCSJ_C000000000300000","TMP1"."FEE" "QCSJ_C000000000300002","from$_subquery$_004"."ID_1" "ID","from$_subquery$_004"."FEE_0" "FEE" FROM "BI"."TMP1" "TMP1", LATERAL( (SELECT "TMP2"."FEE" "FEE_0","TMP2"."ID" "ID_1" FROM "BI"."TMP2" "TMP2" WHERE "TMP1"."ID"="TMP2"."ID" AND ("TMP2"."FEE"=20 OR "TMP2"."FEE"=30)))(+) "from$_subquery$_004"
   
4. Query block (08C058D4) unchanged
   
5. CVM:   Merging SPJ view SEL$2 (#0) into SEL$3 (#0)
   
6. Query block (08C0A6B4) before join elimination:
   
7. SQL:******* UNPARSED QUERY IS *******
   
8. SELECT COUNT(*) "COUNT(*)" FROM "BI"."TMP1" "TMP1", LATERAL( (SELECT "TMP2"."FEE" "FEE_0","TMP2"."ID" "ID_1" FROM "BI"."TMP2" "TMP2" WHERE "TMP1"."ID"="TMP2"."ID" AND ("TMP2"."FEE"=20 OR "TMP2"."FEE"=30)))(+) "from$_subquery$_004"
   
9. Query block (08C0A6B4) unchanged

复制代码

或许因为OR存在，而为了维护A表的完整性，把QUERY提在JOIN前面，所以就只能做NEST LOOP。

如果是用HASH JOIN的话，假设他把（“TMP2"."FEE"=20 OR "TMP2"."FEE"=30）做为过滤器谓词。那么它必须是（“TMP2"."FEE"（+）=20  OR "TMP2"."FEE " （+）=30） 这种形式，但是我在两个版本的试验里，发现这个是触发ORA-01719: outer join operator (+) not allowed in operand of OR or IN 错误的。

而这句：

1. select * from tmp1 left join tmp2 on tmp1.id =tmp2.id and tmp1.fee   =50

复制代码

因为 连接条件是TMP1表的FEE列限定值50，但其实它还需要起出TMP1表的其它值，只不过和TMP2关联的时候都为空。
所以这个语句也不能使用HASH JOIN，因为用HASH JOIN的话，同级别的过滤器谓词总是在访问谓词前执行。
它必须维护TMP1表的完整性。

hotdog04

两篇文章写得很好，受益匪浅，
有一个问题：
是不是（+）这种连接方式，必需在where后的
所有条件上全加上才是正确的语法，否则就因为
不满足语法条件，而不会选择文章里说的查询执行
方法（因为使用（+）进行左连接时，加上左侧表的条件会对不满足的条件进行过滤）：
1、First, a Cartesian product is produced from the two tables involved in the join. This results in all combinations of all rows. The join conditions are then evaluated for each row in the Cartesian-product.
2、If the join conditions evaluate to TRUE, the row in question is retained in the result set.
3、If the join conditions evaluate to FALSE, the row is still retained. The columns from the non-optional table (up_city in my case) are preserved, and the columns from the optional table (up_business) are set to NULL.
4、Any duplicate rows as a result of step 3 are ultimately removed.
5、The WHERE clause is evaluated, and any rows not satisfying the conditions in the WHERE clause are removed.

Yong Huang

> 是不是（+）这种连接方式，必需在where后的
> 所有条件上全加上才是正确的语法

That can't be true. Can you show us a small test case where you get a syntax error?

Yong Huang

toniz

不一定要全加上。
select * from tmp1 a ,tmp2  b where  a.id =b.id(+) and b.fee=20

这个语法等于：
select * from tmp1 a left join tmp2  b on  a.id =b.id where b.fee=20

（+）这种方式是ORACLE 9之前的JOIN ，ANSI JOIN 就是我们常用的LEFT JOIN ,INNER JOIN这些。

toniz

Yong Huang,  thank you for your help.

hotdog04

我的意思是全加上（+），oracle才会解释成左连接或者右链接，否则按照一般的链接处理