甲骨文.防止合并子查询和主查询条件 [英] 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?

例如:

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

注意:添加||ename只是为了防止Oracle通过向子查询1和3添加过滤器(null不为null)"来优化下一个查询.

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

在子查询中，我选择所有属性为'sal％'的行，然后将其过滤到主查询中:

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;

此查询失败导致优化器将子查询与外部查询合并.合并数据库后，尝试将"to_number"应用于值"列中的所有值，但是其中一些具有字符串值.女巫提示会阻止这种优化吗?

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.我想得到与

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

但没有CTE.

推荐答案

ROWNUM是防止优化程序转换并确保类型安全的最安全方法.使用ROWNUM使Oracle认为行顺序很重要，并防止谓词推送和视图合并.

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;

还有其他方法可以做到这一点，但是没有一个是可靠的.不要为简单的内联视图，通用表表达式，CASE，谓词顺序或提示而烦恼.这些常见方法并不可靠，我已经看到它们都失败了.

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.

最好的长期解决方案是将EAV表更改为每种类型具有不同的列，正如我在此答案中所述.立即解决此问题，否则将来的开发人员在不得不编写复杂的查询以避免类型错误时将诅咒您的名字.

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.

这篇关于甲骨文.防止合并子查询和主查询条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！