- 论坛徽章:
- 7
|
本帖最后由 asdf2110 于 2012-10-26 17:26 编辑
疑问:
where 子句只是由 a1 + b2 = prior b1 和 a1 + 1 = prior b1 的区别,为什么得到的查询计划会差这么大呢?主要是FILTER 的位置,在线等待牛人解答
代码如下:
SQL> SELECT * FROM TEST1, TEST2 where a1 + b2 = prior b1 CONNECT BY LEVEL < 3 START WITH A2 = -1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2869622028
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 7 (0)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST2 | 2 | 58 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEST1 | 1 | 29 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LEVEL<3 AND "A2"=(-1))
4 - filter("A1"+"B2"=PRIOR "B1")
Note
-----
- dynamic sampling used for this statement
SQL> SELECT * FROM TEST1, TEST2 where a1 + 1 = prior b1 CONNECT BY LEVEL < 3 START WITH A2 = -1;
A1 A2 A B1 B2 B
---------- ---------- - ---------- ---------- -
2 1 B 4 2 D
2 1 B 3 1 C
执行计划
----------------------------------------------------------
Plan hash value: 2327574006
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 348 | 7 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 3 | MERGE JOIN CARTESIAN | | 6 | 348 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEST2 | 2 | 58 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 3 | 87 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST1 | 3 | 87 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A1"+1=PRIOR "B1")
2 - filter(LEVEL<3 AND "A2"=(-1))
Note
-----
- dynamic sampling used for this statement
SQL>
|
|