In this tutorial we explore the PARTITION component of the MODEL clause. The Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) - Chapter 22 describes partition columns this way.

"Partition columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions described in Chapter 21, "SQL for Analysis and Reporting". Rules in the MODEL clause are applied to each partition independent of other partitions."

"You can partition data and evaluate rules within each partition independent of other partitions. This enables parallelization of model computation based on partitions."

#### Performance

If your system has multiple processors the PARTITION BY clause may improve your query's performance (see Parallel Execution). For example, with a query like this

break on group_1 skip 1 duplicates select group_1 , group_2 , num_val , m_1 from t model dimension by ( group_1, group_2 ) measures ( num_val, 0 m_1 ) rules ( m_1[any, any] = num_val[cv(), cv()] * 10 ) order by group_1 , group_2 ;

GROUP_1 GROUP_2 NUM_VAL M_1 ---------- ---------- ------- ---------- A a1 100 1000 A a2 200 2000 A a3 300 3000 B a1 B a2 300 3000 B a3 100 1000 C a1 100 1000 C a2 a1 200 2000 a2 800 8000

one CPU will most likely process the M_1 rule for all rows in the result set (note: I have not tested this behaviour myself; this information is assumed from the manuals). Using a query like the following produces the same output while allowing one processor on your system to process the rows where GROUP_1 is 'A', another CPU to process those where GROUP_1 is 'B', etc.

select group_1 , group_2 , num_val , m_1 from t model PARTITION BY ( GROUP_1 ) dimension by ( group_2 ) measures ( num_val, 0 m_1 ) rules ( m_1[any] = num_val[cv()] * 10 ) -- note only one dimension now order by group_1 , group_2 ;

GROUP_1 GROUP_2 NUM_VAL M_1 ---------- ---------- ------- ---------- A a1 100 1000 A a2 200 2000 A a3 300 3000 B a1 B a2 300 3000 B a3 100 1000 C a1 100 1000 C a2 a1 200 2000 a2 800 8000

While PARTITION BY had no visible effect on the output in the last example, there are situations where it will have an effect. These are described next.

#### Uniqueness of Dimension Columns

In the DIMENSION BY tutorial we saw that a DIMENSION BY clause whose columns do not uniquely identify each result row generate an error.

select group_2 , num_val from t model dimension by ( group_2 ) -- group_2 is not unique measures ( num_val ) rules () order by group_2 ; t * ERROR at line 5: ORA-32638: Non unique addressing in MODEL dimensions

With a partition clause this no longer happens, as long as the dimension values are unique within a partition.

select group_2 , num_val from t model PARTITION BY ( GROUP_1 ) dimension by ( group_2 ) -- group_2 values are unique within group_1 values measures ( num_val ) rules () order by group_2 ;

GROUP_2 NUM_VAL ---------- ------- a1 100 a1 100 a1 a1 200 a2 800 a2 200 a2 a2 300 a3 100 a3 300

If your dimension values are not unique within a partition you will get an error.

select group_1 , date_val , num_val from t model partition by ( group_1 ) dimension by ( DATE_VAL ) -- there are duplicate date values in group_1 measures ( num_val ) rules () order by group_1 , date_val ; t * ERROR at line 6: ORA-32638: Non unique addressing in MODEL dimensions

#### Aggregate and Analytic Functions

PARTITION BY also effects query results with aggregate and analytic functions. This effect is described in the Aggregate and Analytic Expressions tutorial.