oracle 静态与动态 sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3554892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Static vs dynamic sql
提问by Pravin Satav
In my database at several places developers have used dynamic sql instead of static. And they are saying reason for this is to improve the performance. Can someone tell me can if dynamic sql can really increase the performance in stored procedure or plsql block?
在我的数据库中，开发人员在几个地方使用了动态 sql 而不是静态的。他们说这样做的原因是为了提高性能。有人可以告诉我动态sql是否真的可以提高存储过程或plsql块的性能？
Which will execute faster and why ?
1.
哪个会执行得更快，为什么？
1.
begin
execute immediate 'delete from X';
end;
2.
2.
begin
delete from X;
end;
回答by JulesLt
Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.
您的示例代码非常简单，几乎没有区别，但在这种情况下，静态版本很可能会执行得更好。
The main reason to use dynamic SQL for performance is when the SQL statement can vary in a significant way - i.e. you might be able to add extra code to the WHERE clause at runtime based on the state of the system (restrict by a sub-query on Address, if Address entered, etc).
使用动态 SQL 来提高性能的主要原因是当 SQL 语句可能会有很大的变化时 - 即您可以在运行时根据系统状态（受子查询限制）向 WHERE 子句添加额外的代码地址，如果输入地址等）。
Another reason is that sometimes using Bind variables as parameters can be counter-productive.
另一个原因是有时使用 Bind 变量作为参数可能会适得其反。
An example is if you have something like a status field, where data is not evenly distributed (but is indexed).
一个例子是，如果你有一个类似状态字段的东西，其中数据不是均匀分布的（但是被索引了）。
Consider the following 3 statements, when 95% of the data is 'P'rocessed
考虑以下 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
In the final version, Oracle will choose a generic explain plan. In the first version, it may decide the best plan is to start with the index on status (knowing that 'U'nprocessed entries are a very small part of the total).
在最终版本中，Oracle 将选择通用的解释计划。在第一个版本中，它可能决定最好的计划是从状态索引开始（知道“未处理的条目”占总数的很小一部分）。
You could implement that through different static statements, but where you have more complex statements which only change by a couple of characters, dynamic SQL may be a better option.
您可以通过不同的静态语句来实现它，但是如果您有更复杂的语句，这些语句只会改变几个字符，那么动态 SQL 可能是更好的选择。
Downsides
缺点
Each repetition of the same dynamic SQL statement incurs a soft parse, which is a small overhead compared to a static statement, but still an overhead.
每次重复相同的动态 SQL 语句都会导致软解析，与静态语句相比，这是一个很小的开销，但仍然是一个开销。
Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.
每个 NEW sql 语句（动态或静态）还会导致对 SGA（共享内存）的锁定，并可能导致推出“旧”语句。
A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.
一个糟糕但常见的系统设计是让某人使用动态 SQL 来生成仅因键而异的简单选择 - 即
SELECT col FROM table WHERE id = 5
SELECT col FROM table WHERE id = 20
SELECT col FROM table WHERE id = 7
The individual statements will be quick, but the overall system performance will deteriorate, as it is killing the shared resources.
单个语句会很快，但整体系统性能会下降，因为它会杀死共享资源。
Also - it is far harder to trap errors at compile time with dynamic SQL. If using PL/SQL this is throwing away a good compilation time check. Even when using something like JDBC (where you move all your database code into strings - good idea!) you can get pre-parsers to validate the JDBC content. Dynamic SQL = runtime testing only.
此外 - 在编译时使用动态 SQL 捕获错误要困难得多。如果使用 PL/SQL，这会浪费一个很好的编译时间检查。即使使用 JDBC 之类的东西（将所有数据库代码移动到字符串中 - 好主意！），您也可以使用预解析器来验证 JDBC 内容。动态 SQL = 仅运行时测试。
Overheads
间接费用
The overhead of execute immediate is small - it is in the thousandths of a second - however, it can add up if this is inside a loop / on a method called once per object / etc. I once got a 10x speed improvement by replacing dynamic SQL with generated static SQL. However, this complicated the code, and was only done because we required the speed.
立即执行的开销很小 - 它是千分之一秒 - 但是，如果这是在循环内/在每个对象调用一次的方法上，它可以累加。我曾经通过替换动态获得了 10 倍的速度提升带有生成的静态 SQL 的 SQL。但是，这使代码变得复杂，并且只是因为我们需要速度才这样做。
回答by Colin Pickard
Unfortunately, this does vary on a case-by-case basis.
不幸的是，这确实因具体情况而异。
For your given examples, there is probably no measurable difference. But for a more complicated example, you'd probably want to test your own code.
对于您给出的示例，可能没有可测量的差异。但是对于更复杂的示例，您可能想要测试自己的代码。
The link @DumbCoder gave in the comments has some excellent rules of thumb which also apply to Oracle for the most part. You can use something like this to assist you in deciding, but there is no simple rule like "dynamic is faster than static".
@DumbCoder 在评论中给出的链接有一些很好的经验法则，它们在大多数情况下也适用于 Oracle。您可以使用这样的方法来帮助您做出决定，但没有像“动态比静态快”这样的简单规则。
