甲骨文。防止合并子查询和主查询条件

小编典典

甲骨文。防止合并子查询和主查询条件

sql

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

例如：

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）”来优化下一个查询

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

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

该查询失败
导致优化器将子查询与外部查询合并。合并数据库后，尝试将to_number应用于“值”列中的所有值，但是其中一些具有字符串值。女巫提示是否阻止此优化？

PS我想得到相同的结果

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

但是，没有CTE。

阅读 213

2021-05-16

共1个答案

小编典典

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

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

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

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

2021-05-16