Private temporary tables

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sun, 9 Jun 2019 18:55:36 -0400
Message-ID: <83441468-6582-2fdf-9bc4-b2a4e7dd51b7_at_gmail.com>



Private temporary tables are exceptionally useful in some other databases, like SQL Server. One of the very useful features on SQL Server temporary tables is the possibility to create indexes on them. That doesn't work with Oracle:

SQL> create private temporary table ORA$PTT_TEST(col1 number,col2 varchar2(10))
   2* on commit preserve definition;
Private created.

Elapsed: 00:00:00.004
SQL> create index i1 on ORA$PTT_TEST(col1);

create index i1 on ORA$PTT_TEST(col1)

_*ERROR at line 1:
ORA-14451: unsupported feature with temporary table*_

Elapsed: 00:00:00.006
SQL> select * from v$version;

BANNER



BANNER_FULL

BANNER_LEGACY

     CON_ID

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production           0

Elapsed: 00:00:00.008
SQL> Is this planned? If so, can we expect this in a future patch set or a future version? The "ON COMMIT PRESERVE DEFINITION" clause is necessary here because DDL statements like "CREATE INDEX" begin by issuing a commit, which would wipe out the temporary table without this clause.

Regards

--

Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 10 2019 - 00:55:36 CEST

Original text of this message