Mastering Oracle SQL, 2nd Edition

Chapter 18. Model Queries

Some complex calculations are not easily amenable to SQL. Tasks such as forecasting sales, computing market share, solving simultaneous equations, analyzing time series, and so on involve iterative calculations, often referencing interdependent rows across multiple dimensions. It becomes extremely difficult to solve such problems in SQL, and the resultant SQL code becomes very difficult to understand and maintain. Such SQL often involves multiple levels of subqueries, joins, and UNIONs, and therefore performs inefficiently.

Rather than use SQL to solve problems such as we’ve just described, people usually download the data to a spreadsheet and perform the computations there. Some applications move data into specially created, external calculation engines that can perform the necessary computations efficiently. Downloading data into spreadsheets, or moving data into special-purpose engines, involves overhead and adversely impacts performance, scalability, manageability, and security of the system managing the data.

Oracle Database 10g introduces a new MODEL clause that allows you to treat relational data as a multidimensional array for the purpose of performing spreadsheet-like operations. Now you can more easily solve such problems as we’ve just described, in the database, using a single SQL statement.

Basic Elements of a Model Query

Let’s take an example to understand the basic elements of a model query. The sales_history table holds the sales data for ...