神谕.阻止合并子查询和主查询条件

Question

我有一个像表这样的大型实体属性值。我尝试使用子查询从此表中选择一些行，然后使用行进行过滤。在这种情况下，如何防止合并子查询和主查询？

例如：

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 通过将过滤器"（null 不是 null）"添加到子查询 1 和 3 来优化下一个查询

在子查询中，

我选择所有属性为"sal%"的行，然后在主查询中过滤它：

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

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

附言我想得到与

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

但是，没有CTE。

Answer 1

ROWNUM是

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

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

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

---

最好的长期解决方案是更改 EAV 表，以便每种类型都有不同的列，如我在此答案中所述。 现在修复此问题，否则将来的开发人员在必须编写复杂查询以避免类型错误时会诅咒你的名字。

Answer 2

我怀疑你的问题真的与优化器有任何关系。至少在您的示例中，所有三个属性的 VALUE 都设置为 ENAME。这对于"name"属性很好，但对于"sal"，它可能应该是SAL。对于"mgr"，我不知道，因为您的示例没有提供足够的信息。

我还建议删除"||ename"部分，再次假设优化器不是问题。

最后，如果 EMPNO 是您在 EMP 上的主键，则将 UNION 更改为 UNION ALL。 UNION 尝试将结果减少为唯一行，如果它们在 ID ATTR 上已经是唯一的，则这是不必要的处理。

重新设计视图，然后"从 EAV 中选择 *，其中 ATTR = 'sal'"并确认您看到的实际上是工资。这应该允许您毫无问题地为 sal 执行to_number （ATTR）。