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" |