Thursday, May 03, 2007

Generating rows magically - the many virtues of DUAL

I was reading one of the last issues of Oracle Magazine when i came across the following tip published by the #1 Oracle Guru, Tom Kyte:
with data
as
(select level n
from dual
connect by level <= 5) select * from data; 
if you don't like to use the WITH syntax in such a simple subquery, you can rewrite it as follows:
select n
from (select level n
from dual
connect by level <= 5);
Edited on May 14, 2007: be sure to check out the comments section, there is a comment from Knut about this sql query. Re-edited on September 3, 2007: It turns out that in Oracle 9i there is something odd with the simplified version of the query (after removing the inline view, so check out the comments for the latest version). This short piece of SQL is especially useful because i was still using the old-fashioned select * from table(function(n)) that is a technique available since Oracle 8i where, in order to spawn an arbitrary number of records on the fly, you must populate an object table with the desired number of elements. Note that this technique is still useful when the data must be retrieved in a less straightforward fashion, as it happens usually with complex business rules. However, thanks to this smart technique that takes advantage of the dual table and a sort of fake hierarchical query, you can spawn a recordset made up of n-rows and without additional objects, that is using less resources, less memory and in a simpler, faster way. Now, i was wondering how nice it would be to have a sort of parametric view, something like:
select n from nrows;
Well, if you don't mind setting up a simple package:
CREATE PACKAGE pkg_nrows
as
n   integer;
function fn_n  return integer;
end;
/

CREATE PACKAGE BODY pkg_nrows
as
function fn_n   return integer
is
begin
return n;
end;
end;
/

CREATE VIEW NROWS ( N ) AS
select level n
from dual
connect by level <= pkg_nrows.fn_n 
You can finally perform the query from view nrows, just after setting the value of the desired n value, that was defined as a packaged global variable.
begin
pkg_nrows.n := 5;
end;
/
select *
from nrows
/

N
-
1
2
3
4
5

And there you go with your freshly made n-tier dual table.

Hope it helps!


PS: as quirk of the day I'd mention the space characters handling inside pre-formatted text of Blogger's HTML editor, i can't make out how it works, it keeps trimming my spaces and line breaks randomly!

6 comments:

knut said...

Why not simply:

select level n
from dual
connect by level <= 5;

Byte64 said...

You are absolutely right Knut,
who knows why i didn't notice it!

One can simplify all the queries by pushing the inline view to the upper level.

CREATE OR REPLACE FORCE VIEW "NROWS" ("N") AS
select level n
from dual
connect by level <= pkg_nrows.fn_n

Thanks for pointing out this non-sense!

Dag said...

There seem to be a limit to how many rows you can fetch. I'm using Oracle 9.2.0.6.0 and it stops at 250. On 10.2.0.3.0 this limit seems to be removed. I can at least fetch 10 000 numbers.

Byte64 said...

Dag,
thanks for pointing this out.
I think there must be something odd going on in Oracle 9, because if i run the query:

select level n
from dual
connect by level <= 500

i only get 100 rows.

But if run:

select * from (select level n
from dual
connect by level <= 500)
where n >= 1

I get all 500 rows!

On the contrary on XE, i ran it against 100,000 without any problems.

If you look my original posting, i wrote an inline view initially, then Knut asked why i had used this apparently unnecessary construct and after doing a quick test i replied that he was probably right, so one could remove the inline view.

Now it turns out that apparently the inline view is what makes this strange query work for values greater than 100 (or 250 in your case).

So, now we can go back to Tom Kyte and ask if he had initially used the inline view with a good reason!

;-)

Byte64 said...

Ok, i keep writing overly complicated queries, you can take out the WHERE clause from my last SQL!

Anonymous said...

Nice One...Fantastic way of generating number in single query

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles