18c private temporary tables

View All Scripts Login to Run Script

Script Name 18c private temporary tables
Description 18c private temporary tables
Area SQL General
Contributor Connor Mcdonald (Oracle)
Created Saturday February 17, 2018

Statement 1

begin  
  for i in ( select table_name   
             from   user_private_temp_tables  
             where  owner = sys_context('USERENV','CURRENT_USER')  
           )   
  loop  
    begin  
      execute immediate 'drop table '||i.table_name;  
    exception  
      when others then null;  
    end;  
  end loop;  
end;

Statement processed.

Statement 2
Private temporary tables must be prefixed as per the database parameter 'private_temp_table_prefix'
```
create private temporary table MY_TT ( x int ) 
```
```
ORA-00903: invalid table name 
```
More Details: https://docs.oracle.com/error-help/db/ora-00903

Statement 3

create private temporary table ORA$PTT_MY_TT ( x int )

Table created.

Statement 4

create table ORA$PTT_MY_NORMAL_TABLE ( y int )

ORA-32463: cannot create an object with a name matching private temporary table prefix

More Details: https://docs.oracle.com/error-help/db/ora-32463

Statement 5
The table is truly private. It does not even exist in the the data dictionary, only your session
```
select count(*)  
from   all_objects  
where  object_name = 'ORA$PTT_MY_TT' 
```
```
COUNT(*)
0
```
Statement 6
By default, the moment you commit, the table ceases to exist
```
commit
```
```
Statement processed.
```
Statement 7
```
select * from ORA$PTT_MY_TT 
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

COUNT(*)
0

Statement 8

This can be changed by specifying that the definition should be preserved

create private temporary table ORA$PTT_MY_TT ( x int )  
on commit preserve definition

Table created.

Statement 9

insert into ORA$PTT_MY_TT  
select rownum from dual  
connect by level <= 30

30 row(s) inserted.

Statement 10
```
commit
```
```
Statement processed.
```

Statement 11

select count(*) from ORA$PTT_MY_TT


COUNT(*)
30

COUNT(*)
30

Statement 12

Because the table does not exist in the dictionary, you do not gather statistics on them

exec dbms_stats.gather_table_stats('','ORA$PTT_MY_TT')

ORA-20000: Unable to analyze TABLE "SQL_PSUOAPSCOLIVJEYXWUCPTXUWU"."ORA$PTT_MY_TT", insufficient privileges or does not exist ORA-06512: at "SYS.DBMS_STATS", line 39094
ORA-06512: at "SYS.DBMS_STATS", line 38371
ORA-06512: at "SYS.DBMS_STATS", line 38530
ORA-06512: at "SYS.DBMS_STATS", line 39076
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-20000

Statement 13
```
delete plan_table 
```
```
5 row(s) deleted.
```
Statement 14
Hence, if you do a conventional load of the table, the dynamic sampling will be used for queries
```
explain plan for select count(*) from ORA$PTT_MY_TT
```
```
Statement processed.
```

Statement 15

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 782278473
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |               |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| ORA$PTT_MY_TT |    30 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

13 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 782278473

------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 2 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| \| \|
\| 2 \| TABLE ACCESS STORAGE FULL\| ORA$PTT_MY_TT \| 30 \| 2 (0)\| 00:00:01 \|
------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 16

create private temporary table  ORA$PTT_MY_TT2  
on commit preserve definition as  
select * from all_objects

Table created.

Statement 17
```
commit
```
```
Statement processed.
```

Statement 18

select count(*) from ORA$PTT_MY_TT2


COUNT(*)
56830

Statement 19
```
delete plan_table 
```
```
3 row(s) deleted.
```

COUNT(*)
56830

Statement 20

explain plan for select count(*) from ORA$PTT_MY_TT2

Statement processed.

Statement 21

If the table is populated via CREATE TABLE AS SELECT the statistics on load will be gathered

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3727903318
 
-------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |   310   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| ORA$PTT_MY_TT2 | 56830 |   310   (1)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Note
-----
   - Global temporary table session private statistics used

13 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3727903318

-------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Cost (%CPU)\| Time \|
-------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 310 (1)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| \| \|
\| 2 \| TABLE ACCESS STORAGE FULL\| ORA$PTT_MY_TT2 \| 56830 \| 310 (1)\| 00:00:01 \|
-------------------------------------------------------------------------------------

Note
-----
- Global temporary table session private statistics used

Statement 22

truncate table ORA$PTT_MY_TT2 drop storage

Table dropped.

Statement 23

insert /*+ APPEND */ into ORA$PTT_MY_TT2  
select * from all_objects  
where rownum <= 1000

1000 row(s) inserted.

Statement 24
```
commit
```
```
Statement processed.
```

Statement 25

select count(owner) from ORA$PTT_MY_TT2


COUNT(OWNER)
1000

Statement 26
```
delete plan_table 
```
```
3 row(s) deleted.
```

COUNT(OWNER)
1000

Statement 27

explain plan for select count(owner) from ORA$PTT_MY_TT2

Statement processed.

Statement 28

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3727903318
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |    66 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                |     1 |    66 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| ORA$PTT_MY_TT2 |  1000 | 66000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

13 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3727903318

---------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 66 \| 6 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 66 \| \| \|
\| 2 \| TABLE ACCESS STORAGE FULL\| ORA$PTT_MY_TT2 \| 1000 \| 66000 \| 6 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 29

create private temporary table  ORA$PTT_MY_TT3 ( x int, y int, z int )  
on commit preserve definition

Table created.

Statement 30
However, population of an existing or new empty private temporary table will not perform statistics on load, and will use dynamic sampling for subsequent queries
```
insert /*+ APPEND */ into ORA$PTT_MY_TT3  
select rownum, rownum, rownum from dual  
connect by level <= 50000 
```
```
50000 row(s) inserted.
```
Statement 31
```
commit
```
```
Statement processed.
```

Statement 32

select count(z) from ORA$PTT_MY_TT3


COUNT(Z)
50000

Statement 33
```
delete plan_table 
```
```
3 row(s) deleted.
```

COUNT(Z)
50000

Statement 34

explain plan for select count(z) from ORA$PTT_MY_TT3

Statement processed.

Statement 35

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 253260165
 
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |     1 |    13 |    38   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |                |     1 |    13 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| ORA$PTT_MY_TT3 | 56208 |   713K|    38   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

13 rows selected.

Statement 36
```
delete plan_table 
```
```
3 row(s) deleted.
```

PLAN_TABLE_OUTPUT
Plan hash value: 253260165

---------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 13 \| 38 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 13 \| \| \|
\| 2 \| TABLE ACCESS STORAGE FULL\| ORA$PTT_MY_TT3 \| 56208 \| 713K\| 38 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 37

explain plan for insert /*+ APPEND */ into ORA$PTT_MY_TT3  
select rownum, rownum, rownum from dual  
connect by level <= 50000

0 row(s) inserted.

Statement 38

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 1600317434
 
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |                |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT                | ORA$PTT_MY_TT3 |       |            |          |
|   2 |   COUNT                        |                |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|                |       |            |          |
|   4 |     FAST DUAL                  |                |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(LEVEL<=50000)

16 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 1600317434

-----------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Cost (%CPU)\| Time \|
-----------------------------------------------------------------------------------------
\| 0 \| INSERT STATEMENT \| \| 1 \| 2 (0)\| 00:00:01 \|
\| 1 \| LOAD AS SELECT \| ORA$PTT_MY_TT3 \| \| \| \|
\| 2 \| COUNT \| \| \| \| \|
\|* 3 \| CONNECT BY WITHOUT FILTERING\| \| \| \| \|
\| 4 \| FAST DUAL \| \| 1 \| 2 (0)\| 00:00:01 \|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(LEVEL<=50000)

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database