在oracle中，显式游标是否将整个查询结果加载到内存中？
我有一个大约有 10 亿行的表。我是唯一的用户，所以不存在锁等争用。 我注意到，当我运行这样的东西时：在
DECLARE
CURSOR cur IS SELECT col FROM table where rownum < N;
BEGIN
OPEN cur;
LOOP
dbms_output.put_line("blah")
END LOOP;
CLOSE cur;
END;
我按下回车键的时间和输出开始流入的时间之间有一个滞后。如果 N 很小，那么它是微不足道的。对于较大的 N（或没有 WHERE 子句），此延迟约为小时。
如您所知，我对 Oracle 很陌生，我假设游标只是在表中保留一个指针，它们在循环的每次迭代中都会更新该指针。所以我没想到滞后与执行迭代的表的大小成比例。这是错误的吗？游标是否会在迭代查询结果之前加载整个查询结果？
有没有一种方法可以在没有初始开销的情况下逐行迭代表？
I have a table with about 1 billion rows. I'm the sole user so there's no contention on locks, etc.
I noticed that when I run something like this:
DECLARE
CURSOR cur IS SELECT col FROM table where rownum < N;
BEGIN
OPEN cur;
LOOP
dbms_output.put_line("blah")
END LOOP;
CLOSE cur;
END;
there is a lag between the time when I hit enter and the time the output begins to flow in. If N is small then it's insignificant. For large N (or no WHERE clause) this lag is on the order of hours.
I'm new to oracle as you can tell, and I assumed that cursors just keep a pointer in the table which they update on every iteration of the loop. So I didn't expect a lag proportional to the size of the table over which iteration is performed. Is this wrong? Do cursors load the entire query result prior to iterating over it?
Is there a way to iterate over a table row by row without an initial overhead?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您所看到的是，直到程序完成后才显示 DBMS_OUTPUT.PUT_LINE 的输出。它没有告诉您有关查询返回第一行的速度的任何信息。 （我假设您打算在示例中实际获取数据）。
监视会话的方法有很多种，其中一种是这样的：
当该会话正在运行时，从另一个会话运行：
您将看到 ACTION 的值随着游标获取行而不断变化。
What you are seeing is that the output from DBMS_OUTPUT.PUT_LINE is not displayed until the program has finished. It doesn't tell you anything about how fast the query returned a first row. (I assume you intended to actually fetch data in your example).
There are many ways you can monitor a session, one is like this:
While that is running, from another session run:
You will see that the value of ACTION keeps changing as the cursor fetches rows.
我还喜欢监视 v$session_longops 是否有 Oracle 优化器认为是“长操作”的操作：
select message, time_remaining
来自 v$session_longops
其中 time_remaining > 0;
I also like to monitor v$session_longops for operations deemed by the Oracle optimizer to be "long operations":
select message, time_remaining
from v$session_longops
where time_remaining > 0;