Oracle Scratchpad

April 15, 2009

Model

Filed under: Infrastructure — Jonathan Lewis @ 9:40 am BST Apr 15,2009

When the model clause appeared a few years ago, it went on my list of “interesting, could be useful, must take a closer look some time, not urgent”.

Since then I’ve not come across any good reason for needing the feature, although other people have clearly found it very useful in their environments.

A couple of days ago, though, I was sent an email that pointed me to an extreme, and entertaining, use of the feature – Fast Fourier Transforms.  To me that means “serious physics” – but in this case it Alberto Dell’Era’s response to the first NoCOUG International SQL challenge (pdf file, 700KB).

In the preamble to his solution, Alberto pointed to a set of tutorial pages on the Model clauses that Rob Van Wijk wrote a couple of years ago so, for the benefit of anyone who might want a quick introduction to modelling, I’ve repeated the list of pointers:

  • Part 1 (also available in French)
  • Part 2 (also available in French)
  • Part 3 (also available in Dutch)

After reading Rob’s tutorials I went on to discover Oracle’s own ‘Oracle By Example’ pages – which were also very helpful: there’s an html version including screen snapshot, and a pdf version.

4 Comments »

  1. Volder has some very good posts here http://volder-notes.blogspot.com/search/label/Model and while learning this clause I’ve created one example for mortgage calculator here http://gplivna.blogspot.com/2008/10/mortgage-calculator-using-sql-model.html

    Comment by Gints Plivna — April 15, 2009 @ 6:45 pm BST Apr 15,2009 | Reply

  2. I think I first read fantastic tutorial from SnippyJoe on MODEL clause.
    http://www.sqlsnippets.com/en/topic-11663.html
    I really liked the way he described it, a step-by-step approach with examples and slowly building final query showing all aspects of MODEL clause.

    Comment by Narendra — April 16, 2009 @ 8:37 am BST Apr 16,2009 | Reply

  3. The competition has concluded and Alberto Dell’Era was declared the winner, not for his FFT (fast Fourier transform) solution using the MODEL clause, but for his DFT (discrete Fourier transform) solution. The runner-up was André Araujo from Australia. All told, there were nine entries from seven countries and three continents.

    http://prodlife.wordpress.com/2009/07/31/first-international-nocoug-sql-challenge-and-the-winner-is/
    http://iggyfernandez.wordpress.com/2009/07/31/results-of-the-first-international-nocoug-sql-challenge/

    Comment by Iggy Fernandez — August 10, 2009 @ 6:12 am BST Aug 10,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.