How to do WHERE-CLAUSE in hierarchical query in Oracle

Question

In Oracle hierarchical query, the WHERE-CLAUSE should be evaluated after the Connect-By operator in oracle document says.

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

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

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 plan, the condition in WHERE, f1=b1 and (b2 = 1 or f1=b2 and b1=1 or f2=b1+1) and f1 is not null, has become the two parts:

one: filter(("B2"=1 OR "B1"=1) AND "F1" IS NOT NULL) --> evaluate after connect-by

the other: filter("F1"="B2" OR "F2"="B1"+1) and access("F1"="B1") --> evaluate before connect-by as JOIN-ON

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

thanks.

thanks.

Answer 1

You should not use implicit joins, use explicit JOINs instead.

Once you do that you can distinguish the "real" where condition from the join condition.

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

Rewrite your query to something 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 initial query) as the join will not yield any results if f1 is null anyway.

Answer 2

If you need to split it explicitly, you can do that with parenthesis 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;