Home » RDBMS Server » Performance Tuning » Cannot index temp table after inserting rows (Linux\Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Cannot index temp table after inserting rows [message #683896] Wed, 03 March 2021 03:00 Go to next message
Andrey_R
Messages: 363
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I am trying to create some rows in a temporary table, then index them.

I know I can first create it empty, then index:

[oracle@mylinux ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 3 10:55:54 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user tuser identified by tuser;

User created.

SQL> grant dba to tuser;

Grant succeeded.

SQL>
SQL>
SQL> conn tuser/tuser
Connected.
SQL>
create global temporary table tuser.ttab ( col1 number ) on commit preserve rows;
SQL>
Table created.

SQL>
create index tuser.tind on tuser.ttab(col1);SQL>

Index created.

SQL>

insert into tuser.ttab values ( 1);
insert into tuser.ttab values ( 2);

SQL> SQL>
1 row created.

SQL>
1 row created.

SQL> SQL>
SQL>
commit;SQL>

Commit complete.
But when I try to first insert the rows, then create indexes, it gives me the error, even after I committed:

[oracle@mylinux ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 3 10:46:13 2021

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL>
alter session set "_ORACLE_SCRIPT"=true;  SQL>

Session altered.

SQL> create user tuser identified by tuser;

User created.

SQL> grant dba to tuser;

Grant succeeded.

SQL> conn tuser/tuser
Connected.
SQL>
create global temporary table tuser.ttab ( col1 number ) on commit preserve rows;

SQL>
Table created.

SQL> SQL>
SQL>
SQL>
insert into tuser.ttab values ( 1);
insert into tuser.ttab values ( 2);
SQL>
1 row created.

SQL>
1 row created.

SQL>
SQL>
SQL>
commit;

create index tuser.tind on tuser.ttab(col1);SQL>
Commit complete.

SQL> SQL>
create index tuser.tind on tuser.ttab(col1)
                                 *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use



Can anyone explain what is the logic behind it ( I can reach a state when I have the data + indexes if I create indexes empty, if I index existing data I get error ) ?
Is there a workaround except for creating indexes only when the table is empty ?

Thanks in advance,
Andrey R.
Re: Cannot index temp table after inserting rows [message #683897 is a reply to message #683896] Wed, 03 March 2021 03:31 Go to previous messageGo to next message
John Watson
Messages: 8541
Registered: January 2010
Location: Global Village
Senior Member
If you look at v$lock, you will see that any session with rows in a GTT takes a mode 3 type TO lock on the object. I had thought that this would be blocking the exclusive lock needed to create an index. But! I did a couple of experiments, and from another session I can create an index while the table is in use. But having created it, I cannot drop it: ora-14452.

If one were to investigate the lock types and modes and do a few more tests, perhaps one could reverse engineer it and work out what is happening. Enjoy....


--update: my test was in 19.3

[Updated on: Wed, 03 March 2021 03:32]

Report message to a moderator

Re: Cannot index temp table after inserting rows [message #683898 is a reply to message #683897] Wed, 03 March 2021 03:42 Go to previous message
Andrey_R
Messages: 363
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Wed, 03 March 2021 11:31
If you look at v$lock, you will see that any session with rows in a GTT takes a mode 3 type TO lock on the object. I had thought that this would be blocking the exclusive lock needed to create an index. But! I did a couple of experiments, and from another session I can create an index while the table is in use. But having created it, I cannot drop it: ora-14452.

If one were to investigate the lock types and modes and do a few more tests, perhaps one could reverse engineer it and work out what is happening. Enjoy....


--update: my test was in 19.3
Thank you John.

I do realize why it is happening.
And I realize how to work around it from another session, but this is exactly why it contradicts what I am trying to do:

When a different session is accessing the table, it is not processing any of the data that exists in other sessions for this temp table.

I want to have the data in my session indexed, in the same session.

If I can pre-create the index, I should be able to "post"-create it, IMO..
At the same time, I understand that it would be a DDL that doesn't allow for the table to be in use since the DDL affects all sessions...

I just wanted to ask if there is some kind of work around for it, so that I can achieve

  • Work in memory ( Temp table )
  • Index data that exists in my session only
  • Create indexes after inserting the data, not before ( since it should process faster that way )
Previous Topic: SQL ID changes Plan hash value
Next Topic: Query running slow
Goto Forum:
  


Current Time: Thu May 06 16:45:18 CDT 2021