Skip to Main Content
  • Questions
  • What is the fastest way to delete data from temporary table?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Alex.

Asked: March 13, 2016 - 12:19 pm UTC

Last updated: May 10, 2016 - 1:46 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hello,
Tom, i'm using gtt during daily data uploading procedures.
I have 10 procedures, that launches one by one.
Each of this procedures uses 1 universal gtt (lets call it universal_gtt. Table contains 3 fields: id numeric, date1 date, date2 date).
At the end of each procedure i'm using:
DELETE FROM UNIVERSAL_GTT;
COMMIT;

but it works too long (about 20% of all procedures working time)

That's why i have 2 questions:
1. How to do deletion from gtt faster?
2. Does is will be a good idea to drop and recreate table universal_gtt 10 times each day during launching procedures?

and Connor said...

"commit" if it is defined as 'on commit delete rows'

or

"truncate" if not.

(Obviously you cannot rollback a truncate)


Do *not* drop/recreate gtt's for this purpose. That's a bad idea.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 13, 2016 - 2:57 pm UTC


It causes an error 'no more data read from socket'

Alex Bud, March 13, 2016 - 4:47 pm UTC

'truncate table GTT drop storage' causes an error 'no more data read from socket'
Chris Saxon
March 14, 2016 - 1:52 am UTC

That's typically a client error. You'd need to give us some context.

SQL> create global temporary table gtt ( x int ) on commit preserve rows;

Table created.

SQL> insert into gtt values (1);

1 row created.

SQL> truncate table gtt drop storage;

Table truncated.

SQL> select * from gtt;

no rows selected


ORA-14462 : On GTT Truncate

Rajeshwaran, Jeyabal, March 14, 2016 - 11:27 am UTC

In case of Truncate on GTT while other session/transaction in use watch out for ORA-14462.

rajesh@PDB1> create global temporary table t(x int)
  2  on commit delete rows;

Table created.

rajesh@PDB1> insert into t(x) values(55);

1 row created.

rajesh@PDB1> declare
  2     pragma autonomous_transaction ;
  3  begin
  4     execute immediate 'truncate table t';
  5     commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-14462: cannot TRUNCATE temporary table in an autonomous transaction which is already in use by the parent transaction
ORA-06512: at line 4


rajesh@PDB1>

A reader, May 10, 2016 - 9:01 am UTC

Sorry for long "no comments period".

when I'm running something like that:
create or replace procedure ...
begin
execute immediate 'truncate table gtt drop storage';
commit;
end;

I'll take error ora-03113 at sqlplus (or no more data read from socket at SQL Developer)
How to avoid this error?
Chris Saxon
May 10, 2016 - 10:23 am UTC

Sorry, I'm not able to reproduce:

SQL> create global temporary table gtt ( x int ) on commit preserve rows;

Table created.

SQL> insert into gtt values (1);

1 row created.

SQL>
SQL> create or replace procedure p as
  2  begin
  3  execute immediate 'truncate table gtt drop storage';
  4  commit;
  5  end;
  6  /

Procedure created.

SQL>
SQL> exec p;

PL/SQL procedure successfully completed.

SQL>


Could you show a copy-paste of your complete test case, starting with "create table"?

Chris

A reader, May 10, 2016 - 11:18 am UTC

MY GTT:
CREATE GLOBAL TEMPORARY TABLE CL_LINKS_PREV_TEMP
(
LINK_ID NUMBER(9, 0) NOT NULL
, RDATE_FROM DATE NOT NULL
, RDATE_TO DATE NOT NULL
, PERIOD_ID NUMBER(1, 0) NOT NULL
, TYPE_ID VARCHAR2(6 BYTE) NOT NULL
, CONSTRAINT CL_LINKS_PREV_TEMP_PK PRIMARY KEY (LINK_ID) USING INDEX (CREATE UNIQUE INDEX CL_LINKS_PREV_TEMP_PK ON CL_LINKS_PREV_TEMP (LINK_ID ASC) NOPARALLEL) ENABLE)
ON COMMIT PRESERVE ROWS TABLESPACE MIS_ETL_TEMP_TBS
NOPARALLEL;

AND MY MAIN procedures:
create or replace procedure load_bs
as
begin
for i in 1..10 loop
load_daily_offbs(null);
end loop;
end;

