#### VARCHAR2 Measures

In this topic we explore the session memory impact of updating VARCHAR2 cells with values of varying lengths. In each of the following tests the number of mutable VARCHAR2 measures is fixed at one. The number of rows updated is fixed at 1000. The length of updated cell values varies from one run to the next.

##### Tests

Starting Test #1 select key, m_1 from t_1k model dimension by( key ) measures( m_1 ) rules update( m_1[any] = null ) ;

Starting Test #2 select key, m_1 from t_1k model dimension by( key ) measures( m_1 ) rules update( m_1[any] = rpad('x',1,'x') ) ;

Starting Test #3 select key, m_1 from t_1k model dimension by( key ) measures( m_1 ) rules update( m_1[any] = rpad('x',10,'x') ) ;

Starting Test #4 select key, m_1 from t_1k model dimension by( key ) measures( m_1 ) rules update( m_1[any] = rpad('x',100,'x') ) ;

Starting Test #5 select key, m_1 from t_1k model dimension by( key ) measures( m_1 ) rules update( m_1[any] = rpad('x',1000,'x') ) ;

##### Results

Updated to Updated to Updated to Updated to Updated to METRIC_NAME NULL Length 1 Length 10 Length 100 Length 1000 -------------------------- ---------- ---------- ---------- ---------- ----------- session pga memory max (K) 3,584 3,584 3,584 3,584 3,584 session uga memory max (K) 3,382 3,382 3,382 3,382 3,382

See Statistics Descriptions for a description of each metric.

##### Observations

The results suggest that, unlike database tables and PL/SQL variables over 2000 bytes long which both allocate variable amounts of space for VARCHAR2 columns and variables, VARCHAR2 measures occupy a fixed amount of session memory (the full declared length). In other words, VARCHAR2 measures appear to behave the same as CHAR measures.

#### CLOB Measures

MODEL does not appear to support measures with a CLOB datatype.

create table t1( key number, m_1 clob ) ; select 'x' from t1 model dimension by( key ) measures( m_1 ) () ; select 'x' from t1 model dimension by( key ) measures( m_1 ) () * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got CLOB

Warning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and average the results from multiple runs before making performance decisions.