Questions about oracle connectby and rownum-CodePudding

in recent study connect by some of the basic knowledge and the operation principle, see have a SQL, displays connect by operating out of memory, how is the SQL logic is not very good,

 with a as (
Select 5 as n the from dual 
Union all 
Select the from 10 dual 
Union all 
The select 15 from dual 
Union all 
Select the from 20 dual 
) 
Select * from a where rownum=1 connect by rownum<6;

if there is no the where condition is understandable, added the where don't understand,
Have a great god can explain?

CodePudding user response:

This should be available to the infinite loop

CodePudding user response:

http://www.tuicool.com/articles/qUVn2y

CodePudding user response:

reference 1st floor u010412440 response:

this should be available to the infinite loop

Because each loop is rownum=1, so the infinite loop?

CodePudding user response:

I and execute the SQL

 with a as (
Select 5 as n the from dual 
Union all 
Select the from 10 dual 
Union all 
The select 15 from dual 
Union all 
Select the from 20 dual 
) 
Select * from a where clause rownum<4 connect by level<6;

it's the where is the final result of screening,
Whether in the where clause should be in every cycle, or in the last work?

CodePudding user response:

reference 4 floor zyann95 response:

I and execute the SQL
 with a as (
Select 5 as n the from dual 
Union all 
Select the from 10 dual 
Union all 
The select 15 from dual 
Union all 
Select the from 20 dual 
) 
Select * from a where clause rownum<4 connect by level<6; 
it's the where is the final result of screening,
Whether in the where clause should be in every cycle, or in the last work?

Perform connect by first, and then execute the where

CodePudding user response:

Feeling attached to use two ROWNUM can trigger an ORACLE BUG

CodePudding user response:

The rownum swap places for hair look at the principle of the

 with a as (
Select 5 as n the from dual 
Union all 
Select the from 10 dual 
Union all 
The select 15 from dual 
Union all 
Select the from 20 dual 
) 
Select * from a where clause rownum<6 connect by rownum=1;