create or replace procedure load_daily_offbs(datCurrent date)
authid current_user
IS
...
BEGIN
.../*many rows with loading data to different GTT tables and transformation of that data*/
load_used_contract_links(to_date('31.12.2015','dd.mm.yyyy'),'010000'); /*this procedure truncates table mis_etl.cl_links_prev_temp and after that loads data to this table*/
.../*few selects from table mis_etl.cl_links_prev_temp and merges into other tables*/
END;

create or replace procedure load_used_contract_links(datCurrent date, sCurrentTypeID varchar)
authid current_user
IS
...
type arrT is varray(4) of varchar2(80);
arrTables arrT:=arrT('mis_dm.daily_bs','mis_dm.monthly_bs','mis_dm.daily_offbs','mis_dm.daily_transfer');
...
BEGIN
.../*few selects into variables via DBLink*/
execute immediate 'truncate table mis_etl.cl_links_prev_temp drop storage';
commit;

for i in 1..4 loop
execute immediate 'insert into mis_etl.cl_links_prev_temp select ... from ' || arrTables(i) || ' where ...';
commit;
end loop;
EXCEPTION
...
END;

And on truncating table (but not always) i'm having next error (ora-03113).
Trace file contains next information:
...
ORA-07445: exception encountered: core dump [audbfr()+12] [SIGSEGV] [ADDR:0x101010121B018A0] [PC:0x104F93DCC] [Address not mapped to object] []

[TOC00001-END]
[TOC00002]
========= Dump for incident 17776 (ORA 7445 [audbfr]) ========
[TOC00003]
----- Beginning of Customized Incident Dump(s) -----
Dumping swap information
swap info: free_mem = 990.52M rsv = 64.00M
alloc = 48.96M avail = 16384.00M swap_free = 16335.04M
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x101010121B018A0] [PC:0x104F93DCC, audbfr()+12] [flags: 0x0, count: 1]
Registers:
iar: 0x0000000104f93dcc lr: 0x0000000104f892b0
msr: 0xa00000000200d032 cr: 0x0000000042220042
r00: 0x0000000000000000 r01: 0x0ffffffffffebbf0 r02: 0x00000001216c68f0
r03: 0x0ffffffffffec630 r04: 0x0101010121b01878 r05: 0x0ffffffffffebc64
r06: 0x0ffffffffffebc62 r07: 0x0ffffffffffebc60 r08: 0x0000000000000000
r09: 0x0000000000000000 r10: 0x0000000000000000 r11: 0x07000101b9b1a418
r12: 0x0000000104f890a8 r13: 0x000000012173aae0 r14: 0x0000000000000000
r15: 0x0000000000000001 r16: 0x0000000000000000 r17: 0x0000000000000001
r18: 0x0000000000000055 r19: 0x0000000000000000 r20: 0x0700000000034d00
r21: 0x0000000000000000 r22: 0x0000000000000000 r23: 0x07000101b63a4480
r24: 0x0ffffffffffec358 r25: 0x00002ef2b569608f r26: 0x0000000000000055
r27: 0x0000000000000000 r28: 0x0000000000000055 r29: 0x0000000000000000
r30: 0x0000000121b01878 r31: 0x0000000000000055
audbfr()+0 ori r0, r0, 0
audbfr()+4 11 0
audbfr()+8 addi r0, r0, 0
> audbfr()+12 ld r8, 40(r4)
audbfr()+16 bc 0x30 <0x4f93e00>, 0x2, 0x4
audbfr()+20 addi r4, r0, 0
audbfr()+24 std r4, 200(r3)
audbfr()+28 sth r4, 0(r5)
audbfr()+32 std r5, 208(r3)

*** 2016-05-10 11:40:47.840
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
[TOC00004]
----- Current SQL Statement for this session (sql_id=83zq2pkdhjwvc) -----
truncate table mis_etl.cl_links_prev_temp drop storage
[TOC00005]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
7000101b03f2308 69 procedure MIS_DM.LOAD_USED_CONTRACT_LINKS
7000101b78e4f90 313 procedure MIS_DM.LOAD_DAILY_OFFBS
70001019d35b588 5 procedure MIS_DM.LOAD_BS
7000101b78e8f78 1 anonymous block
[TOC00005-END]
[TOC00004-END]
...
Connor McDonald
May 10, 2016 - 1:46 pm UTC

Ah, ORA-07445. Looks like you've hit a bug.

You'll need to take this up with support.

FWIW you example works for me - 12.1.0.2 on Linux

A reader, May 10, 2016 - 11:36 am UTC

I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production on AIX

Alex Bud, May 11, 2016 - 5:58 pm UTC

Thanks a lot!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library