SQL Snippets ™: Tutorials for Oracle Developers

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.