sqloraclesubqueryquery-optimizationhints

Oracle. Preventing merge subquery and main query conditions

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.

Solution

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