静态与动态 sql [英] Static vs dynamic sql
问题描述
在我的数据库中，开发人员在几个地方使用了动态 sql 而不是静态的.他们说这样做的原因是为了提高性能.有人可以告诉我动态sql是否真的可以提高存储过程或plsql块的性能?
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?
哪个会执行得更快，为什么?
1.
Which will execute faster and why ?
1.
begin
execute immediate 'delete from X';
end;
2.
begin
delete from X;
end;
推荐答案
您的示例代码非常简单，几乎没有区别，但在这种情况下，静态版本很可能会执行得更好.
Your example code is so simple that there will be little difference, but in that case the static version would most likely execute better.
使用动态 SQL 来提高性能的主要原因是当 SQL 语句可以有很大的变化时 - 即您可以在运行时根据系统状态(受限制)向 WHERE 子句添加额外的代码地址的子查询，如果输入地址等).
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).
另一个原因是有时使用绑定变量作为参数会适得其反.
Another reason is that sometimes using Bind variables as parameters can be counter-productive.
一个例子是，如果你有一个类似状态字段的东西，其中数据不是均匀分布的(但被索引了).
An example is if you have something like a status field, where data is not evenly distributed (but is indexed).
考虑以下 3 个语句，当 95% 的数据被处理"时
Consider the following 3 statements, when 95% of the data is 'P'rocessed
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 会选择一个通用的解释计划.在第一个版本中，它可能决定最好的计划是从状态索引开始(知道'未处理的条目只占总数的很小一部分).
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).
您可以通过不同的静态语句来实现它，但是如果您有更复杂的语句，这些语句只会改变几个字符，那么动态 SQL 可能是更好的选择.
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 语句的每次重复都会导致软解析，与静态语句相比，这是一个很小的开销，但仍然是一个开销.
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 语句(动态或静态)也会导致对 SGA(共享内存)的锁定，并可能导致推出旧"语句.
Each NEW sql statement (dynamic or static) also incurs a lock on the SGA (shared memory), and can result in pushing 'old' statements out.
一个糟糕但常见的系统设计是让某人使用动态 SQL 生成仅因键而异的简单选择 - 即
A bad, but common, system design is for someone to use dynamic SQL to generate simple selects that only vary by key - i.e.
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.
此外 - 在编译时使用动态 SQL 捕获错误要困难得多.如果使用 PL/SQL，这会浪费一个很好的编译时间检查.即使使用 JDBC 之类的东西(将所有数据库代码移动到字符串中 - 好主意！)，您也可以使用预解析器来验证 JDBC 内容.动态 SQL = 仅运行时测试.
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.
间接费用
立即执行的开销很小 - 它是千分之一秒 - 但是，如果这是在循环内/在每个对象调用一次的方法上/等等，它可以加起来.我曾经获得了 10 倍的速度提升通过用生成的静态 SQL 替换动态 SQL.然而，这使代码变得复杂，并且只是因为我们需要速度.
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.
这篇关于静态与动态 sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
