如何防止 Oracle 基于成本的优化器进行不良优化？

Question

考虑以下场景。我在一个我无法控制的模式中有一个表（一个stupid_table）。这是第三方，禁止入内。不敏感。我可以查询它，但不能添加索引或新表或更改设计。

Stupid_table 中的每一列都是一个 VARCHAR2(50 BYTE) ，有很多列，但我只需要其中两列：row_type 和 <代码>magic_number。 magic_number 使用整数的字符串表示形式填充，但仅其中 row_type 设置为 'DATA' ，我只需要大于零的幻数。

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

这会导致“无效数字”Oracle 错误，因为基于成本的优化器 (CBO) 选择在检查 row_type 之前评估 TO_NUMBER，并且存在一大堆具有不同 row_type 和 magic_number 字段不同用途的行。

好的，如果我先过滤行，然后进行比较怎么样？

SELECT TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

现在，CBO 似乎发现该查询非常简单，并且忽略了我使用的狡猾方法，产生了与原始查询计划相同的查询计划。

最后，沮丧之余，我采取了肮脏的黑客手段：使用 /*+RULE*/ 查询提示强制 Oracle 使用旧的基于规则的优化器。这就像一个梦想，但它不应该是必要的，更不用说它使用了不再支持的 Oracle 功能。

有更好的方法吗？

Answer 1

我将通过编写自己的转换函数来解决这个问题，该函数会吞掉异常，

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN number
IS 
BEGIN
  RETURN to_number( p_str );
EXCEPTION
  WHEN OTHERS THEN
    RETURN null;
END;

即然后更改查询

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND MY_TO_NUMBER(magic_number) > 0;

，除非您可以采用 RBO 生成的查询计划并创建一个配置文件来强制 CBO 使用该计划。这可能比尝试提供一套完整的提示来防止 CBO 在 ROW_TYPE 谓词之前应用 MAGIC_NUMBER 谓词更容易管理。

Answer 2

让 CASE 为您完成工作

select to_number(magic_number) 
from stupid_table
where row_type = 'DATA'
and case when row_type = 'DATA' then to_number(magic_number) else 0 end > 0

在我的测试用例中，我无法重新创建您的错误，因此想知道是否有一些 DATA 行中没有数字。但这也可能是优化器处理我的查询的方式。

我认为 no_merge 提示也可能会解决您的问题，但由于我无法重现该问题，所以我不能确定。

SELECT --+ no_merge(t)
  TO_NUMBER(t.magic_number)
FROM (
    SELECT magic_number
    FROM mike_temp_stupid_table
    WHERE row_type = 'DATA'
) t
where TO_NUMBER(t.magic_number) > 0;

Answer 3

您可以完全避免使用 TO_NUMBER 吗？看起来无论如何都会提高性能。比如：

WHERE t.magic_number != '0'

如果可能有负数，或者数字是浮点数，您可能需要额外的检查，但这看起来确实可行。

Answer 4

确切的方法是使用 ordered_predicates 提示来更改 WHERE 条件的评估顺序。

文档：
Oracle ORDERED_PREDICATES 提示

SELECT /*+ ORDERED_PREDICATES */ TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND TO_NUMBER(magic_number) > 0;

现在尝试交换条件，你会再次得到错误。还请考虑其他答案，因为我也怀疑唤起 TO_NUMBER 是您的最佳解决方案。

Answer 5

创建仅包含“DATA”的 row_type 的愚蠢表切片的物化视图怎么样？

Answer 6

我通常添加一个 rownum 来停止谓词推送。 （提示也可以做到这一点，但它们很容易出错，对于这种类型的问题，如果你弄错了，你可能不会立即注意到。）此外，你可能应该添加一条评论，这样别人就不会稍后尝试“优化”您的代码并删除看起来不必要的逻辑。

SELECT TO_NUMBER(t.magic_number)
FROM (
    --Bad data, use rownum for type safety
    SELECT magic_number, rownum
    FROM stupid_table
    WHERE row_type = 'DATA'
) t
AND TO_NUMBER(t.magic_number) > 0;

Answer 7

with 语句允许您应用特定的求值顺序。

WITH
has_numerics_only AS
(
    SELECT magic_number
    FROM stupid_table
    WHERE row_type = 'DATA'
)
SELECT TO_NUMBER(t.magic_number)
FROM has_numerics_only
WHERE TO_NUMBER(t.magic_number) > 0;

还要考虑一个或多个“DATA”行中确实存在错误数据的可能性。

Answer 8

您可以尝试：

SELECT TO_NUMBER(magic_number)
FROM stupid_table
WHERE row_type = 'DATA'
AND REGEXP_LIKE(magic_number, '^\d{1,}
如果这仍然不起作用，将条件移至 HAVING 子句可能会强制优化器首先对其进行评估。
SELECT TO_NUMBER(magic_number)
FROM (
SELECT magic_number
FROM stupid_table
WHERE row_type = 'DATA'
GROUP BY magic_number
HAVING REGEXP_LIKE(magic_number, '^\d{1,}
如果做不到这一点，物化视图或使用 PL/SQL 游标可能是唯一的方法。
);

如果这仍然不起作用，将条件移至 HAVING 子句可能会强制优化器首先对其进行评估。

如果做不到这一点，物化视图或使用 PL/SQL 游标可能是唯一的方法。
)) ilv;

如果做不到这一点，物化视图或使用 PL/SQL 游标可能是唯一的方法。

);

如果这仍然不起作用，将条件移至 HAVING 子句可能会强制优化器首先对其进行评估。

如果做不到这一点，物化视图或使用 PL/SQL 游标可能是唯一的方法。