Private temporary tables
Date: Tue, 29 Dec 2020 14:18:43 -0500
Message-ID: <71ffbded-5ce2-ef84-7a91-57cdcdcae3a1_at_gmail.com>
Having replied to Mark earlier, I went to check whether indexes on PTT's have quietly been supported in Oracle 19.9. They haven't:
SQL> create private temporary table ora$ptt_test1 (rec_id integer
primary key,rec_val varchar2(20)) on commit preserve definition;
create private temporary table ora$ptt_test1 (rec_id integer primary
key,rec_val varchar2(20)) on commit preserve definition
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
The problem is in the "PRIMARY KEY" constraint. Without it, the creation works as expected:
SQL> create private temporary table ora$ptt_test1 (rec_id integer,rec_val varchar2(20)) on commit preserve definition;
Table created.
Elapsed: 00:00:00.00
The technique of slicing and dicing queries with local temporary tables comes from SQL Server where it is possible to have a primary key on the temporary table:
SQL> [mgogala_at_umajor ~]$ isql MS-scott scott tiger
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+SQL> create table ##test1 (rec_id integer primary key,rec_val varchar(20)) SQLRowCount returns 0
SQL> Tables with the names beginning with "##" are local temp tables in SQL Server. This is SQL Server 2017:
SQL> select _at__at_version
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64)
Aug 20 2020 22:33:27 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Oracle Linux Server 7.9) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+SQLRowCount returns 0
1 rows fetched
It is possible to create index on global temporary tables in Oracle. My feeling is that PTT feature in Oracle is only at its beginning. I don't have a cloud account but from the SQL reference for Oracle 21c, it looks like indexing PTT's will not be supported with the initial version of Oracle 21c either. Can someone tell me if I'm right?
Regards
-- Mladen Gogala Database Consultant http://mgogala.byethost5.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Dec 29 2020 - 20:18:43 CET