Search BC Oracle Sites

# Integer Generators in SQL

SQL Tips by Donald Burleson

Counting as was introduced in Chapter 1 is fundamental to our culture. Some SQL queries however require more sophistication than simply counting rows. Although sometimes there is no obvious candidate relation to count, a need may arise requiring the generation of an arbitrary number of rows and the counting of those rows. Formally, what is needed is the Integers relation which contains nothing more than a single numeric column with a list of positive integer numbers.

Surprisingly, there is no built-in Integers relation in SQL. The first half of this chapter will show about a dozen ways to cook it. And the second part of this chapter will introduce the numerous applications.

Integers Relation

Commercial databases do not come already equipped with the Integers relation. However, it is fairly easy to manufacture. Since the solution differs between various vendors, now is a good time to branch out this information.

Recursive With

DB2 has, arguably, the most satisfactory answer with recursive SQL:

with Integers (num) as
( values(1)
union all
select num+1 from Integers

where num < 100
)
select * from Integers

The execution flow literally follows the formal description. The Integers relation is defined via itself, hence the adjective ?recursive?. Unlike other relational operators, which determine the result instantaneously and by purely logical means, the recursive definition works in steps. The process begins with an empty Integers relation.  Add to this, the first tuple (num=1). After that is complete the recursive part

?
select num+1 from Integers
where num < 100
?

is ignited and starts producing additional tuples. The recursion stops as soon as the recursive part is unable to generate more tuples.

The only imperfection with this process is the hard-coded constant in the where condition. Was the Integers relation really defined or just the IntegersThatAreLessThan100? With this question in mind, it becomes very tempting to move the whole condition to the outer query block where it fits more naturally:

with Integers (num) as
( values(1)
union all
select num+1 from Integers

)
select * from Integers

where num < 100

Why this is such a good idea, and yet why it does not work will be a recurring theme in this chapter.

DB2 has enjoyed the recursive SQL feature for quite a while. This solution applies to Microsoft SQL Server circa 2005 as well. A reader interested in prior art is advised to lookup Usenet Groups history on Google, since the question, ?What is the analog of rownum in a MS Server?? was asked and, perhaps, is still being asked about every week on the Microsoft SQL Server programming forum.

Big Table

There is about dozen of different ways to generate integers in Oracle. Moreover, every new release increases this number. The oldest and the least sophisticated method just hangs on the rownum column to some big table; the all_objects dictionary view being the most popular choice:

select rownum from all_objects where rownum < 100

The rownum is a pseudo column that assigns incremental integer values to the rows in the result set. This column name is Oracle proprietary syntax, hence the question from users of the other RDBMS platforms highlighted in the previous section.

The ROWNUM Pseudocolumn

The ROWNUM is a hack. Expression with a ROWNUM in a where clause

select ename

from Emp

where ROWNUM = 2

is responsible for an output that confuses a newbie.

ROWNUM predates the much more cleanly defined row_number() analytic function. For example

select ename, row_number() over (order by ename) num

from Emp rownum

projects the Emp relation to the ename column, and extends it with an additional integer counter column. This extension, however, conflicts with the selection operator. Both

select ename, row_number() over (order by ename) num

from Emp

where num = 2

and

select ename, row_number() over (order by ename) num

from Emp

where row_number() over (order by ename) = 2

are illegal.

The all_objects view is fairly complicated, though. From a performance perspective, the sys.obj\$ dictionary table is a much better choice, because it is an actual table rather than a view.

 This is an excerpt from the new book SQL Design Patterns: The Expert Guide to SQL Programming by Vadim TropashkoYou can buy it direct from the publisher for 30%-off.

��

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:

and include the URL for the page.

 Burleson Consulting The Oracle of Database Support Oracle Performance Tuning