Tip of the Week
Tip for Week of May 29, 2006

Oracle 10g Fibonacci Sequence (SQL Model)

This tip comes from Marcio Portes, who provides DBA support to Citibank in Sao Paulo, Brazil.

NOTE: This code requires Oracle Database 10g (which includes the SQL Model clause feature).

SQL> select s seq
   2  from dual
   3  model return all rows
   4  dimension by ( 0 d ) measures ( 0 s )
   5  rules iterate (12) (
   6  s[iteration_number ] = decode(
   7  iteration_number, 0, 0, 1, 1, s[iteration_number-2]
   8  ) + nvl(s[iteration_number-1],0)
   9  )
  10  /

           SEQ
-------------
             0
             1
             1
             2
             3
             5
             8
            13
            21
            34
            55
            89

12 rows selected.