Oracle. Preventing merge subquery and main query conditions
<p>I have a large entity-attribute-value like a table. I try to select some rows from this table with a subquery and then filtrate with rows. How can I prevent merging subquery and main query in this situation?</p>

<p>For example:</p>

<pre><code>EMP:
EMPNO | ENAME  | SAL
---------------------
1000  | KING   | 10000
1001  | BLAKE  | 7500

CREATE VIEW EAV(ID,ATTR,VALUE) AS
select empno, 'name'||ename, ename from emp -- subquery 1
union
select empno, 'sal'||ename, ename from emp -- subquery 2
union
select empno, 'mgr'||ename, ename from emp -- subquery 3
</code></pre>

<p>NOTE: <code>||ename</code> was added just to prevent Oracle to optimize next queries by adding filter "(null is not null)" to subquery 1 and 3</p>

<p>In subquery I select all rows with attribute 'sal%' and then filtrate it in the main query:</p>

<pre><code>select *
FROM (select id,value from EAV where attr like 'sal%')
WHERE to_number(value) &gt; 5000;
</code></pre>

<p><strong>This query fall</strong> cause optimizer merge subquery with outer query. After merging DB try to apply to_number to all values in column "value", but some of it has a string value. Witch HINT prevent this optimization?</p>

<p>p.s. I want to get same result as </p>

<pre><code>WITH t as (
   select /*+ materialize */ id,value
   from eav
   where attr like 'sal%') 
select * from t where to_number(value) &gt; 5000;
</code></pre>

<p>but, without CTE.</p>