Oracle 18c New feature Private Temporary Table

Hello,

18c introduced a new object called Private Temporary Table (I will call it PTT in this post). PTT is just another version of Global Temporary Table(GTT). The data is temporary and will be stored for a time. Data in temporary tables (both PTT and GTT) can be queried by the session which inserts the data. I will assume that you have experience with GTTs.

As you know GTTs are created once and can be used many times. GTT is an object and it is listed in Dba_Objects view as an object but PTTs are not! they are session objects (actually PTT is just an memory array that stores your data in your session). GTTs store data until you commit your transactions or sessions and same thing applies to PTTs but after the data erased PTTs are gone! They drop themselves.

So PTTs are specific to your session and only your session, create the table use it and then it will drop itself. Also it has a special naming convention. A new parameter “private_temp_table_prefix” is the mandatory prefix for PTTs. Default value is ORA$PTT.

create private Temporary table ora$ptt_x_trn (d number, c varchar2(80)) on commit drop definition;

create private Temporary table ora$ptt_x_sess (d number, c varchar2(80)) on commit preserve definition;

insert into ora$ptt_x_trn (d,c) values (1, 'X');

insert into ora$ptt_x_sess (d,c) values (1, 'X');

select * from ora$ptt_x_trn;
--         D C
------------ -------
--         1 X

select * from ora$ptt_x_sess;
--         D C
------------ -------
--         1 X

Commit;

select * from ora$ptt_x_trn;
--ERROR at line 1:
--ORA-00942: table or view does not exist

select * from ora$ptt_x_sess;
--         D C
------------ -------
--         1 X

create private Temporary table ora$ptt_x_trn (d number, c varchar2(80)) on commit drop definition;

create private Temporary table ora$ptt_x_sess (d number, c varchar2(80)) on commit preserve definition;

insert into ora$ptt_x_trn (d,c) values (1, 'X');

insert into ora$ptt_x_sess (d,c) values (1, 'X');

select * from ora$ptt_x_trn;

-- D C

------------ -------

-- 1 X

select * from ora$ptt_x_sess;

-- D C

------------ -------

-- 1 X

Commit;

select * from ora$ptt_x_trn;

--ERROR at line 1:

--ORA-00942: table or view does not exist

select * from ora$ptt_x_sess;

-- D C

------------ -------

-- 1 X

I created 2 tables ORA$PTT_X_TRN (End of life is transaction) and ORA$PTT_X_SESS (end of life is session). as you can see I inserted 1 row each table and after commit, my query to ORA$PTT_X_TRN has got an error “table or view does not exists”. so these are just session/transaction objects.

Personally I didn’t like this much. They are not much different from GTTs and have some cons compared to GTTs. Documentation says, PTTs are stored in Memory and GTTs are on disk but this is not entirely true. Oracle always stores data in memory as long as it can so GTT’s data are also in memory. Let’s do some performance check between GTTs and PTTs:

First insert performance one by one:

SQL> conn mustafa/mustafa
Connected.
SQL>
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL>
SQL> create private Temporary table ora$ptt_test (d number, c varchar2(80)) on commit preserve definition;

Table created.

Elapsed: 00:00:00.00
SQL> create global Temporary table gtt_test (d number, c varchar2(80)) on commit preserve rows;

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2    t varchar2(80);
  3  begin
  4    for i in 1..9999 loop
  5      insert into ora$ptt_test (d, c) values (i, 'CONST');
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60
SQL>
SQL> declare
  2    t varchar2(80);
  3  begin
  4    for i in 1..9999 loop
  5      insert into gtt_test (d, c) values (i, 'CONST');
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60

SQL> conn mustafa/mustafa

Connected.

SQL>

SQL> set autotrace traceonly statistics

SQL> set timing on

SQL>

SQL> create private Temporary table ora$ptt_test (d number, c varchar2(80)) on commit preserve definition;

Table created.

Elapsed: 00:00:00.00

SQL> create global Temporary table gtt_test (d number, c varchar2(80)) on commit preserve rows;

Table created.

Elapsed: 00:00:00.01

SQL>

SQL> declare

2 t varchar2(80);

3 begin

4 for i in 1..9999 loop

5 insert into ora$ptt_test (d, c) values (i, 'CONST');

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60

SQL>

SQL> declare

2 t varchar2(80);

3 begin

4 for i in 1..9999 loop

5 insert into gtt_test (d, c) values (i, 'CONST');

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60

so timing is almost same. I run this for a few times so all results are similar.

How about bulk insert:

SQL> conn mustafa/mustafa
Connected.
SQL>
SQL> set autotrace traceonly statistics
SQL> set timing on
SQL>
SQL> create private Temporary table ora$ptt_test (d number, c varchar2(80)) on commit preserve definition;

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> drop table gtt_test;

Table dropped.

Elapsed: 00:00:00.02
SQL> create global Temporary table gtt_test (d number, c varchar2(80)) on commit preserve rows;

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> drop table test_Data;

Table dropped.

Elapsed: 00:00:00.02
SQL> create table test_Data as select level lvl, 'CONS' data from dual connect by level < 1000000;

