Linda Webb Got breaking Oracle news?     Click here for more Oracle News Headlines

Learn the SQL Model clause

Jim Czuprynski has published an excellent overview of the Oracle SQL “model” clause.

Czuprynski gives an excellent example of the SQL model clause syntax with explanations:

SELECT state, prod, total_sales

  FROM sh.sales_midwest_only

 WHERE prod BETWEEN 125 AND 130

 GROUP BY state, prod

    MODEL

        PARTITION BY (state)

        DIMENSION BY (prod)

        MEASURES (SUM(sold) AS total_sales)

        IGNORE NAV

        UNIQUE DIMENSION

        RULES UPSERT

        (

        total_sales[99910] = 

             total_sales[prod=125] + total_sales[prod=126],

        total_sales[99920] = 

             total_sales[prod=127] 

            + total_sales[prod=128] 

            + total_sales[prod=129] 

            + total_sales[prod=130],

        total_sales[99999] = 

              total_sales[99920] 

            - total_sales[99910] 

        )

ORDER BY state, prod;

“When this SQL statement is executed, Oracle retrieves the selected result set, places the result set into memory and then allows the MODEL clause to divvy up all returned rows into a multi-dimensional array.

For example, a three-dimensional array can be visualized as a cube that has been cut into several horizontal slices, vertical slices, and layers.

Continuing this cubist analogy (apologies to Pablo Picasso!), the MODEL clause also lets me apply rules that perform inter-row calculations on the data that is stored at each intersection of these slices and layers.”

Czuprynski concludes that the SQL model clause can greatly simplify complex calculations using Oracle SQL:

“Oracle 10g's new inter-row calculation capabilities significantly expand the already-powerful suite of analytical functions so that any developer or Oracle DBA can perform complex, advanced data modeling and reporting in multiple dimensions, all without the use of third-party software to perform these analyses.

Oracle 10g's extensions to Structured Query Language have insured it is among the most robust, flexible, and fully featured of any of the relational premier database management systems.”