0

I read that savepoints in Oracle global temporary tables delete all the data, but when I tested on Oracle 11g they worked like heap tables. Can anybody explain?

insert into table_1 values('one');
insert into table_1 values('two');
savepoint f1;
insert into table_1 values('three');
insert into table_1 values('four');

rollback to f1;

-- the records in table are 2 records just like heap tables, but I read that
-- savepoints in GTT truncates all the data
Mureinik
  • 297,002
  • 52
  • 306
  • 350
Pankaj Pradhan
  • 9
  • 1

2 Answers2

0

Where did you read this? I suspect not in the Oracle SQL Reference. So the explanation is simple: the author of that assertion hadn't tested the behaviour of global temporary tables. Either that or you were reading a description of some other SQL implementation, such as DerbyDB.

For the sake of completeness, let's rule out the role of transaction or session scope. Here are two global temporary tables:

create global temporary table gtt1
   ( col1 varchar2(30) )
   ON COMMIT PRESERVE ROWS 
/

create global temporary table gtt2
   ( col1 varchar2(30) )
   ON COMMIT DELETE ROWS 
/

Let's run your experiment for the one with session scope:

SQL> insert into gtt1 values('one');

1 row created.

SQL> insert into gtt1 values('two');

1 row created.

SQL> savepoint f1;

Savepoint created.

SQL> insert into gtt1 values('three');

1 row created.

SQL> insert into gtt1 values('four');

1 row created.

SQL> rollback to f1;

Rollback complete.

SQL> select * from gtt1;

COL1
------------------------------
one
two

SQL>

Same result for the table with transaction scope:

SQL> insert into gtt2 values('five');

1 row created.

SQL> insert into gtt2 values('six');

1 row created.

SQL> savepoint f2;

Savepoint created.

SQL> insert into gtt2 values('seven');

1 row created.

SQL> insert into gtt2 values('eight');

1 row created.

SQL> rollback to f2;

Rollback complete.

SQL> select * from gtt2;

COL1
------------------------------
five
six

SQL>

Actually this is not surprising. The official Oracle documentation states:

"The temporary table definition persists in the same way as the definitions of regular tables"

Basically they are heap tables. The differences are:

  • the scope (visibility) of the data
  • the tablespace used to persist data (global temporary tables write to a temporary tablespace).
APC
  • 144,005
  • 19
  • 170
  • 281
0

I think you misunderstand - if you rollback to a savepoint then Oracle should undo all the work done after the savepoint (while still keeping any uncommitted work that was done prior to the savepoint).

For a temporary table, Oracle lazily allocates storage (a temporary segment for your session) when you put stuff in, and that when the data is done with (either at the end of the session, or at the end of the transaction, depending on the type) it can just deallocate the storage rather than individually deleting the rows, rather like what happens when you TRUNCATE a normal table.

I was interested to find out what happened if you had a savepoint before any data was put in, and rolled back to that savepoint - would Oracle deallocate the storage or would it keep the storage and delete the rows from within it?

It turns out the former - it behaves like a truncate.

SAVEPOINT f0;
SELECT * FROM v$tempseg_usage; -- Should show nothing for your session
insert into table_1 values('one');
insert into table_1 values('two');
SELECT * FROM v$tempseg_usage; -- Should show a DATA row for your session
savepoint f1;
insert into table_1 values('three');
insert into table_1 values('four');
rollback to f1; -- Undo three and four but preserve one and two
SELECT * FROM v$tempseg_usage; -- Still shows a DATA row for your session
rollback to f0; -- Undo all the inserts
SELECT * FROM v$tempseg_usage; -- row for your session has gone

The reason this matters is that when you do a normal delete - rather than a truncate - then any full scan of the table will still have to sift through all the data blocks to see if they have any data in. DML against an empty table can potentially incur a lot of I/O if the table had a lot of data in it at some time before!

I am trying to speed up some code that is doing exactly that - it isshoving some stuff into a temporary table as a scratchpad, partly so it canjoin to a permanent table, and returning a result to its caller. The temporary table is only for the benefit of this routine, so it's safe to clear it down at the end of the routine, but it might be called many times within a parent transaction, so I can't truncate (TRUNCATE is DDL and so commits the transaction) but I can't not clear it down either, or invocations within the same transaction will pick up one anothers' rows. Clearing down by a DELETE is causing quite a bit of overhead, especially as there is no index on the table and so selects against it will always full scan.

The option I am exploring is to have a SAVEPOINT at the start of the routine, do my temporary work, and then roll back to the savepoint just before it returns the result. Another option might be to put the routine inside an autonomous transaction, but it would mean porting C code to a PL/SQL stored procedure, and wouldn't work anyway if the temporary table needs to be joined to uncommitted data inserted by the caller.

Note that I did my research in 12c - there have been some improvements to temporary tables in this release (see https://oracle-base.com/articles/misc/temporary-tables) but I don't think that affects the behaviour wrt savepoints.

googlydalek
  • 60
  • 1
  • 1
  • 9