• Welcome
• Projects
• Presentations
• Research

REFERENCE MODEL

Description

Accesses a reference table in a SQL model statement

Versions

This operation is implemented in the following versions

Example

This example was developed using Oracle 10.2.0.1 on Linux

This example requires the following table definitions

    CREATE TABLE t1 
    (
      c1 NUMBER,
      c2 NUMBER,
      c3 NUMBER
    );

    CREATE TABLE t2
    (
      c1 NUMBER,
      c2 NUMBER
    );

The statement

    SELECT c1,c2,v1,v2
    FROM t1
    GROUP BY c1,c2
    MODEL
      REFERENCE t2_ref ON
        (
          SELECT c1,c2 FROM t2
        )
        DIMENSION BY (c1)
        MEASURES (c2)
      MAIN t1_main
        DIMENSION BY (c1,c2)
        MEASURES (SUM (c3) v1,SUM (c3) v2)
      RULES
      (
        v2[10,2] = v1[CV(c1),1] * t2_ref.c2[10],
        v2[20,2] = v1[CV(c1),1] * t2_ref.c2[20]
      );

generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   SQL MODEL (ORDERED FAST)
2   1     REFERENCE MODEL OF 'T2_REF'
3   2       TABLE ACCESS (FULL) OF 'T2'
4   1     HASH GROUP BY
5   4       TABLE ACCESS (FULL) OF 'T1'