7 Replies Latest reply: Jul 16, 2012 9:57 PM by indra budiantho RSS

    How to do WHERE-CLAUSE in hierarchical query in Oracle

    949587
      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