Sometimes, having a way to create a series of integers greatly simplifies certain queries. For example, if your data looks like this:
select * from t ;
DAY_OF_WEEK VAL
----------- ----------
1 100
3 300
4 400
5 500
and you want a report that looks like this
DAY_OF_WEEK VAL
----------- ----------
0
1 100
2
3 300
4 400
5 500
6
it would be useful to have a table with the numbers 0 to 6 in it so you could write an outer join query like this.
select
day_of_week,
t.val
from
days_of_the_week d
left outer join t using ( day_of_week )
order by
day_of_week
;
DAY_OF_WEEK VAL
----------- ----------
0
1 100
2
3 300
4 400
5 500
6
If you expect to write lots of queries that use the same series of integers and they are based on real world phenomena then creating a table like DAYS_OF_THE_WEEK can be the best solution.
Occassionally however, you may need a different set of integers just for one specific query, for ad-hoc reports, or for a system you do not have CREATE TABLE privileges on. In these cases it may be impractical or impossible to create a dedicated table that meets your needs. Fortunately there are flexible, generic techniques for generating integers. The tutorials in this section demonstrate a few of them. The feature graph below will help you decide which method is best for you.
| Feature | Integer Table | MODEL | ROWNUM + a Big Table | CONNECT BY LEVEL | CUBE | Type Constructor | Pipelined Function |
|---|---|---|---|---|---|---|---|
| Pure SQL solution; no custom objects required | N | Y | Y | Y | Y | N | N |
| Works in versions prior to 10g | Y | N | Y | Y | Y | Y | Y |
Performance comparison charts for all these methods are available at the end of the section on the Performance Comparison - Small Numbers and Performance Comparison - Large Numbers pages.
See Also
If you need a series of numbers to store in a database column (e.g. for primary key columns) use a database sequence.
If you need a gap-free series of number to store in a database column (e.g. for invoice numbers, check numbers, etc.) see Serial Numbers.
Tags
| "integer generator" | "generate integers" |
| "number generator" | "generate numbers" |
| "row generator" | "generate rows" |