Table created.

Elapsed: 00:00:01.31
SQL>
SQL> insert into ora$ptt_test select * from test_data;

999999 rows created.

Elapsed: 00:00:00.44

Statistics
----------------------------------------------------------
         37  recursive calls
      14090  db block gets
       4152  consistent gets
       2074  physical reads
    2883024  redo size
        195  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL> insert into gtt_test select * from test_data;

999999 rows created.

Elapsed: 00:00:00.38

Statistics
----------------------------------------------------------
         29  recursive calls
      14099  db block gets
       4161  consistent gets
          0  physical reads
    2881624  redo size
        195  bytes sent via SQL*Net to client
        395  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     999999  rows processed

SQL>

SQL> conn mustafa/mustafa

Connected.

SQL>

SQL> set autotrace traceonly statistics

SQL> set timing on

SQL>

SQL> create private Temporary table ora$ptt_test (d number, c varchar2(80)) on commit preserve definition;

Table created.

Elapsed: 00:00:00.00

SQL>

SQL> drop table gtt_test;

Table dropped.

Elapsed: 00:00:00.02

SQL> create global Temporary table gtt_test (d number, c varchar2(80)) on commit preserve rows;

Table created.

Elapsed: 00:00:00.00

SQL>

SQL> drop table test_Data;

Table dropped.

Elapsed: 00:00:00.02

SQL> create table test_Data as select level lvl, 'CONS' data from dual connect by level < 1000000;

Table created.

Elapsed: 00:00:01.31

SQL>

SQL> insert into ora$ptt_test select * from test_data;

999999 rows created.

Elapsed: 00:00:00.44

Statistics

----------------------------------------------------------

37 recursive calls

14090 db block gets

4152 consistent gets

2074 physical reads

2883024 redo size

195 bytes sent via SQL*Net to client

399 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

999999 rows processed

SQL> insert into gtt_test select * from test_data;

999999 rows created.

Elapsed: 00:00:00.38

Statistics

----------------------------------------------------------

29 recursive calls

14099 db block gets

4161 consistent gets

0 physical reads

2881624 redo size

195 bytes sent via SQL*Net to client

395 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

999999 rows processed

SQL>

on my example, GTT is faster on bulk insert but after many times testing, on some of them PTT were faster so timing not much difference at all. Also you can see that redo size, db block gets etc are very similar.

How about querying?

SQL> declare
  2    t varchar2(80);
  3  begin
  4    for i in 1..99999 loop
  5      select c into t from ora$ptt_test where d =i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:42.64
SQL>
SQL> declare
  2    t varchar2(80);
  3  begin
  4    for i in 1..99999 loop
  5      select c into t from gtt_test where d =i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:03:42.55
SQL>

SQL> declare

2 t varchar2(80);

3 begin

4 for i in 1..99999 loop

5 select c into t from ora$ptt_test where d =i;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:42.64

SQL>

SQL> declare

2 t varchar2(80);

3 begin

4 for i in 1..99999 loop

5 select c into t from gtt_test where d =i;

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed.

Elapsed: 00:03:42.55

SQL>

again not much difference and update:

SQL> update ora$ptt_test set c = c || 'a' where rownum < 100000;

99999 rows updated.

Elapsed: 00:00:02.13

Statistics
----------------------------------------------------------
         14  recursive calls
     241460  db block gets
        512  consistent gets
          0  physical reads
   20347688  redo size
        195  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> update gtt_test set c = c || 'a' where rownum < 100000;

99999 rows updated.

Elapsed: 00:00:01.89

Statistics
----------------------------------------------------------
         68  recursive calls
     213479  db block gets
        502  consistent gets
          0  physical reads
   17868444  redo size
        195  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99999  rows processed

SQL> update ora$ptt_test set c = c || 'a' where rownum < 100000;

99999 rows updated.

Elapsed: 00:00:02.13

Statistics

----------------------------------------------------------

14 recursive calls

241460 db block gets

512 consistent gets

0 physical reads

20347688 redo size

195 bytes sent via SQL*Net to client

409 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

99999 rows processed

SQL> update gtt_test set c = c || 'a' where rownum < 100000;

99999 rows updated.

Elapsed: 00:00:01.89

Statistics

----------------------------------------------------------

68 recursive calls

213479 db block gets

502 consistent gets

0 physical reads

17868444 redo size

195 bytes sent via SQL*Net to client

405 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

99999 rows processed

no difference. Also there is a very huge con for PTTs. You can not create index on a PTT! This makes a huge difference because many of my simple tests will be changed and GTTs become the leader.

So I will continue to use GTTs instead of PTTs. This PTT is look like table variable of Microsoft sql server db but not same. we still have to use Execute immediate to create a PTT and create table script will be a string so to check the syntax we have to copy the code and test it first etc etc. so not much usable to me. By the way I’am already against to create temp table just for a small code because whenever I convert a mssql db procedure to pslql procedure, I saw huge performance gaps because of those temp tables.

thanks for reading.

Leave a Reply Cancel reply