Re: Index on Global Temp Tables Not Supported?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 08 Apr 2006 19:19:56 GMT
Message-Id: <pan.2006.04.08.19.19.56.122948@sbcglobal.net>

On Fri, 07 Apr 2006 15:04:00 -0700, cdavis10717 wrote:

> Hi, all,
>
> I'm using Oracle 9.2.0.5 and I can not create an index on a global
> temporary table.

You can create an index but you cannot specify tablespace. Also, you cannot create index if the table already contains data:

SQL> create global temporary table t_emp   2 on commit preserve rows
  3 as select * from emp;

Table created.

SQL> create index t_emp_ename_i on t_emp(ename);  create index t_emp_ename_i on t_emp(ename)

                               *

ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL> connect scott/tiger
Connected.
SQL> create index t_emp_ename_i on t_emp(ename);

Index created.

SQL> insert /*+ append */ into t_emp
  2 select * from emp;

14 rows created.

SQL>
SQL> connect scott/tiger
Connected.
SQL> drop table t_emp;

Table dropped.

SQL> purge recyclebin; /* I don't actually have 9i any longer and I don't

                          want to go to my garage and fetch the CDs. */

Recyclebin purged.

SQL>

-- 
http://www.mgogala.com

Received on Sat Apr 08 2006 - 14:19:56 CDT