I've got two temporary tables, a first level JOIN across 5 tables and a couple hundred thousand rows down to a table with 6 columns and something less than 10k rows. As the user in one session uses filters, I have a second temporary table that contains the filtered results, those filters change per the end-user's whim.
Every time the filters change, I need to TRUNCATE the filtered table and recreate it from the first table.
In essence, I'm bound by permissions, DBA does not want to give me TRUNCATE permissions on a TEMPORARY TABLE.
DELETE FROM table WHERE 1=1;
Is just the wrong method (though works, admittedly). Closing and opening a new session has me lose the first table, opening a second session for the second table keeps me from having access to the first.
Using SQL Developer, it looks like DELETE and ROLLBACK take roughly the same time, maybe a slight advantage to ROLLBACK.
The general question is,
What do you recommend? Am I wrong in my understanding of some of this?
Specific questions:
Which is better, ROLLBACK or DELETE (on a temporary table)?
Are the roles so narrowly controlled/defined in Oracle that we need to restrict TRUNCATE on a temporary table?
BTW: This is a PeopleSoft system so everyone involved is hyper-sensitive to permissions and roles
Testing on 11.2.0.4:
create global temporary table gtt (id number) on commit preserve rows;
insert into gtt42 select level from dual connect by level <= 100000;
prompt rollback
set timing on
rollback;
set timing off;
insert into gtt select level from dual connect by level <= 100000;
prompt delete
set timing on
delete from gtt;
set timing off;
insert into gtt select level from dual connect by level <= 100000;
prompt truncate
set timing on
truncate table gtt;
set timing off;
rollback
Elapsed: 00:00:00.049
delete
Elapsed: 00:00:01.036
truncate
Elapsed: 00:00:00.052
Rollback is about the same as truncate, and both are much faster than delete. Logically truncate and rollback are doing pretty much the same thing.
For your scenario it seems to you just need to commit the insert into the first GTT, then repeatedly rollback and insert into the second:
create global temporary table gtt1 (id number) on commit preserve rows;
create global temporary table gtt2 (id number) on commit preserve rows;
insert into gtt1 (id) select level from dual connect by level <= 100;
commit;
insert into gtt2 (id) select id from gtt1 where mod(id, 2) = 0;
select count(*), min(id), max(id) from gtt2;
COUNT(*) MIN(ID) MAX(ID)
---------- ---------- ----------
50 2 100
rollback;
insert into gtt2 (id) select id from gtt1 where mod(id, 3) = 0;
select count(*), min(id), max(id) from gtt2;
COUNT(*) MIN(ID) MAX(ID)
---------- ---------- ----------
33 3 99
The commit preserves the rows in the first GTT; the rollback then only affects the data in the second GTT and doesn't touch the first.
As for the privileges, from the documentation for truncate:
To truncate a table, the table must be in your schema or you must have the DROP ANY TABLE system privilege.
As you can't truncate the tables presumably aren't in your schema; and you can hopefully understand why your DBA wouldn't want to give you the very powerful and dangerous drop any table privilege. You can't grant a privilege to allow only truncation of a specific table in another schema.