I was very excited when I learned about the new feature in Oracle 18c, namely the private temporary tables. The idea was to do the same thing as with SQL Server, which is able to index those tables and use them for reports. This works in SQL Server:
select * into #test1 from emp;
create index jobs_t on #test1(job);
The “#” prefix means that I am creating a temporary table. I am able to create indexes on the SQL Server temporary tables, for reporting purposes. Let’s show it:
mgogala@mssql:~$ sqlcmd -U scott -P tiger -d scott
1> select * into #test1 from emp;
2> go
(14 rows affected)
1> create index jobs_t on #test1(job);
2> go
1> select * from #test1 where job=’CLERK’;
2> go
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———– ————————————————– ————————————————– ———– —————- ———– ———– ——
7369 SMITH CLERK 7902 1980-12-17 800 NULL 20
7876 ADAMS CLERK 7788 1987-05-23 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950 NULL 30
7934 MILLER CLERK 7782 1982-01-23 1300 NULL 10
(4 rows affected)
The table #test1 vanishes automatically after disconnecting from SQL Server:
mgogala@mssql:~$ sqlcmd -U scott -P tiger -d scott
1> select * from #test1;
2> go
Msg 208, Level 16, State 1, Server mssql, Line 1
Invalid object name ‘#test1’.
1>
However, the same thing doesn’t work with Oracle 18c:
1 CREATE PRIVATE TEMPORARY TABLE ORA$PTT_EMP(
2 “EMPNO” NUMBER(4,0),
3 “ENAME” VARCHAR2(10),
4 “JOB” VARCHAR2(9),
5 “MGR” NUMBER(4,0),
6 “HIREDATE” DATE,
7 “SAL” NUMBER(7,2),
8 “COMM” NUMBER(7,2),
9 “DEPTNO” NUMBER(2,0))
10* ON COMMIT PRESERVE DEFINITION
SQL> /
Table created.
Elapsed: 00:00:00.00
SQL> create index ora$ptt_jobs on ora$ptt_emp(job);
create index ora$ptt_jobs on ora$ptt_emp(job)
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
What does that mean? It is possible to create private temporary table but restrictions on them are such that they are almost useless. It is not possible to index or compress private temporary table which means that it is not possible to use Oracle private temporary tables the same way we use SQL Server temporary tables, for reporting. There is also a funny thing with the “ON COMMIT DROP DEFINITION” clause. Remember, the first phase of every DDL is execute a “COMMIT” statement. An attempt to index private temporary table defined with “ON COMMIT DROP DEFINITION” will actually drop the private temporary table because the first thing that “CREATE INDEX” statement actually does is – COMMIT.
So, while it is encouraging to see Oracle trying to catch up with some other competing database systems, the private temporary tables are very far away from being ready for the prime time. For now, I don’t see much use for the Oracle private temporary tables. In order for private temporary tables to be useful, it must be possible to index them. Unfortunately, that is not possible now. It would also help to be able to compress them, which also doesn’t work.
