Integer Series Generators

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"



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11814.html]SQL Snippets: SQL Techniques Tutorials - Integer Series Generators[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11814.html">SQL Snippets: SQL Techniques Tutorials - Integer Series Generators</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: SQL Techniques Tutorials - Integer Series Generators
  • URL (href): http://www.sqlsnippets.com/en/topic-11814.html