Accesses a reference table in a SQL model statement
This operation is implemented in the following versions
|
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'