SQL Snippets ™: Tutorials for Oracle Developers

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.