Oracle 18c New feature Private Temporary Table


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.

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:

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

How about bulk insert:

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?

again not much difference and update:

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

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