This discussion is archived
7 Replies Latest reply: Jul 16, 2012 7:57 PM by indra budiantho RSS

How to do WHERE-CLAUSE in hierarchical query in Oracle

949587 Newbie
Currently Being Moderated
Hi, all

In Oracle Release 11.1.0.6.0 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 will be evaluated after the Connect-By operator.

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

example:
SQL> create table bar(b1 int, b2 int);

Table created.

SQL> create table foo(f1 int, f2 int);

Table created.

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.

SQL>

h2. HERE is my detailed questions:
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.
--------------------------------------------------------------------------------------

Edited by: user5332354 on 2012-7-16 下午8:22

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points