静态sql与动态sql

Question

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

哪个会执行得更快?为什么？

begin  
    execute immediate 'delete from X';  
end;

2.

begin  
    delete from X;  
end;

Answer 1

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

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

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

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

考虑以下3个语句，当95%的数据是‘P’时

   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将选择通用的解释计划。在第一个版本中，它可能会决定最好的计划是从状态索引开始(知道‘U’n处理的条目只占总数的很小一部分)。

您可以通过不同的静态语句来实现这一点，但是如果您有更复杂的语句，并且只有几个字符发生变化，那么动态SQL可能是更好的选择。

对的不利影响

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

每条新的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 =仅运行时测试。

的开销

execute immediate的开销很小-它只有千分之一秒-但是，如果这是在一个循环中/在一个称为每个对象一次/等等的方法上，它可能会增加。我曾经通过用生成的静态SQL替换动态SQL获得了10倍的速度改进。然而，这使代码变得复杂，这样做只是因为我们需要速度。

Answer 2

不幸的是，这在具体情况下是不同的。

对于给定的示例，可能没有可测量的差异。但对于更复杂的示例，您可能希望测试自己的代码。

评论中给出的@DumbCoder链接有一些很好的经验法则，这些规则在很大程度上也适用于Oracle。你可以使用这样的东西来帮助你做出决定，但是没有像“动态比静态快”这样的简单规则。