Oracle 关联的时候，是先过滤后连接还是先连接后过滤？

显示全部楼层 · 2007-7-4 17:27:50

语句：select T1.*,T2.* FROM T1 , T2 WHERE T1.ac=T2.ac
and t1.bb='A001'
and t2.cc='B002'
像这样的一条语句，oracle内部执行的时候是先对t1、T2连接操作，得出临时表后分别对t1 bb列，t2 cc列过滤得到最后的集合
还是先分别对T1 bb列，T2.cc列过滤后分别得到t1',t2'集合，然后再执行连接呢？

千问 · 2007-7-4 17:27:50

不要猜，看执行计划即可

千问 · 2007-7-4 17:27:50

先笛卡尔积，再过滤

千问 · 2007-7-4 17:27:50

bell6248 发表于 2016-12-16 09:55
不要猜，看执行计划即可
有道理！

千问 · 2007-7-4 17:27:50

本帖最后由 zst007007 于 2016-12-16 17:06 编辑
SQL> set linesize 1000
SQL> select t1.*,t2.* from tbl t1,tbc t2 where t1.a=t2.a and t1.a='100';
A
B
A
C
---------- -------------------- ---------- ------------------------------
10000000
100abcde
10000001
100abcde
10000002
100abcde

Execution Plan
----------------------------------------------------------
Plan hash value: 2074768284
----------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|| 2 |42 | 3 (0)| 00:00:01 |
| 1 |MERGE JOIN CARTESIAN || 2 |42 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | TBC| 1 |11 | 2 (0)| 00:00:01 |
|*3 |INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT
|| 3 |30 | 1 (0)| 00:00:01 |
| 5 |TABLE ACCESS BY INDEX ROWID| TBL| 3 |30 | 1 (0)| 00:00:01 |
|*6 | INDEX RANGE SCAN
| IDX_T1 | 3 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T2"."A"='100')
6 - access("T1"."A"='100')

像上面这个执行计划，是不是先对T1 和T2 过滤a=100,然后再执行排序合并操作？
最近刚刚开始学oracle，很多疑惑

千问 · 2007-7-4 17:27:50

access是先过滤
fliter是后过滤

千问 · 2007-7-4 17:27:50

都是有可能的，要看CBO的分析导致它采用了哪种。

千问 · 2007-7-4 17:27:50

先过滤，后连接
1.JOIN表连接执行会比较两个表的每一行数据（当连接的条件值为TRUE返回））
Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause;
To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.
2.WHERE子句会限制连接查询的结果集返回；
A WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query