When performing direct path operations on a temporary table (where ON COMMIT PRESERVE ROWS is enabled), session-level statistics are gathered by default for the temporary table being loaded. Two typical direct path load operations are CREATE TABLE AS SELECT (CTAS) and direct path INSERTs (INSERTs with the /*+ append */ hint).
A simple example will demonstrate this. Here, we create a CTAS table:
SQL> create global temporary table gt on commit preserve rows as select * from all_users;
Table created.
We can verify that session-level statistics have been generated via the following query:
SQL> select table_name, num_rows, last_analyzed, scope from user_tab_statistics
where table_name like ‘GT’;
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
GT SHARED
GT 51 27-JUN-21 SESSION
This eliminates the need to call DBMS_STATS to generate statistics when direct path loading a temporary table that is defined as ON COMMIT PRESERVE ROWS.
Private Temporary Tables
Oracle 18c introduced private temporary tables. With private temporary tables, both the object and the data are dropped at the end of a session or transaction. Private temporary tables persist only in memory and are therefore only visible to the session which created it.
Private temporary tables must be created with the prefix defined by the PRIVATE_TEMP_TABLE_PREFIX initialization parameter. The default for this parameter is ORA$PTT. You can view the definition of this parameter as follows:
SQL> show parameter PRIVATE_TEMP_TABLE_PREFIX
NAME TYPE VALUE
private_temp_table_prefix string ORA$PTT_
The basic syntax for creating a private temporary table is as follows:
CREATE PRIVATE TEMPORARY TABLE (column definition,column definition,…) ON COMMIT [DROP DEFINITION | PRESERVE DEFINITION];
The ON COMMIT clause is used to define whether the private temporary table persists just for a transaction or persists throughout the session connection. In other words, use ON COMMIT DROP DEFINITION if your requirement is to have the table dropped after a COMMIT or ROLLBACK statement is issued. Use ON COMMIT PRESERVE DEFINITION to have the table persist for the duration of the session connection.
This first example demonstrates creating a private temporary table that persists only for the current transaction:
$ sqlplus eoda/foo@PDB1
SQL> create private temporary table ora$ptt_temp1(a int) on commit drop definition;Table created.
Now I’ll insert some data and select from the table:
SQL> insert into ora$ptt_temp1 values(1); SQL> select * from ora$ptt_temp1;
Now if I issue either a COMMIT or a ROLLBACK (thus ending the transaction), the table will be automatically dropped:
SQL> commit;
SQL> desc ora$ptt_temp1;
ERROR:
ORA-04043: object ora$ptt_temp1 does not exist
This next example creates a private temporary table that persists for the duration of the session:
SQL> create private temporary table ora$ptt_temp2(a int) on commit preserve definition;
SQL> insert into ora$ptt_temp2 values(1); SQL> commit;
SQL> select * from ora$ptt_temp2;
Now if I disconnect and reconnect, the table will be dropped automatically:
SQL> disconnect;
SQL> connect eoda/foo@PDB1
SQL> select * from ora$ptt_temp2;select * from ora$ptt_temp2*
ERROR at line 1:
ORA-00942: table or view does not exist
When should you use a global temporary table vs. a private temporary table? Table 10-3 compares the features of these temporary tables.
Table 10-3. Features of Global and Private Temporary Tables
If you need a temporary table that persists across connections, or need it to be visible by multiple sessions, then use a global temporary table. Also, the performance of querying global temporary tables can be improved via indexes and optimizer statistics, whereas you cannot create indexes or generate statistics for private temporary tables.