what's for "select level from dual connect by level <= 10" 2

[lovekang]

what's for this type of query?
select level from dual connect by level <= 10
in what situation this can be used?

Thanks.

 

[SantaMufasa]

LoveKang,

The code you list is actually, probably, a code fragment taken out of context. By itself, the code doesn't do much more than a plain, old "SELECT <something> from dual;". Notice:

SQL> select 'hello' from dual;

'HELL
-----
hello

SQL> select 'hello' from dual connect by level <= 10;

'HELL
-----
hello

But if you use the code as an in-line subquery, it can be rather useful to generate a desired number of rows in the case where the number or rows does not relate the number or rows from the table from which you are attempting to SELECT:

select 'hello'
  from (select null from dual connect by level <= 10)
/

'HELL
-----
hello
hello
hello
hello
hello
hello
hello
hello
hello
hello

10 rows selected.

An alternative to the above technique (of displaying a specific number of rows) that does not use the very esoteric CONNECT BY clause is to simply choose some other table or view to drive your query. For example:

select 'hello' from all_tab_columns where rownum <= 10;

'HELL
-----
hello
hello
hello
hello
hello
hello
hello
hello
hello
hello

10 rows selected.

Although the above code is conceptually much easier to interpret, it relies upon the use of an object ("ALL_TAB_COLUMNS", a data dictionary view that exists in every Oracle database) that certainly has 10 rows, which, in this case is the number of rows we wanted. The number of rows that your "ALL_TAB_COLUMNS" possesses, however, is, at any moment, unpredictable. (In my database, the number of rows in "ALL_TAB_COLUMNS", at this moment, happens to be 30,114. In virtually every case where I would like to use this technique, 30K rows should be plenty to achieve my objective.)

I'm sure you have noticed that I have not yet explained the behaviour of the "CONNECT BY" clause. Since you asked to know what the query is for, I believe that I have explained that. How it (the CONNECT BY) works, however, requires a rather complex explanation, which I shall not get into unless you insist.

Until you insist, suffice it to say that using "CONNECT BY" to generate a specific number or rows is rather like using a wrench to drive a nail...It works, but certainly not the purpose for which Oracle created the "CONNECT BY" clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lovekang]

"it can be rather useful to generate a desired number of rows in the case where the number or rows does not relate the number or rows from the table from which you are attempting to SELECT:"

I haven't met the situation before.

Thanks.

 

[SantaMufasa]

I haven't met the situation before.

Here is an example that may clarify the need for your wanting a specific number of rows. Let's assume that you want to create a report that shows how many people have birthdays in each month of the year. Let's also assume the following results from your query:

select to_char(birthdate,'MON')Month,count(*) Birthdays
from person
group by to_char(birthdate,'MM'), to_char(birthdate,'MON')
/

MON  BIRTHDAYS
--- ----------
JAN          2
FEB          3
APR          3
MAY          3
JUN          7
JUL          8
SEP          2
OCT          3
NOV          3
DEC          3

10 rows selected.

Notice that "MAR" and "AUG" do not appear in the list since there are no PERSONs with birthdays in those two months. If we want all 12 months to appear on the list, then we need to "force" twelve rows of output.


To force twelve rows of output, I'll "generate" twelve rows using the "CONNECT BY" method, then doing an OUTER JOIN ["(+)"] between those twelve rows and the PERSON table. The OUTER JOIN forces all twelve months to appear despite there not being any birthdays in March and August. I use the 12 numbers generated from the CONNECT BY to translate into Month names:

select to_char(to_date(Month_data.Month_num,'MM'),'MON') Month
      ,count(Person_data.Month_num) Birthdays
  from (select to_char(birthdate,'MM')  month_num
              ,to_char(birthdate,'MON') month_name
          from person
       ) person_data
      ,(select rownum Month_num
          from dual connect by level <= 12) Month_data
 where Month_data.Month_num = person_data.Month_num(+)
 group by Month_data.Month_num, Month_name
/

MON  BIRTHDAYS
--- ----------
JAN          2
FEB          3
MAR          0
APR          3
MAY          3
JUN          7
JUL          8
AUG          0
SEP          2
OCT          3
NOV          3
DEC          3

12 rows selected.

Let us know if this is a reasonable example of why one might need to generate a specific number of rows.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[lovekang]

Thanks a million

 

[jimirvine]

Hi Dave, sorry to butt in (I seem to have a habit of doing that to you, sorry) were you using v9 for your initial example by any chance?
in v10
SELECT 'Hello'
FROM DUAL
CONNECT BY LEVEL<=10;
Will actually produce 10 'Hello's

Great example as to where to use it, but it can be simplified:

SELECT TO_CHAR(m.mnth,'Mon') hire_month, count(*)
FROM hr.employees e
RIGHT OUTER JOIN (SELECT TO_DATE(LEVEL,'MM') mnth
		FROM DUAL
		CONNECT BY LEVEL<=11) m
ON TO_CHAR(m.mnth,'Mon') = to_char(e.hire_date,'Mon')
GROUP BY m.mnth
ORDER BY m.mnth

Another time I use it quite a lot is to generate a table of dates as part of a Package initialization block which in turn populates a package Associative Array

I noticed that you mentioned the limitation on the number of rows when using rownum and a 'normal' table like all_tab_columns, I would say that this isn't really a valid limitation as we can overcome it with a simple cartesian join to generate as many rows as you will ever need.
The main reason to use the method that you showed above is efficiency
Compare the following 2 similar pieces of code. the tenkrows is a single column table that holds exactly 10000 rows (the hint is in the name ) so that no cartesians or restrictions are required to 'muddy the waters'

SQL> select sysdate+level
  2  from dual
  3  connect by level <= 10000;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|   1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
     177083  bytes sent via SQL*Net to client
       7707  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> select sysdate+lvl
  2  from tenkrows;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4197133435

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 10000 |   126K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TENKROWS | 10000 |   126K|     6   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        708  consistent gets
         16  physical reads
          0  redo size
     177081  bytes sent via SQL*Net to client
       7707  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Now granted, because this was a pretty small dataset, the timings are pretty much the same, but look at the stats, and th IO stats in particular.
Dual is a VERY special table which does not require the same kind of processing as our 'normal' tables and hence is far, far more efficient at generating rows than any other method.
Jim

 

[SantaMufasa]

Jim,

I never consider your excellent contributions a "butt in"...you always provide valuable insight and fill in gaps that may exist in my own knowledge/experience.

Hava

for yet another such contribution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Thanks Dave, glad to contribute