Oracle. Preventing merge subquery and main query conditions

Question

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?

For example:

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

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

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

select *
FROM (select id,value from EAV where attr like 'sal%')
WHERE to_number(value) > 5000;

This query fall 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.s. I want to get same result as

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

but, without CTE.

Answer 1

ROWNUM is the safest way to prevent optimizer transformations and ensure type safety. Using ROWNUM makes Oracle think the row order matters, and prevents things like predicate pushing and view mergning.

select *
from
(
   select id, value, rownum --Add ROWNUM for type safety.
   from eav
   where attr like 'sal%' 
)
where to_number(value) > 5000;

There are other ways to do this but none of them are reliable. Don't bother with simple inline views, common table expressions, CASE, predicate ordering, or hints. Those common methods are not reliable and I have seen them all fail.

---

The best long-term solution is to alter the EAV table to have a different column for each type, as I describe in this answer. Fix this now or future developers will curse your name when they have to write complex queries to avoid type errors.

Answer 2

I doubt your problem really has anything to do with the optimizer. At least in your example, VALUE is set to ENAME for all three atttributes. That's fine for the "name" attribute, but for "sal" it should probably be SAL. For "mgr", I have no clue as your example doesn't provide enough information.

I'd also recommend removing the "||ename" part, again on the assumption that the optimizer is not the problem.

Lastly, change the UNIONs to UNION ALLs, if EMPNO is your primary key on EMP. UNION attempts to reduce the results to unique rows, which is unnecessary processing if they're already unique on ID, ATTR.

Rework the view, and then "select * from EAV where ATTR = 'sal'" and confirm that what you're seeing is actually salaries. That should allow you to do to_number(ATTR) for sal with no issues.