How to make WHERE-CLAUSE in a hierarchical query in Oracle

Question

In a hierarchical query, the Oracle WHERE-CLAUSE should be evaluated after the Connect-By statement in the oracle document reports.

But there are difficult situations: if WHERE-CLAUSE contains a JOIN style qualification, as the oracle says, the Join-Style qualification should be evaluated before the Connect-By statement, and the other - non-Join-Style, which refers to only one relation will be evaluated after the Connect-By statement.

So, the question is: how to distinguish qualifications in WHERE-CLAUSE into two parts, one is evaluated before the Connect-By statement, and the other is evaluated after the Connect-By statement.

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. 

So, as shown above, the condition in WHERE, f1 = b1 and (b2 = 1 or f1 = b2 and b1 = 1 or f2 = b1 + 1), and f1 is not equal to zero, became two parts:

one: filter (("B2" = 1 OR "B1" = 1) AND "F1" DOES NOT) → evaluate after connection

another: filter ("F1" = "B2" OR "F2" = "B1" +1) and access ("F1" = "B1") → evaluate before connecting - as JOIN-ON

So, who can explain how to distinguish the conditions in the WHERE clause and how to form the two parts from the WHERE clause that should be applied before or after the connection?

thanks.

thanks.

Answer 1

You should not use implicit joins, use JOIN explicitly instead.

Once you do this, you can distinguish “real” when the condition is from the join condition.

It’s not clear to me (and this is the result of using the implicit join syntax) what exactly do you want to use as the join condition and what to use as the where condition.

Rewrite your query like this:

 from foo join bar on foo.f1 = bar.b1 where bar.b2 = 1 or .... and f1 is not null connect by level < 10; 

the condition f1 is not null seems unnecessary (even in your original request), since the connection will not produce any results if f1 is null.

Answer 2

If you need to separate it explicitly, you can do this with parentheses and inline views.

 select * from (select * from foo, bar where f1=b1 and (b2 = 1 or f1=b2 and b1=1 or f2=b1+1) and f1 is not null) connect by level < 10;