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
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.