Wednesday, March 18, 2009

Filtering first or constructing the hierarchical tree first?

A SQL with "start with" and "connect by" looks like:

select level, a.*
from t a
where a.ntid =2
start with a.vp_bid =9 or a.vp_bid is null
connect by prior a.bid= a.vp_bid;

My doubt is that Oracle will first apply the predicate: ntid=2 , or Oracle will
apply the "start with ... connect by" first, then do the filtiering? My test shows the latter is true


The rows in the table:


SQL> select * from t;

BID NTID VP_BID
---------- ---------- ----------
1 2
2 2
3 2 1
4 2 100
5 2 2
6 7 2
7 7 9
10 10 9
11 10 10
12 2 10
30 30 30

11 rows selected.



If filtering first, what we should see:





SQL> select level, a.* from
2 ( select * from t where ntid=2) a
3 start with a.vp_bid =9 or a.vp_bid is null
4 connect by prior a.bid= a.vp_bid;

LEVEL BID NTID VP_BID
---------- ---------- ---------- ----------
1 1 2
2 3 2 1
1 2 2
2 5 2 2



If applying the hierarchical condition first, then the filtering, what we should see:





SQL> select * from (
2 select level, a.*
3 from t a
4 start with a.vp_bid =9 or a.vp_bid is null
5 connect by prior a.bid= a.vp_bid
6 )
7 where ntid=2;

LEVEL BID NTID VP_BID
---------- ---------- ---------- ----------
2 12 2 10
1 1 2
2 3 2 1
1 2 2
2 5 2 2


What we see from the original SQL confirms that Oracle applys the hierarchical condition first:


SQL> select level, a.*
2 from t a
3 where a.ntid =2
4 start with a.vp_bid =9 or a.vp_bid is null
5 connect by prior a.bid= a.vp_bid;

LEVEL BID NTID VP_BID
---------- ---------- ---------- ----------
2 12 2 10
1 1 2
2 3 2 1
1 2 2
2 5 2 2




Without the predicate, we should see:


SQL> select level, a.*
2 from t a
3 start with a.vp_bid =9 or a.vp_bid is null
4 connect by prior a.bid= a.vp_bid;

LEVEL BID NTID VP_BID
---------- ---------- ---------- ----------
1 7 7 9
1 10 10 9
2 11 10 10
2 12 2 10
1 1 2
2 3 2 1
1 2 2
2 5 2 2
2 6 7 2

9 rows selected.

No comments: