Oracle 10 中的本地临时表(适用于存储过程的范围)
我是 oracle 的新手.我需要在存储过程中处理大量数据.我正在考虑使用临时表.我正在使用连接池并且应用程序是多线程的.
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?
推荐答案
您说您是 Oracle 的新手.我猜你已经习惯了 SQL Server，在那里使用临时表是很常见的.Oracle 的工作方式不同，因此不太常见，因为它不太必要.
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:
- 读取数据以填充临时表
- 将临时表数据写入文件
- 在进程启动时从临时表读取数据
- read data to populate temporary table
- write temporary table data to file
- read data from temporary table as your process starts
大部分活动在帮助您完成工作方面毫无用处.一个更好的主意是看看您是否可以在单个操作中完成所有操作，最好是纯 SQL.
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 来自动管理此类事情.
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.
相关文章