SQL Snippets ™: Tutorials for Oracle Developers

In this topic we see whether the PARTITION BY clause affects session memory or not.

Tests

Without
PARTITION BY
100 Rows Updated:

select m_1 from t_100k
model
  dimension by (key_3, key_2)
  measures (m_1)
  rules ( m_1[any,1] = 'x' )
;

Test Complete
 

With
PARTITION BY
100 Rows Updated:

select m_1 from t_100k
model
  partition by (key_3)
  dimension by (key_2)
  measures     (m_1)
  rules ( m_1[1] = 'x' )
;

Test Complete
 

Without
PARTITION BY
10000 Rows Updated:

select m_1 from t_100k
model
  dimension by (key_3, key_2)
  measures     (m_1)
  rules ( m_1[any,for key_2 like '%' from 1 to 10 increment 1] = 'x' )
;

Test Complete
 

With
PARTITION BY
10000 Rows Updated:

select m_1 from t_100k
model
  partition by (key_3)
  dimension by (key_2)
  measures     (m_1)
  rules ( m_1[for key_2 like '%' from 1 to 100 increment 1] = 'x' )
;

Test Complete
 

Results

                           Without          With             Without            With
                           PARTITION BY     PARTITION BY     PARTITION BY       PARTITION BY
Metric                     100 Rows Updated 100 Rows Updated 10000 Rows Updated 10000 Rows Updated
-------------------------- ---------------- ---------------- ------------------ ------------------
session pga memory max (K) 30,592           39,168           30,272             39,168
session uga memory max (K) 29,780           37,993           29,468             37,993

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

Observations

In the above tests partitioning had no effect on session memory usage.