oracle where clause,sql – 如何在Oracle中的分层查询中执行WHERE-CLAUSE

在Oracle分层查询中,应在oracle文档中的Connect-By运算符之后评估WHERE-CLAUSE.

但是有一些复杂的情况：如果WHERE-CLAUSE包含JOIN样式的限定,正如oracle所说,应该在Connect-By运算符之前评估Join-Style限定,而另一个只引用一个关系的none-Join-Style将是在Connect-By运算符之后进行评估.

所以问题是：如何将WHERE-CLAUSE中的资格区分为两部分,一部分在Connect-By运算符之前进行评估,另一部分在Connect-By运算符之后进行评估.

example:

SQL> desc bar

Name Null? Type

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

B1 NUMBER(38)

B2 NUMBER(38)

SQL> desc foo;

Name Null? Type

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

F1 NUMBER(38)

F2 NUMBER(38)

SQL> set pagesize 3000

SQL> set linesize 3000

SQL> explain plan for select * from foo, bar where

2 **f1=b1 and (b2 = 1 or f1=b2 and b1=1 or f2=b1+1) and f1 is not null**

3 connect by level < 10;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 2657287368

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

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

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

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

|* 1 | FILTER | | | | | |

|* 2 | CONNECT BY WITHOUT FILTERING| | | | | |

|* 3 | HASH JOIN | | 1 | 52 | 5 (20)| 00:00:01 |

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

| 5 | TABLE ACCESS FULL | BAR | 1 | 26 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - **filter(("B2"=1 OR "B1"=1) AND "F1" IS NOT NULL)**

2 - filter(LEVEL<10)

3 - **access("F1"="B1")**

**filter("F1"="B2" OR "F2"="B1"+1)**

Note

-----

- dynamic sampling used for this statement

24 rows selected.

因此,如上图所示,WHERE中的条件,f1 = b1和(b2 = 1或f1 = b2且b1 = 1或f2 = b1 1)且f1不为空,

已成为两部分：

一：filter((“B2”= 1或“B1”= 1)和“F1”IS NOT NULL) – >连接后评估

另一个：过滤器(“F1”=“B2”或“F2”=“B1”1)和访问(“F1”=“B1”) – >在连接之前评估为JOIN-ON

那么,谁可以解释如何区分WHERE子句中的条件以及如何从连接之前或之后应用的WHERE子句中形成两个部分？

谢谢.

谢谢.