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.

4 Comments

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

Leave a Reply

Your email address will not be published.