OraFAQ Forum: Performance Tuning » Cannot index temp table after inserting rows

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cannot index temp table after inserting rows [message #683896]

Wed, 03 March 2021 03:00

Andrey_R
Messages: 441
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.

Report message to a moderator

Re: Cannot index temp table after inserting rows [message #683897 is a reply to message #683896]

Wed, 03 March 2021 03:31

John Watson
Messages: 8964
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

Andrey_R
Messages: 441
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 )

Report message to a moderator

Previous Topic:	SQL ID changes Plan hash value
Next Topic:	Query running slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 02:48:12 CST 2025