Home » SQL & PL/SQL » SQL & PL/SQL » Connect by Without Prior
Connect by Without Prior [message #221102] Fri, 23 February 2007 20:56 Go to next message
eworm
Messages: 3
Registered: February 2007
Junior Member
Dear all:
There is a Connect By statement without the key Prior,
like 'connect by rownum<5'.

Can someone tell me the logical of this statement?
Re: Connect by Without Prior [message #221314 is a reply to message #221102] Mon, 26 February 2007 04:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Does this help?

MHE
Re: Connect by Without Prior [message #222127 is a reply to message #221102] Fri, 02 March 2007 00:05 Go to previous messageGo to next message
eworm
Messages: 3
Registered: February 2007
Junior Member
select * from customers;
CUS_ID CUS_NAME
------- ---------
1 Home
3 School

select * from customers connect by rownum<5;
CUS_ID CUS_NAME
------- -------------------------------
1 Home
1 Home
1 Home
1 Home

what does this result mean?
Re: Connect by Without Prior [message #222311 is a reply to message #221102] Fri, 02 March 2007 15:02 Go to previous messageGo to next message
vadimtro
Messages: 8
Registered: December 2006
Junior Member
This particular technique was invented by Mikito Harakiri who apparently tried to break hierarchical query cycle detection. See the chapter on integer generators in

http://www.rampant-books.com/book_2006_1_sql_coding_styles.htm


Hierarchical Query

I have mentioned already DB2 integer generator leveraging recursive SQL. Oracle doesn’t have recursive SQL capabilities (at the time of this writing), so that users have to use non-standard hierarchical query extension. A contrast between the Oracle and DB2 solutions is often enlightening. One fundamental difference between these two platforms is that Oracle seems to be able to detect loops, while DB2 doesn’t make such a claim. Detecting loops, in general, is undecideable, which is the basis for DB2’s position. Does Oracle’s loop detection work because hierarchical extension has narrowed query expression capabilities compared to recursive SQL? Can we challenge it?

Consider a typical hierarchical query

select ename from emp
connect by empno = prior mgr
start with empno = 1000

First, Oracle finds all the nodes in the graph satisfying the start with condition. Then, for each batch of nodes found on a previous step, it finds a batch of extra nodes that satisfy the connect by condition. Any time the new node collides with the nodes that have been already discovered, it signals the connect by loop error. How does it identify the nodes? Should it compare all the relation attributes, or only those in the select clause, or choose some other ones? It is easy to see that the attributes in the select clause shouldn’t matter. Indeed, adding a rownum pseudo column would artificially make the next node appear to be always different from its predecessors. The loop, however, is a property of the graph. Graph either has a cycle or not, no matter what node labels there may be. Therefore, the only columns which should be relevant for loop detection are the ones in the predicate with the prior prefix.

What if we write hierarchical query without the prior? This experiment reveals a remarkably succinct integer generator

select rownum from dual
connect by 0=0

As an alternative to the rownum pseudo column, we could use level

select level from dual
connect by 0=0


Re: Connect by Without Prior [message #222328 is a reply to message #221102] Fri, 02 March 2007 21:20 Go to previous messageGo to next message
eworm
Messages: 3
Registered: February 2007
Junior Member
Good answer,Thanks!
Re: Connect by Without Prior [message #228037 is a reply to message #221102] Fri, 30 March 2007 19:47 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
For what it's worth, there is some debate about whether using CONNECT BY without PRIOR is legal or not (see the "To Use PRIOR or Not to Use PRIOR, That is the Question" section at SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method).

For a less risky solution try using MODEL instead (assuming you have 10g).

select integer_value
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10


You can learn more about this technique at SQL Snippets: Integer Series Generators - MODEL Method.

--
SnippetyJoe
http://www.sqlsnippets.com/
Re: Connect by Without Prior [message #228066 is a reply to message #228037] Sat, 31 March 2007 02:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is it ok with you if I add some of the content of the page mentioned to 'our' wiki page? I will of course add a link to the source.
Re: Connect by Without Prior [message #228090 is a reply to message #228066] Sat, 31 March 2007 08:14 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Thanks for asking Frank. I can't get to it now, but I'll send you an email granting you some reproduction rights on Monday or Tuesday. After that, no problem.

BTW, I mentioned two pages in my post. I assume it's the second one you're interested in?

[Updated on: Sat, 31 March 2007 08:16]

Report message to a moderator

Re: Connect by Without Prior [message #228185 is a reply to message #228090] Mon, 02 April 2007 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, I meant the model-clause. It is still missing from the OraFAQ Wiki.
I could also add just a link to it.
btw, if this email concerns rights to copy copyrighted material, better cc Frank Naude, as he is the admin of this site.
Re: Connect by Without Prior [message #228345 is a reply to message #228185] Mon, 02 April 2007 11:14 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
You know what, it was easier for me to simply edit that Wiki page myself. Check it out. The changes should be there now.

I've never edited a Wiki page before. It was kinda neat, not as hard as I thought it would be. Thanks for mentioning it.
Re: Connect by Without Prior [message #228351 is a reply to message #228345] Mon, 02 April 2007 11:40 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cool. Saves me the work Smile
Previous Topic: time format
Next Topic: function based index
Goto Forum:
  


Current Time: Sat Dec 03 04:07:24 CST 2016

Total time taken to generate the page: 0.24273 seconds