ORALCE 外连接

来源：互联网发布：淘宝上自粘墙纸有毒吗编辑：程序博客网时间：2024/06/07 05:29

写了多年SQL发现连外连接都没搞懂，还给别人讲了半天错误的，想想都是误认子弟啊。

表TEST1

select t.bucket,t.std_week from TEST1 t;

BUCKETSTD_WEEK201208011231201208061232201208131233201208201234201208271235201209011235201209031236201209101237201209171238201209241239201210011240201210081241201210151242201210221243201210291244201211011244201211051245201211121246201211191247

表TEST2：

select t.bucket,t.std_week from TEST2 t;

BUCKETSTD_WEEK201208011231201208061232201208131233201208201234201208271235201209011235201209031236201209101237201209171238

第一个sql:

SELECT a.bucket, a.std_week, b.bucket, b.std_week
FROM test1 a, test2 b
where a.bucket = b.bucket(+)
and a.std_week ='1245'

结果：

BUCKETSTD_WEEKBUCKETSTD_WEEK201211051245

执行计划：

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 7 | 00:00:01 |
| * 1 | HASH JOIN OUTER | | 1 | 90 | 7 | 00:00:01 |
| * 2 | TABLE ACCESS FULL | TEST1 | 1 | 45 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST2 | 9 | 405 | 3 | 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."BUCKET"="B"."BUCKET"(+))
* 2 - filter("A"."STD_WEEK"='1245')

结论：可以看出第二行先做了筛选，第一行才做的连接。对于左连接来讲先筛选后连接还是后连接先筛选对于结果是没有影响的，但是优化器考虑到先筛选后结果集会减少，有利于后面的操作，所以执行先筛选。

第二个sql：

SELECT a.bucket, a.std_week, b.bucket, b.std_week
FROM test1 a, test2 b
where a.bucket = b.bucket(+)
and b.std_week ='1231'

结果：

BUCKETSTD_WEEKBUCKETSTD_WEEK201208011231201208011231

执行计划：

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 90 | 6 | 00:00:01 |
| * 1 | HASH JOIN | | 1 | 90 | 6 | 00:00:01 |
| * 2 | TABLE ACCESS FULL | TEST2 | 1 | 45 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST1 | 19 | 855 | 3 | 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."BUCKET"="B"."BUCKET")
* 2 - filter("B"."STD_WEEK"='1231')

trc 文件部分：

Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."BUCKET" "BUCKET","A"."STD_WEEK" "STD_WEEK","B"."BUCKET" "BUCKET","B"."STD_WEEK" "STD_WEEK" FROM "REPORT"."TEST1" "A","REPORT"."TEST2" "B" WHERE "A"."BUCKET"="B"."BUCKET"(+) AND "B"."STD_WEEK"='1231'
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."BUCKET" "BUCKET","A"."STD_WEEK" "STD_WEEK","B"."BUCKET" "BUCKET","B"."STD_WEEK" "STD_WEEK" FROM "REPORT"."TEST1" "A","REPORT"."TEST2" "B" WHERE "A"."BUCKET"="B"."BUCKET"(+) AND "B"."STD_WEEK"='1231'
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: Considering outer-join elimination on query block SEL$1 (#0)
OJE: considering predicate"A"."BUCKET"="B"."BUCKET"(+)

rejected
OJE: considering predicate"B"."STD_WEEK"='1231'

OJE: Converting outer join of TEST2 and TEST1 to inner-join.
considered
Registered qb: SEL$940B0631 0x83286938 (OUTER-JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "B"@"SEL$1")
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$940B0631 nbfros=2 flg=0
fro(0): flg=0 objn=461958 hint_alias="A"@"SEL$1"
fro(1): flg=0 objn=461959 hint_alias="B"@"SEL$1"

OJE: outer-join eliminated
OJE: End: finding best directive for query block SEL$940B0631 (#0)
JE: Considering Join Elimination on query block SEL$940B0631 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."BUCKET" "BUCKET","A"."STD_WEEK" "STD_WEEK","B"."BUCKET" "BUCKET","B"."STD_WEEK" "STD_WEEK" FROM "REPORT"."TEST1" "A","REPORT"."TEST2" "B" WHERE "A"."BUCKET"="B"."BUCKET" AND "B"."STD_WEEK"='1231'
JE: cfro: TEST1 objn:461959 col#:2 dfro:TEST2 dcol#:2
JE: cfro: TEST1 objn:461959 col#:2 dfro:TEST2 dcol#:2
JE: cfro: TEST2 objn:461958 col#:2 dfro:TEST1 dcol#:2
JE: cfro: TEST2 objn:461958 col#:2 dfro:TEST1 dcol#:2
SQL:******* UNPARSED QUERY IS *******

结论：注意trc 文件红色部分，ORACLE 在查询转换部分考虑到筛选条件是一个非空值，直接把外连接改为了内连接，ORACLE 感觉有点先连接后筛选的味道，左连接就是为了得到右边表的空值，现在你又不要空值，那和内连接有啥区别呢。

第三个sql ：

SELECT a.bucket, a.std_week, b.bucket, b.std_week
FROM test1 a, test2 b
where a.bucket = b.bucket(+)
and b.std_week is null

结果：

BUCKETSTD_WEEKBUCKETSTD_WEEK201211051245201210291244201211011244201210151242201210221243201209241239201210011240201210081241201211121246201211191247

执行计划:
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 1710 | 7 | 00:00:01 |
| * 1 | FILTER | | | | | |
| * 2 | HASH JOIN OUTER | | 19 | 1710 | 7 | 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST1 | 19 | 855 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 9 | 405 | 3 | 00:00:01 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("B"."STD_WEEK" IS NULL)
* 2 - access("A"."BUCKET"="B"."BUCKET"(+))

结论：这里可以明显的看出当筛选空值的时候。oracle 使用了先连接后筛选的顺序。

第四个SQL：

SELECT a.bucket, a.std_week, b.bucket, b.std_week
FROM test1 a, test2 b
where a.bucket = b.bucket(+)
and b.std_week(+) = '1231'

结果：

BUCKETSTD_WEEKBUCKETSTD_WEEK201208011231201208011231201208201234201211191247201210291244201210081241201210151242201211121246201210011240201208131233201208061232201209101237201209031236201210221243201209171238201211051245201211011244201209011235201209241239201208271235

执行计划：

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 418 | 6 | 00:00:01 |
| * 1 | HASH JOIN OUTER | | 19 | 418 | 6 | 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST1 | 19 | 209 | 3 | 00:00:01 |
| * 3 | TABLE ACCESS FULL | TEST2 | 1 | 11 | 3 | 00:00:01 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."BUCKET"="B"."BUCKET"(+))
* 3 - filter("B"."STD_WEEK"(+)='1231')

结论：(+) 写上之后就可以让ORACLE知道是要先筛选再去连接了。

关于使用（+）的一些注意事项：

（+）操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
当使用（+）操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
（+）操作符只适用于列，而不能用在表达式上。
（+）操作符不能与OR和IN操作符一起使用。
（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

0 0