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.
|
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:
|
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:
|
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?
|
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:
|
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.