sql - 静态 vs 动态 sql

Question

在我的数据库中，开发人员在几个地方使用了动态 sql 而不是静态的。他们说这样做的原因是为了提高性能。有人可以告诉我动态sql是否真的可以提高存储过程或plsql块的性能？

哪个会执行得更快，为什么？
1.

begin  
    execute immediate 'delete from X';  
end;

2.

begin  
    delete from X;  
end;

Answer 1

您的示例代码非常简单，几乎没有区别，但在这种情况下，静态版本很可能会执行得更好。

使用动态 SQL 提高性能的主要原因是当 SQL 语句可能会有很大的变化时 - 即您可以在运行时根据系统状态(受子查询限制)向 WHERE 子句添加额外的代码地址，如果输入地址等)。

另一个原因是有时使用 Bind 变量作为参数可能会适得其反。

一个例子是，如果你有一个类似状态字段的东西，其中数据不是均匀分布的(而是被索引的)。

考虑以下 3 个语句，当 95% 的数据被“处理”时

   SELECT col FROM table 
   WHERE status = 'U'-- unprocessed
   AND company = :company

   SELECT col FROM table 
   WHERE status = 'P' -- processed
   AND company = :company

   SELECT col FROM table
   WHERE status = :status
   AND company = :company

在最终版本中，Oracle 将选择一个通用的解释计划。在第一个版本中，它可能决定最好的计划是从状态索引开始(知道“未处理的条目”占总数的很小一部分)。

您可以通过不同的静态语句来实现它，但是如果您有更复杂的语句，这些语句只会改变几个字符，那么动态 SQL 可能是更好的选择。

缺点

每次重复相同的动态 SQL 语句都会导致软解析，与静态语句相比，这是一个很小的开销，但仍然是一个开销。

每个 NEW sql 语句(动态或静态)还会导致对 SGA(共享内存)的锁定，并可能导致推出“旧”语句。

一个糟糕但常见的系统设计是让某人使用动态 SQL 生成仅因键而异的简单选择 - 即

SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7

单个语句会很快，但整体系统性能会下降，因为它会杀死共享资源。

此外 - 在编译时使用动态 SQL 捕获错误要困难得多。如果使用 PL/SQL，这会浪费一个很好的编译时间检查。即使使用 JDBC 之类的东西(将所有数据库代码移动到字符串中 - 好主意!)，您也可以使用预解析器来验证 JDBC 内容。动态 SQL = 仅运行时测试。

管理费用

立即执行的开销很小 - 它是千分之一秒 - 但是，如果这是在循环内/在每个对象调用一次的方法上，它可以累加。我曾经通过替换动态获得了 10 倍的速度提升带有生成的静态 SQL 的 SQL。然而，这使代码变得复杂，并且只是因为我们需要速度。