Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Oracle 18c New feature Private Temporary Table

Mustafa, 2020-03-22

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.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
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?

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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.

18c 19c Development Performance SQL / PLSQL 18c19cglobal temporary tableglobal vs private temporary tablegttprivate temporary tableptt

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes