使用VARCHAR2列调整查询

Question

有一个存储过程，它构建一个动态查询字符串，然后执行它。sp在开发和测试环境中运行良好，但客户公司的DBA已经通知说，这个查询在生产中对数据库的打击非常大。IT领域已要求我们调整查询。到目前为止，我们已经将几乎所有的sp从动态构建查询字符串转移到了一个执行速度非常快的大型查询中(与旧查询相比)。

我们发现(除其他外)sp通过评估参数是否具有默认值或实际值(即)来构建查询字符串的where子句

IF P_WORKFLOWSTATUS <> 0 THEN
L_SQL := TRIM(L_SQL) || ' AND WORKFLOW.STATUS = ' || TO_CHAR(P_WORKFLOWSTATUS);
END IF;

因此，我们将这种行为优化为

WHERE
...
AND (WORKFLOW.STATUS = P_WORKFLOWSTATUS OR P_WORKFLOWSTATUS = 0)

这种更改改进了影响数字列的查询，但我们发现VARCHAR2参数和列存在问题。当前行为为

--CLIENT.CODE is a VARCHAR2(14) column and there is an unique index for this column.
--The data stored in this field is like 'N0002077123', 'E0006015987' and similar
IF NVL(P_CLIENT_CODE, '') <> '' THEN
L_SQL := TRIM(L_SQL) || ' AND CLIENT.CODE = ''' || P_CLIENT_CODE || '''';
END IF;

我们试图通过将其更改为查询的优化版本

WHERE
...
AND (CLIENT.CODE = P_CLIENT_CODE OR NVL(P_CLIENT_CODE, '') = '')

但这一更改使查询失去了性能。有没有一种方法可以优化查询的这一部分，或者我们应该(再次)将我们的大查询转换为动态查询，只是为了评估这个VARCHAR2参数是否应该添加到where子句中？

提前谢谢。

Answer 1

Oracle将空字符串''视为NULL。所以这个条件NVL(P_CLIENT_CODE, '') = ''实际上没有多大意义。此外，它总是错误的，因为在这里我们要检查NULL的相等性，这总是错误的。为此，您可能也可能应该将查询的这一部分重新编码为：

WHERE
...
AND ( (CLIENT.CODE = P_CLIENT_CODE) OR (CLIENT IS NULL) )

Answer 2

我建议或将此varchar2参数移回动态，或使用以下参数：

WHERE
...
AND CLIENT.CODE = nvl(P_CLIENT_CODE,CLIENT.CODE)

并确保你在client.code上有索引

Answer 3

当然，正如前面所说，您需要执行正确的null检查。

然而，诀窍是，之间的区别

AND (CLIENT.CODE = P_CLIENT_CODE OR NVL(P_CLIENT_CODE, '') = '') 

和

AND ( (CLIENT.CODE = P_CLIENT_CODE) OR (CLIENT IS NULL) )

不太可能仅凭其自身造成性能问题。我甚至想说，使用第二个子句的查询可能比使用第一个子句执行更差，因为它将为更多的行生成true，从而为后续联接/订单/文件等产生更大的结果集。

我敢打赌，将此子句添加到查询中会以某种方式破坏其最佳执行计划。例如，对于过时的统计信息，优化器可以做出次优决策，在client.code上选择未选择的索引，而不是其他可用索引。

然而，如果没有看到慢查询的实际执行计划(而不是预期的执行计划，您可以使用explain plan命令！)和您的表结构，很难确定。