You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




04 November 2005

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.

7 Comments:

Anonymous Duke Ganote said...

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!

17/11/05 21:25  
Anonymous laurent schneider said...

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

17/11/05 22:59  
Anonymous Anonymous said...

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.

17/12/05 13:01  
Blogger Laurent Schneider said...

Thanks ben for your comment.
What is missing in my profile?

18/12/05 08:20  
Blogger SnippetyJoe said...

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/

31/3/07 02:32  
Blogger Laurent Schneider said...

can you generate 1M rows with model?

31/3/07 09:43  
Anonymous Anonymous said...

> 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.

16/11/07 13:59  

Post a Comment

<< Home