Hello, I would like to run a PL/SQL procedure involving 80,000,000 records.
This PL/SQL procedure deletes about 80,000,000 records, backupping them in a GLOBAL TEMPORARY TABLE created with the clause ON COMMIT PRESERVE ROWS.
How can I know how many records can contain this GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS?
What is the size limit for these tables, with a COMMIT only at the end of the PL/SQL procedure?
Hi,
Two factors will limit the number of rows you can insert: temporary space and undo space.
You can put as much data in a temporary table as there is space in the temporary tablespace. If the temporary tablespace is allowed to grow (with autoextend datafiles and tablespace), you will only be limited by the disk space. Now you want to estimate the size of your rows and allow some extra room for overhead. This will give you a rough estimate of the size needed by the temporary tablespace.
A single transaction needs to fit entirely in the undo tablespace. Undo data for insert is smaller than other DMLs, still 80M rows will produce a LOT of undo. If you're also deleting these rows from some other table, the undo will take roughly the same space as the original rows. You're probably using automatic undo management, just set the tablespace and its datafiles to autoextend and you're good.
If this is a one-shot you may want to reduce the size of both temporary and undo tablespaces once you're done. If you're going to perform this operation on a regular basis, just let the tablespaces grow and leave them there afterwards.
The only real problem with a 80M row transaction is the looooooong rollback time you may experience if something goes wrong. Deleted rows in particular will make your rollback a lot longer than the actual deletion.
While there is nothing fundamentally wrong with Oracle and a large transaction (Oracle will scale), dividing the total work into smaller work units will allow you to restart the process faster and on a smaller subset of data in case of failure.
If the only commit is at the end of the procedure then the on commit clause is a bit irrelevant, unless this procedure is just a part of a larger process. When your session ends the GTT data will be gone regardless of the on commit setting, so your 'backup' data is only available within the session that's executing the procedure. From the context given it's not clear if you realise that your 'backup' is temporary.
What the on commit preserve rows clause does is allow you to commit non-GTT data during the session without losing what's in the GTT. Say you want to delete your data in chunks, maybe a million rows at a time. So you identify your million rows, copy them to the GTT, delete them from the original table, and commit. If your on commit setting is delete rows then at this point your GTT is empty again, so you have no backup. But if your on commit is preserve rows then your GTT keeps the million rows you inserted.
Repeat 80 times... and at the end with delete rows the GTT is empty and never held more than a million rows at a time; with preserve rows it will have grown each time and will have all 80 million records. But still only until the session ends.
With preserve rows if you encounter a problem at any point you can reinsert all the data from your 'backup' GTT into your original table. With delete rows you could only reinsert whatever has been deleted since the last commit - but then you might as well just rollback at that point.