Connect by to generate rows
I just want to clarify something today : I do not like the connect by level<5 or connect by 1=1 where rownum, etc...
I would prefer to receive ORA-01436: CONNECT BY loop in user data when trying such a hack.
Nowadays, Tom Kyte uses this method in all his demonstrations. Which makes the users confident of using it.
Still I do not feel this method to be safe. It just seems too much abstract to me.
Also, it seems it could burn a lot of cpu time, depending on how the optimizer evaluate it.
Let's try a few examples
SQL> select * from dual connect by level<3;
D
-
X
X
it works
SQL> select * from dual connect by level<3 and dummy=prior dummy;
select * from dual connect by level<3 and dummy=prior dummy
*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
it fails. Can you tell me why? the first example is also doing a virtually infinite loop.
I would prefer to receive ORA-01436: CONNECT BY loop in user data when trying such a hack.
Nowadays, Tom Kyte uses this method in all his demonstrations. Which makes the users confident of using it.
Still I do not feel this method to be safe. It just seems too much abstract to me.
Also, it seems it could burn a lot of cpu time, depending on how the optimizer evaluate it.
Let's try a few examples
SQL> select * from dual connect by level<3;
D
-
X
X
it works
SQL> select * from dual connect by level<3 and dummy=prior dummy;
select * from dual connect by level<3 and dummy=prior dummy
*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
it fails. Can you tell me why? the first example is also doing a virtually infinite loop.
Posted by Laurent Schneider at 11/04/2005 08:58:00 AM
Permalink : Connect by to generate rows
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
7 Comments:
For what it's worth, I concur with your assessment of "DUAL CONNECT BY LEVEL < ". I use it as a simple means of row generation for test and sample data, even though it doesn't "make sense" to me without a PRIOR. It works in some versions, and that's "good enough" if I document the version. For a production system, I'd follow the recommendation you found of using a PIPELINED function. Thank you for digging that up!
note that in production, I would recommend creating "lookup" tables if appropriate.
For datawarehouse, prefer using dimension tables, as described in the
datawarehouse guide
, if I need to outer join with each day of the year, or each minute, I create the relevant table and insert each values than join with that fix table. It can be partitioned, or IOT, and it is highly scalable.
Generating the rows is not "better" than using a fixed lookup table. Check what is the most appropriate.
thanks for your comment, I greatly appreciate
Sali Laurent hope you yre doing fine. Your profile is at moment not complete. So it is a bit difficult to accuit you.
Thanks Ben.
Thanks ben for your comment.
What is missing in my profile?
For what it's worth CONNECT BY LEVEL makes me nervous too.
I prefer he following MODEL solution instead. It's faster, scales pretty much the same as CONNECT BY LEVEL, doesn't require a table or pipelined function, is fairly compact, and it has no questionable syntax.
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
--
SnippetyJoe
http://www.sqlsnippets.com/
can you generate 1M rows with model?
> select * from dual
> connect by level<3
> and dummy=prior dummy
>*
>ERROR at line 1:
>ORA-01436: CONNECT BY loop in user data
Oracle checks the connect by condition before executing the query. Probably Oracle just checks that you don't have the same field referenced twice in the connect by prior clause, since "dummy=prior dummy" without an additional condition would in fact result in a infinite loop.
Post a Comment
<< Home