select level from dual connect by level<=4 how it works internally [duplicate]

Question

I know level usage along with hierarchical queries but I am not able to understand how to analyze this:

(select level from dual connect by level<=4) 

query works internally and how it is generating numbers.

For generating numbers I had another way that is:

select r from (select rownum r from all_objects) e where r<=10 

it will generate the first 10 numbers. But I am not able to understand how level is working internally.

Please explain why!

Answer 1

This builds a hierarchical query. The connect by defines how to walk from the parent node to its children node and their children's children node. In this case, your definition of connection is only when level >= 4. The trick here is that level is pseudo column, like rownum, so the condition is satisfied only by the less-than equal number supplied (in this case 4). You can also do this with rownum:

select level from dual connect by rownum <= 4; 

Level is used to count the depth of the connections, so a parent would be 1, a child 2, a child of the child 3, etc. So think of it as a rownum for hierarchical queries.