SQL Snippets ™: Tutorials for Oracle Developers

In this topic we explore the session memory impact of using different styles of dimension references on the left hand side of RULE assignments. In each of the following tests the number of measures updated is fixed at one and the number of rows updated is fixed at one. The dimension reference expression varies from one test to the next.

Starting Test #1

select key, m_1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[key=1] = 'x' ) ;
 

Starting Test #2

select key, m_1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[key in ( 1 )] = 'x' ) ;
 

Starting Test #3

select key, m_1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[key between 1 and 1] = 'x' ) ;
 

Starting Test #4

select key, m_1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[for key from 1 to 1 increment 1] = 'x' ) ;
 

Starting Test #5

select key, m_1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[sign(1)] = 'x' ) ;
 

Starting Test #6

select key, m_1 from t_1k
model dimension by( key2 ) measures( m_1 )
rules update ( m_1[key2 like '1'] = 'x' ) ;
 

Starting Test #7

variable bv number

execute :bv := 1

select 1 from t_1k
model dimension by( key ) measures( m_1 )
rules update ( m_1[:bv] = 'x' ) ;
 

Starting Test #8

select 1 from t_1k
model dimension by( key ) measures( m_1 )
rules update iterate (1) ( m_1[iteration_number] = 'x' ) ;
 

Results

                                                    FOR                     Bind ITERATION
METRIC_NAME                     =   IN() BETWEEN   Loop Function   LIKE Variable   _NUMBER
-------------------------- ------ ------ ------- ------ -------- ------ -------- ---------
session pga memory max (K)    256    256     256    256      256  3,584    3,520     3,520
session uga memory max (K)    256    256     256    256      256  3,382    3,382     3,382

See Statistics Descriptions and Latches and Internal Locks for metric descriptions.
 

Observations

Dimension references employing LIKE, bind variables, or the ITERATION_NUMBER function can cause Oracle to reserve enough session memory to store every row in the result set, regardless of how many rows are actually targeted by the reference. On the other hand, references employing =, IN, BETWEEN, or functions other than ITERATION_NUMBER can cause Oracle to reserve only enough session memory for targeted rows.

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.