Oracle 10 中的本地临时表(适用于存储过程的范围)
我是甲骨文新手。 我需要在存储过程中处理大量数据。 我正在考虑使用临时表。 我正在使用连接池,并且该应用程序是多线程的。
有没有一种方法可以为每次调用存储过程创建不同的表实例来创建临时表,以便来自多个存储过程调用的数据不会混淆?
I am new to oracle. I need to process large amount of data in stored proc. I am considering using Temporary tables. I am using connection pooling and the application is multi-threaded.
Is there a way to create temporary tables in a way that different table instances are created for every call to the stored procedure, so that data from multiple stored procedure calls does not mix up?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我最近使用了全局临时表,它的行为非常不受欢迎。
我在过程调用中使用临时表来格式化一些复杂的数据,一旦数据格式化,将数据传递到前端(Asp.Net)。
在对该过程的第一次调用中,我用于获取正确的数据,并且除了当前调用之外,任何后续调用都用于向我提供上次过程调用的数据。
我在网上进行了调查,发现了一个在提交时删除行的选项。
我认为这会解决问题..你猜怎么着? 当我使用“提交删除行”选项时,我总是从数据库中获取 0 行。 所以我必须回到提交时保留行的原始方法,即使在提交事务后也保留行。此选项仅在会话终止后才从临时表中清除行。
然后我发现了这篇文章,并开始了解跟踪会话 call_id 的专栏。
我实施了该解决方案,但仍然无法解决问题。
然后,在开始任何处理之前,我在程序中写了以下语句。
Delete From Temp_table;
上面的statemnet成功了。 我的前端正在使用连接池,并且在每个过程调用之后它都会提交事务,但仍然将连接保留在连接池中,并且后续请求使用相同的连接,因此数据库会话在每次调用后都不会终止。
在进行任何处理之前从临时表中删除行使其工作......
这让我发疯,直到我找到这个解决方案......
I used global temporary table recently and it was behaving very unwantedly manner.
I was using temp table to format some complex data in a procedure call and once the data is formatted, pass the data to fron end (Asp.Net).
In first call to the procedure, i used to get proper data and any subsequent call used to give me data from last procedure call in addition to current call.
I investigated on net and found out an option to delete rows on commit.
I thought that will fix the problem.. guess what ? when i used on commit delete rows option, i always used to get 0 rows from database. so i had to go back to original approach of on commit preserve rows, which preserves the rows even after commiting the transaction.This option clears rows from temp table only after session is terminated.
then i found out this post and came to know about the column to track call_id of a session.
I implemented that solution and still it dint fix the problem.
then i wrote following statement in my procedure before i starting any processing.
Delete From Temp_table;
Above statemnet made the trick. my front end was using connection pooling and after each procedure call it was commitng the transaction but still keeping the connection in connection pool and subsequent request was using the same connection and hence the database session was not terminated after every call..
Deleting rows from temp table before strating any processing made it work....
It drove me nuts till i found this solution....
在 Oracle 中,几乎不需要在运行时创建对象。
全局临时表很可能是解决您问题的最佳解决方案,但是由于您没有确切说明为什么需要临时表,因此我建议您首先检查临时表是否有必要; 您可以用一个 SQL 完成您可能认为需要多个查询的工作的一半时间。
也就是说,我过去在需要为同一会话中的多个上下文在表中维护单独“空间”的应用程序中非常成功地使用了全局临时表; 这是通过添加一个附加的 ID 列(例如“CALL_ID”)来完成的,该列最初设置为 1,并且对该过程的后续调用将增加该 ID。 ID 必须在某处使用全局变量来记住,例如在包体中声明的包全局变量。 EG:
您会发现即使在高并发情况下,GTT 也表现得非常好,当然比使用普通表更好。 最佳实践是设计您的应用程序,使其永远不需要从临时表中删除行 - 因为会话结束时 GTT 会自动清除。
In Oracle, it's almost never necessary to create objects at runtime.
Global Temporary Tables are quite possibly the best solution for your problem, however since you haven't said exactly why you need a temp table, I'd suggest you first check whether a temp table is necessary; half the time you can do with one SQL what you might have thought would require multiple queries.
That said, I have used global temp tables in the past quite successfully in applications that needed to maintain a separate "space" in the table for multiple contexts within the same session; this is done by adding an additional ID column (e.g. "CALL_ID") that is initially set to 1, and subsequent calls to the procedure would increment this ID. The ID would necessarily be remembered using a global variable somewhere, e.g. a package global variable declared in the package body. E.G.:
You'll find GTTs perform very well even with high concurrency, certainly better than using ordinary tables. Best practice is to design your application so that it never needs to delete the rows from the temp table - since the GTT is automatically cleared when the session ends.
如果您使用事务(而不是会话)级临时表,那么这可能已经达到您想要的效果...只要每个调用仅包含一个事务? (您没有提供足够的详细信息来明确是否属于这种情况)
因此,要明确的是,只要每个调用仅包含一个事务,那么您使用的是连接池,因为无论如何在每次 COMMIT 或 ROLLBACK 后数据都会从临时表中清除。
(另一种选择是使用 EXECUTE IMMEDIATE 在每次调用中创建一个唯一命名的临时表。但不确定这样做的性能如何。)
IF you're using transaction (rather than session) level temporary tables, then this may already do what you want... so long as each call only contains a single transaction? (you don't quite provide enough detail to make it clear whether this is the case or not)
So, to be clear, so long as each call only contains a single transaction, then it won't matter that you're using a connection pool since the data will be cleared out of the temporary table after each COMMIT or ROLLBACK anyway.
(Another option would be to create a uniquely named temporary table in each call using EXECUTE IMMEDIATE. Not sure how performant this would be though.)
你说你是 Oracle 新手。 我猜您已经习惯了 SQL Server,其中使用临时表是很常见的。 Oracle 的工作方式不同,因此不太常见,因为它的必要性较低。
请记住,使用临时表会产生以下开销:
Most of that activity is useless in terms of helping you get stuff done. A better idea is to see if you can do everything in a single action, preferably pure SQL.
顺便说一句,您提到的连接池引发了另一个问题。 处理大量数据的进程不适合在 OLTP 模式下运行。 您确实应该考虑启动后台(即异步)进程(可能是数据库作业)来运行存储过程。 如果您想定期运行此作业,则尤其如此,因为我们可以使用 DBMS_SCHEDULER 来自动管理此类事物。
You say you are new to Oracle. I'm guessing you are used to SQL Server, where it is quite common to use temporary tables. Oracle works differently so it is less common, because it is less necessary.
Bear in mind that using a temporary table imposes the following overheads:
Most of that activity is useless in terms of helping you get stuff done. A better idea is to see if you can do everything in a single action, preferably pure SQL.
Incidentally, your mention of connection pooling raises another issue. A process munging large amounts of data is not a good candidate for running in an OLTP mode. You really should consider initiating a background (i.e. asysnchronous) process, probably a database job, to run your stored procedure. This is especially true if you want to run this job on a regular basis, because we can use DBMS_SCHEDULER to automate the management of such things.