SQL Snippets ™: Tutorials for Oracle Developers

This topic explores the limits of mutable row width (i.e. the sum of the widths of all mutable measures). In each of the following tests the number of mutable rows is fixed at zero. The width and number of mutable measures varies from one test to the next.

This model query with many narrow mutable measures works without error.

select 1 as c_1
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures
  (
    cast( null as varchar2(1) ) as m_1 ,
    cast( null as varchar2(1) ) as m_2 ,
    cast( null as varchar2(1) ) as m_3 ,
    cast( null as varchar2(1) ) as m_4 ,
    cast( null as varchar2(1) ) as m_5 ,
    cast( null as varchar2(1) ) as m_6 ,
    cast( null as varchar2(1) ) as m_7 ,
    cast( null as varchar2(1) ) as m_8 ,
    cast( null as varchar2(1) ) as m_9 ,
    cast( null as varchar2(1) ) as m_10,
    cast( null as varchar2(1) ) as m_21 ,
    cast( null as varchar2(1) ) as m_22 ,
    cast( null as varchar2(1) ) as m_23 ,
    cast( null as varchar2(1) ) as m_24 ,
    cast( null as varchar2(1) ) as m_25 ,
    cast( null as varchar2(1) ) as m_26 ,
    cast( null as varchar2(1) ) as m_27 ,
    cast( null as varchar2(1) ) as m_28 ,
    cast( null as varchar2(1) ) as m_29 ,
    cast( null as varchar2(1) ) as m_30
  )
  rules update
  (
    m_1[any] = 'x' ,
    m_2[any] = 'x' ,
    m_3[any] = 'x' ,
    m_4[any] = 'x' ,
    m_5[any] = 'x' ,
    m_6[any] = 'x' ,
    m_7[any] = 'x' ,
    m_8[any] = 'x' ,
    m_9[any] = 'x' ,
    m_10[any] = 'x' ,
    m_21[any] = 'x' ,
    m_22[any] = 'x' ,
    m_23[any] = 'x' ,
    m_24[any] = 'x' ,
    m_25[any] = 'x' ,
    m_26[any] = 'x' ,
    m_27[any] = 'x' ,
    m_28[any] = 'x' ,
    m_29[any] = 'x' ,
    m_30[any] = 'x'
  )
;

no rows selected

 

This query with a few wide mutable measures works without error too.

select 1 as c_1
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures
  (
    cast( null as varchar2(4000) ) as m_1 ,
    cast( null as varchar2(4000) ) as m_2 ,
    cast( null as varchar2(4000) ) as m_3 ,
    cast( null as varchar2(4000) ) as m_4 ,
    cast( null as varchar2(4000) ) as m_5 ,
    cast( null as varchar2(4000) ) as m_6 ,
    cast( null as varchar2(4000) ) as m_7 ,
    cast( null as varchar2(4000) ) as m_8 ,
    cast( null as varchar2(4000) ) as m_9 ,
    cast( null as varchar2(4000) ) as m_10,
    cast( null as varchar2(4000) ) as m_21 ,
    cast( null as varchar2(4000) ) as m_22 ,
    cast( null as varchar2(4000) ) as m_23 ,
    cast( null as varchar2(4000) ) as m_24 ,
    cast( null as varchar2(4000) ) as m_25 ,
    cast( null as varchar2(4000) ) as m_26 ,
    cast( null as varchar2(4000) ) as m_27 ,
    cast( null as varchar2(4000) ) as m_28 ,
    cast( null as varchar2(4000) ) as m_29 ,
    cast( null as varchar2(4000) ) as m_30
  )
  rules update
  (
    m_1[any] = 'x' ,
    m_2[any] = 'x' ,
    m_3[any] = 'x'
  )
;

no rows selected

 

However, this query with many, very wide mutable measures throws an error.

select 1 as c_1
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures
  (
    cast( null as varchar2(4000) ) as m_1 ,
    cast( null as varchar2(4000) ) as m_2 ,
    cast( null as varchar2(4000) ) as m_3 ,
    cast( null as varchar2(4000) ) as m_4 ,
    cast( null as varchar2(4000) ) as m_5 ,
    cast( null as varchar2(4000) ) as m_6 ,
    cast( null as varchar2(4000) ) as m_7 ,
    cast( null as varchar2(4000) ) as m_8 ,
    cast( null as varchar2(4000) ) as m_9 ,
    cast( null as varchar2(4000) ) as m_10,
    cast( null as varchar2(4000) ) as m_21 ,
    cast( null as varchar2(4000) ) as m_22 ,
    cast( null as varchar2(4000) ) as m_23 ,
    cast( null as varchar2(4000) ) as m_24 ,
    cast( null as varchar2(4000) ) as m_25 ,
    cast( null as varchar2(4000) ) as m_26 ,
    cast( null as varchar2(4000) ) as m_27 ,
    cast( null as varchar2(4000) ) as m_28 ,
    cast( null as varchar2(4000) ) as m_29 ,
    cast( null as varchar2(4000) ) as m_30
  )
  rules update
  (
    m_1[any] = 'x' ,
    m_2[any] = 'x' ,
    m_3[any] = 'x' ,
    m_4[any] = 'x' ,
    m_5[any] = 'x' ,
    m_6[any] = 'x' ,
    m_7[any] = 'x' ,
    m_8[any] = 'x' ,
    m_9[any] = 'x' ,
    m_10[any] = 'x' ,
    m_21[any] = 'x' ,
    m_22[any] = 'x' ,
    m_23[any] = 'x' ,
    m_24[any] = 'x' ,
    m_25[any] = 'x' ,
    m_26[any] = 'x' ,
    m_27[any] = 'x' ,
    m_28[any] = 'x' ,
    m_29[any] = 'x' ,
    m_30[any] = 'x'
  )
;
from   dual
       *
ERROR at line 2:
ORA-06522: CANNOT ALLOCATE ENOUGH MUTABLE AREA


 

Observations

The results suggest there is a maximum limit to the combined widths of all mutable measures in a MODEL query, even when it returns no rows.