Local Temporary table in Oracle 10 (for the scope of Stored Procedure)

by

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:

  1. read data to populate temporary table
  2. write temporary table data to file
  3. read data from temporary table as your process starts

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.

More Related Contents:

  1. How do I reset a sequence in Oracle?
  2. ORA-00904: invalid identifier
  3. How do you create a temporary table in an Oracle database?
  4. Is there any boolean type in Oracle databases?
  5. SQL ROWNUM how to return rows between a specific range
  6. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  7. Call a stored procedure with another in Oracle
  8. MySQL equivalent of DECODE function in Oracle
  9. SQL recursive query on self referencing table (Oracle)
  10. create table with sequence.nextval in oracle [duplicate]
  11. Procedure or function !!! has too many arguments specified
  12. Solution to “cannot perform a DML operation inside a query”?
  13. Deferrable Constraints in SQL Server
  14. Array in IN() clause oracle PLSQL
  15. Oracle (Old?) Joins – A tool/script for conversion?
  16. Comma separated values in a database field
  17. What is it exactly a BLOB in a DBMS context
  18. What can cause an Oracle ROWID to change?
  19. Is there a version control system for database structure changes?
  20. Inserting into Oracle and retrieving the generated sequence ID
  21. How do I call an Oracle stored procedure from an Excel VBA script?
  22. How to use Alias in Where clause? [duplicate]
  23. How do I list all the columns in a table?
  24. Functions vs Stored Procedures
  25. How do you interpret a query’s explain plan?
  26. Oracle DB: How can I write query ignoring case?
  27. How do NULL values affect performance in a database search?
  28. Oracle , != , ^= operators
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

Leave a Comment