CODEWITHSUNDEEP
sqloracle-databasemodeloracle10g
user372724
user372724

Reputation:

Model clause in Oracle

I am recently inclined towards in Oracle jargon and the more I am looking into the more is attracting me.

I have recently come across the MODEL clause but to be honest I am not understanding the behaviour of this. Can any one with some examples please let me know about the same.

Thanks in advance

Upvotes: 6

Views: 2623

Answers (3)

Bob
Bob

Reputation: 2586

The best explanation is in the official white paper. It uses the SH demo schema and you really need it installed.

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

I don't think they do a very good job explaining this. It basically lets you load up data into an array and and then loop through array using straight SQL, instead of having to write procedural logic. Alot of the terms are based on spreadsheet terms (they are used in the Excel Help). So if you have them in excel, this would be confusing.

They should have drawn a picture for each of the queries and shown the array created than shown how you look through the array. The syntax looks to be based on Excel syntax. I'm not sure if this is common to all spreadsheet tools or not.

It has uses. Bin fitting is the most common. See the 2nd example. This is basically a complex group by where you are grouping by a range, but that range can change. It requires procedural logic. The example gives 3 ways to do it one of which is the model clause.

http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22asktom-1518271.html

I think people (often managers) who do complex spreadsheet calculations may have an easier time seeing uses for this and getting the lingo.

Upvotes: 1

Lukas Eder
Lukas Eder

Reputation: 221106

I think the MODEL clause is quite simple to understand, when you slowly read the official whitepaper. In my opinion, the whitepaper nicely explains the MODEL clause step by step, adding one feature at a time to the examples, leaving out the most advanced features to the official documentation.

From the whitepaper, I also find it easy to understand when to actually use the MODEL clause. In some examples, it is a lot simpler to do "Excel-spreadsheet-like" operations using MODEL rather than, for instance, using window functions, CONNECT BY, or subquery factoring. Think about Excel. Whenever you want to define a complex rule set for Excel columns, use the MODEL clause. Example Excel spreadsheet rules:

A10 = A9 + A8
B10 = A10 * 5
C10 = MAX(A1:A9)
D10 = C10 / A10

In other words, MODEL is a very powerful SQL spreadsheet!

Upvotes: 6

Gary Myers
Gary Myers

Reputation: 35401

Some examples of MODEL are given here.

Personally I've looked at MODEL several times and not yet succeeded in finding a use case for it. While it first appears to be useful, there's a lot of places where only literals work (rather than binds or variables) which restrict its flexibility. For example, on inter-row calculations, you can't readily refer to the 'previous' or 'next' row, but have to be able to absolutely identify it by its attributes. So you can't say 'take the value of the row with the same date in the previous month' but can only code a specific date.

It might be used (internally) by some analytical tools. But as an end-user tool, I never 'got' it. I have previously recommended that, if you ever find a problem you think can be solved by the application of the MODEL clause, go and have a lie down until the feeling wears off.

Upvotes: 6

Related Questions