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.
Oracle Scratchpad 
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 |
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 |
[…] Comment on Alberto Dell’Era’s solution by Jonathan Lewis: https://jonathanlewis.wordpress.com/2009/04/15/model/ […]
Pingback by Got Lost Following NoCoug SQL Challenge? « I’m just a simple DBA on a complex production system — April 20, 2009 @ 9:55 pm BST Apr 20,2009 |
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 |