Reference Model Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

Reference Model

A reference model is a read-only model that is used in the main model.

SELECT
   *
FROM
   EMP
MODEL
REFERENCE
   D
   ON
   (
      SELECT
         *
      FROM
         DEPT
   )
   DIMENSION BY
   (
      DEPTNO
   )
   MEASURES
   (
      DNAME
   )
MAIN
  E
PARTITION BY
(
   DEPTNO
)
DIMENSION BY
(
   EMPNO
)
MEASURES
(
   ENAME,
   CAST(NULL AS VARCHAR2(14)) DNAME
)
(
   E.DNAME[ANY]=D.DNAME[CV(DEPTNO)]
);

    DEPTNO      EMPNO ENAME      DNAME
---------- ---------- ---------- --------------
        30       7499 ALLEN      SALES
        30       7521 WARD       SALES
        30       7654 MARTIN     SALES
        30       7698 BLAKE      SALES
        30       7844 TURNER     SALES
        30       7900 JAMES      SALES
        20       7369 SMITH      RESEARCH
        20       7566 JONES      RESEARCH
        20       7788 SCOTT      RESEARCH
        20       7876 ADAMS      RESEARCH
        20       7902 FORD       RESEARCH
        10       7782 CLARK      ACCOUNTING
        10       7839 KING       ACCOUNTING
        10       7934 MILLER     ACCOUNTING

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   182 |     3   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED  |      |    14 |   182 |            |          |
|   2 |   REFERENCE MODEL   | D    |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL | EMP  |    14 |   182 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

The main model E contains the employees, partitioned by department. The dimension is the employee name. The measure is initialized to null cast to VARCHAR2(14).

A reference model D is defined on the DEPT table with a dimension DEPTNO and a measure DNAME. The reference model can be neither upserted nor updated.

The execution planshows the REFERENCE MODEL operation. A reference model is comparable as an external worksheet in Excel.

The REFERENCE MEASURES can be used as assignment for the MAIN MODEL but cannot be selected outside of the model clause.

SELECT
   *
FROM
   DEPT
MODEL
REFERENCE
   EMP
ON
   (
      SELECT
         EMPNO,
         SAL
      FROM
         EMP
   )
   DIMENSION BY
   (
      EMPNO
   )
   MEASURES
   (
      SAL
   )
MAIN
   DEPT
DIMENSION BY
(
   DEPTNO
)
MEASURES
(
   DNAME,
   0 BONUS
)
(
   BONUS[10]=SAL[7782]*10+SAL[7839],
   BONUS[20]=SAL[7788]+SAL[7839],
   BONUS[30]=SAL[7844]/2
);

    DEPTNO DNAME               BONUS
---------- -------------- ----------
        40 OPERATIONS              0
        10 ACCOUNTING          29500
        20 RESEARCH             8000
        30 SALES                 750

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     4 |    52 |     3   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED FAST|      |     4 |    52 |            |          |
|   2 |   REFERENCE MODEL      | EMP  |    14 |   112 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL   | EMP  |    14 |   112 |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL    | DEPT |     4 |    52 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

There is no relation between DEPT and EMP here. A random access to the EMP worksheet retrieves the salaries of Clark, King, Scott and Turner and assigns them to the bonus column of the main worksheet.

The execution planshows a TABLE ACCESS FULL of EMP. The optimizer did not recognize the access by primary key in this example.

Aggregation

Used in rules, the aggregate function is evaluated on a measure expression and the dimension limits the scope of the aggregation.

SELECT
   DESCRIPTION,
   TOTAL
FROM
   EMP
MODEL
UNIQUE SINGLE REFERENCE
RETURN UPDATED ROWS
DIMENSION BY
(
   JOB,
   DEPTNO,
   CAST(NULL AS VARCHAR2(30)) DESCRIPTION
)
MEASURES
(
   0 TOTAL,
   SAL
)
(
   TOTAL[NULL,NULL,'Manager']=SUM(SAL)['MANAGER',ANY,NULL],
   TOTAL[NULL,NULL,'Sales']=SUM(SAL)[ANY,30,NULL],
   TOTAL[NULL,NULL,'Research analyst/clerk']=
      SUM(SAL)[JOB IN ('CLERK','ANALYST'),20,NULL]
);

DESCRIPTION                         TOTAL
------------------------------ ----------
Research analyst/clerk               7900
Sales                                9400
Manager                              8275

The total for manager is computed by summing all salaries where the job is manager, the department is ANY and the description is NULL. The total for sales is the sum of salaries where the job is ANY, the department is 30 and the description is NULL. The total for research analyst/clerk is the sum of salaries where the job is either clerk or analyst, the department is 20 and the description is NULL.

There are three dimensions: The job and the department, which are set to NULL in the subtotal; the description, which is initialized to NULL and set to a text literal for the subtotals; and the SUM function in the MODEL rules has a special syntax and does not require a GROUP BY clause in the base query.

When the base query is using a GROUP BY clause, any group by expression or aggregate function can be used in partition, dimension or measure expressions.

SELECT
   *
FROM
   EMP
GROUP BY
   ROLLUP(DEPTNO),JOB
MODEL
PARTITION BY
(
   DEPTNO
)
DIMENSION BY
(
   JOB
)
MEASURES
(
   SUM(SAL) SAL
)
RULES SEQUENTIAL ORDER
(
   SAL[JOB='PRESIDENT']=SAL['PRESIDENT']*10,
   SAL[JOB='CLERK']=SAL['CLERK']*1.1,
   SAL[NULL]=SUM(SAL)[ANY]
)
ORDER BY
   DEPTNO,
   JOB;

    DEPTNO JOB              SAL
---------- --------- ----------
        10 CLERK           1430
        10 MANAGER         2450
        10 PRESIDENT      50000
        10                53880
        20 ANALYST         6000
        20 CLERK           2090
        20 MANAGER         2975
        20                11065
        30 CLERK           1045
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9495
           ANALYST         6000
           CLERK           4565
           MANAGER         8275
           PRESIDENT      50000
           SALESMAN        5600
                          74440

The base query is using a GROUP BY clause. The department partition and job dimension are GROUP BY expressions. The salary measure is an expression that uses the aggregate function SUM. The aggregated salary of the president is multiplied by 10 while the clerks get a 10% raise only. The subtotal per job and the grand total are evaluated in the rules. Sequential order is the default; first, the president gets +900%; then the clerks get +10%; and finally, the subtotals per department and grand total are upserted.