MODEL Performance Tuning: Avoid LIKE in Dimension References

Using LIKE in a dimension reference can cause excessive session memory usage. Where possible use operators that use less memory, such as =, IN, BETWEEN, or a FOR LOOP (see Dimension References).

For example, Query 2 below will use less memory than Query 1.

Query 1:

select m_1 from t_1k
model
  dimension by ( key2 )
  measures     ( m_1 )
  rules        ( m_1[KEY2 LIKE '5%'] = 'x' )
;

Test Complete

Query 2:

select m_1 from t_1k
model
  dimension by( key2, SUBSTR(KEY2,1,1) AS KEY3 )
  measures    ( m_1 )
  rules       ( m_1[any, '5'] = 'x' )
;

Test Complete

Results


Metric                     Query 1 Query 2
-------------------------- ------- -------
session pga memory max (K) 3,456   1,408
session uga memory max (K) 3,318   1,329

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

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 ave rage the results from multiple runs before making performance decisions.

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

[url=http://www.sqlsnippets.com/en/topic-12268.html]SQL Snippets: MODEL Performance Tuning - Avoid LIKE in Dimension References[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

<a href="http://www.sqlsnippets.com/en/topic-12268.html">SQL Snippets: MODEL Performance Tuning - Avoid LIKE in Dimension References</a>

To link to this page in other web sites use the following values.

Link Text : SQL Snippets: MODEL Performance Tuning - Avoid LIKE in Dimension References
URL (href): http://www.sqlsnippets.com/en/topic-12268.html

SQL Snippets ™: Tutorials for Oracle Developers

MODEL Performance Tuning

Avoid LIKE in Dimension References

Results

Linking to SQL Snippets ™

Revision Notes