CODEWITHSUNDEEP
sqloracle-database
Nvr
Nvr

Reputation: 171

Internal process of Dual table with Level Pseudocolumn

Oracle says Dual table by default has one record in it

But when I use the LEVEL Pseudocolumn, dual table generate more than one row with multiple records as below

SQL> SELECT SYSDATE+LEVEL, to_char(SYSDATE+LEVEL, 'Day') Day
  2  FROM dual
  3  CONNECT BY LEVEL <= 5;

SYSDATE+L DAY
--------- ---------
27-DEC-19 Friday
28-DEC-19 Saturday
29-DEC-19 Sunday
30-DEC-19 Monday
31-DEC-19 Tuesday

What is happening internally in dual when it comes to LEVEL?

Upvotes: 0

Views: 54

Answers (3)

William Robertson
William Robertson

Reputation: 16001

Just to add, there is nothing special about level here, other than the way it gets incremented by each iteration of connect by, allowing you to specify the number of rows you want. You can think of it as

connect by [boolean expression]

where connect by will generate rows as long as [boolean expression] evaluates to true. For example:

select rownum from dual
connect by rownum < dbms_random.value * 10

Personally I prefer rownum to level as it's the number of rows I care about and not the hierarchical level, even though they come to the same thing for a single-row table (try it for a 2-row table and you'll see a big difference - though probably that's something you'll only ever do by accident).

Strictly speaking, a connect by clause requires a prior expression to complete the syntax (e.g. connect by prior employee_id = manager_id), and using it without one purely to generate rows might be considered a hack, however it is widely used and unlikely to become invalid in a forceable Oracle release.

Upvotes: 1

Ori Marko
Ori Marko

Reputation: 58772

Using CONNECT BY is looping through the dual single row 5 times

  1. Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.

  2. Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

  3. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18665

You are mixing 2 concepts. As per the documentation, dual contains one column and one row. 

SELECT * FROM dual

returns

X

The CONNECT BY LEVEL <= N clause will return you N time the records that are returned by the original query. So in your case you will get 5 (the value for n) x 1 (one row from your select from dual) rows. CONNECT BY is meant for hierarchical queries (check the doc) where LEVEL is a pseudocolumn, but this can be used to generated dummy rows or a fixed number of rows like a date range etc (plenty of examples everywhere).

Upvotes: 2

Related Questions