Connect by to generate rows – Laurent Schneider

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.

4 thoughts on “Connect by to generate rows”

Duke Ganote November 17, 2005 at 21:25

For what it’s worth, I concur with your assessment of “DUAL CONNECT BY LEVEL

laurent schneider November 17, 2005 at 22:59

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

Anonymous December 17, 2005 at 13:01

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.

Laurent Schneider December 18, 2005 at 08:20

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

Comments are closed.