oracle 游标有什么问题？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/743183/
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
What is wrong with Cursors?
提问by Chakra
SQL Server developers consider Cursors a bad practise , except under some circumstances. They believe that Cursors do not use the SQL engine optimally since it is a procedural construct and defeats the Set based concept of RDBMS.
SQL Server 开发人员认为 Cursors 是一种不好的做法，除非在某些情况下。他们认为 Cursors 并没有以最佳方式使用 SQL 引擎，因为它是一个过程构造并且违背了 RDBMS 的基于 Set 的概念。
However, Oracle developers do not seem to recommend against Cursors. Oracle's DML statements themselves are implicit cursors.
但是，Oracle 开发人员似乎并不反对 Cursors。Oracle 的 DML 语句本身就是隐式游标。
Why this difference in approach ? Is it because of the way these 2 products are made , or does this advise apply to both products?
为什么会有这种方法上的差异？是因为这两种产品的制造方式，还是该建议适用于两种产品？
回答by Quassnoi
What's wrong with cursors is that they are often abused, both in Oracleand in MS SQL.
游标的错误在于它们经常被滥用，无论是 inOracle还是 in MS SQL。
Cursor are for keeping a stable resultset which you can retrieve row-by-row. They are implicitly created when your query is run, and closed when it's finished.
光标用于保持稳定的结果集，您可以逐行检索。它们在您的查询运行时隐式创建，并在查询完成时关闭。
Of course keeping such a resultset requires some resources: locks, latches, memory, even disk space.
当然，保持这样的结果集需要一些资源：locks、latches、memory、 甚至disk space。
The faster these resources are freed, the better.
释放这些资源的速度越快越好。
Keeping a cursor open is like keeping a fridge door open
保持光标打开就像打开冰箱门一样
You don't do it for hours without necessity, but it does not mean you should never open your fridge.
你不会在没有必要的情况下连续几个小时这样做，但这并不意味着你永远不应该打开你的冰箱。
That means that:
这意味着：
- You don't get your results row-by-row and sum them: you call the
SQL'sSUMinstead. - You don't execute whole query and get the first results from the cursor: you append a
rownum <= 10condition to your query
- 您不会逐行获得结果并将它们相加：而是调用
SQL'sSUM。 - 您不会执行整个查询并从游标中获取第一个结果：您将
rownum <= 10条件附加到您的查询中
, etc.
， 等等。
As for Oracle, processing your cursors inside a procedure requires infamous SQL/PLSQL context switchwhich happens every time you get a result of an SQLquery out of the cursor.
至于在Oracle过程中处理游标需要臭名昭著的SQL/PLSQL context switch，每次SQL从游标中获得查询结果时都会发生这种情况。
It involves passing large amounts of data between threads and synchronizing the threads.
它涉及在线程之间传递大量数据并同步线程。
This is one of the most irritating things in Oracle.
这是最令人恼火的事情之一Oracle。
One of the less evident consequences of that behaviour is that triggers in Oracle should be avoided if possible.
这种行为不太明显的后果之一是，如果可能，应该避免 Oracle 中的触发器。
Creating a trigger and calling a DMLfunction is equal to opening the cursor selecting the updated rows and calling the trigger code for each row of this cursor.
创建触发器并调用DML函数相当于打开游标选择更新的行，并为该游标的每一行调用触发代码。
Mere existence of the trigger (even the empty trigger) may slow down a DMLoperation 10 timesor more.
仅仅存在触发器（即使是空触发器）可能会减慢DML操作10 times或更多。
A test script on 10g:
一个测试脚本10g：
SQL> CREATE TABLE trigger_test (id INT NOT NULL)
2 /
Table created
Executed in 0,031 seconds
SQL> INSERT
2 INTO trigger_test
3 SELECT level
4 FROM dual
5 CONNECT BY
6 level <= 1000000
7 /
1000000 rows inserted
Executed in 1,469 seconds
SQL> COMMIT
2 /
Commit complete
Executed in 0 seconds
SQL> TRUNCATE TABLE trigger_test
2 /
Table truncated
Executed in 3 seconds
SQL> CREATE TRIGGER trg_test_ai
2 AFTER INSERT
3 ON trigger_test
4 FOR EACH ROW
5 BEGIN
6 NULL;
7 END;
8 /
Trigger created
Executed in 0,094 seconds
SQL> INSERT
2 INTO trigger_test
3 SELECT level
4 FROM dual
5 CONNECT BY
6 level <= 1000000
7 /
1000000 rows inserted
Executed in 17,578 seconds
1.47seconds without a trigger, 17.57seconds with an empty trigger doing nothing.
1.47没有触发器的17.57秒数，空触发器的秒数什么都不做。
回答by Gary.Ray
From MSDN:Cursor Implementations
Using a cursor is less efficient than using a default result set. In a default result set the only packet sent from the client to the server is the packet containing the statement to execute. When using a server cursor, each FETCH statement must be sent from the client to the server, where it must be parsed and compiled into an execution plan.
If a Transact-SQL statement will return a relatively small result set that can be cached in the memory available to the client application, and you know before executing the statement that you must retrieve the entire result set, use a default result set. Use server cursors only when cursor operations are required to support the functionality of the application, or when only part of the result set is likely to be retrieved.
使用游标的效率低于使用默认结果集。在默认结果集中，从客户端发送到服务器的唯一数据包是包含要执行的语句的数据包。当使用服务器游标时，每个 FETCH 语句都必须从客户端发送到服务器，在那里它必须被解析并编译成一个执行计划。
如果 Transact-SQL 语句将返回一个相对较小的结果集，该结果集可以缓存在客户端应用程序可用的内存中，并且您在执行该语句之前知道必须检索整个结果集，请使用默认结果集。仅当需要游标操作来支持应用程序的功能时，或者只有部分结果集可能被检索时，才使用服务器游标。
I'm not an Oracle DBA, so I can't really speak to how the implementations are different. However, from a programming standpoint, set based operations are almost always faster than processing results in a cursor.
我不是 Oracle DBA，所以我不能真正谈论实现的不同之处。然而，从编程的角度来看，基于集合的操作几乎总是比在游标中处理结果快。
回答by Esteban Küber
I have always been told that cursors where evil, but always by MS SQL Server gurus, because of it's bad performance. Regarding Oracle's PL/SQL I found this saying when to usecursors:
我一直被告知游标在哪里邪恶，但总是被 MS SQL Server 专家告知，因为它的性能很差。关于 Oracle 的 PL/SQL，我发现这句话说的是何时使用游标：
Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL.
不使用游标会导致重复解析。如果未使用绑定变量，则将对所有 SQL 语句进行硬解析。这对性能有一个数量级的影响，而且它是完全不可扩展的。将游标与可打开游标并多次执行的绑定变量一起使用。对生成动态 SQL 的应用程序持怀疑态度。
As cursors are implicitly createdon every operation, it doesn't seem so performance-punishing to use them when needed :)
由于游标是在每个操作中隐式创建的，因此在需要时使用它们似乎并没有那么影响性能:)
Remember that Oracle's implementation is closer to Postgres than to Sybase (Genesis of MS SQL Server), so performance will be different for each on different tasks.If you can, avoid the hustle of tweak for performance on systems that can swap able back-ends, go for least common denominator if you need to work with both. /tangential_topic
请记住，Oracle 的实现更接近 Postgres 而不是 Sybase（MS SQL Server 的起源），因此每个任务在不同任务上的性能会有所不同。如果可以，请避免在可以交换后端的系统上进行性能调整，如果您需要同时使用两者，请选择最小公分母。/tangential_topic
回答by Alan Featherston
why-do-people-hate-sql-cursors-so-much
why-is-it-considered-bad-practice-to-use-cursors-in-sql-server
and a very good article here...
这里有一篇非常好的文章......
回答by Al W
I'm sure someone can explain in more detail, but it basically comes down to cursors in SQL server are SLOW.
我相信有人可以更详细地解释一下，但基本上归结为 SQL 服务器中的游标很慢。
回答by Jamie Ide
The other answers correctly point out the performance issues with cursors, but they don't mention that SQL and relational databases are best at set-based operations and cursors are fundamentally for iterative operations. There are some operations (in the broader sense) that are easier to perform using cursors, but when working with SQL you should always be thinking about working with sets of data. Cursors are often misused because the coder didn't grasp how to perform the task using set-based operations.
其他答案正确地指出了游标的性能问题，但他们没有提到 SQL 和关系数据库最适合基于集合的操作，而游标基本上用于迭代操作。有些操作（广义上）使用游标更容易执行，但是在使用 SQL 时，您应该始终考虑使用数据集。游标经常被误用，因为编码人员没有掌握如何使用基于集合的操作来执行任务。
