在全局临时表上创建索引

问题描述：

是否可以在全局临时表上创建索引?如果是，我们是否需要在单个会话中创建.

Is it possible to create index on Global temporary table? If Yes, Do we need to create in a single session.

我尝试了以下方法，最终遇到了以下问题.

I tried the following way and ended up with the below issue.

会议1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
   (    "C1" VARCHAR2(6 CHAR)
   ) ON COMMIT PRESERVE ROWS ;

CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

会议1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
       (    "C1" VARCHAR2(6 CHAR)
       ) ON COMMIT PRESERVE ROWS ;

会议2:

CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

会议1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
       (    "C1" VARCHAR2(6 CHAR)
       ) ON COMMIT PRESERVE ROWS ;

会议2:

TRUNCATE TABLE DUMMY_GLO
CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

对于上述所有尝试，我都收到一条错误消息，提示.

For all the above tries i got an error message saying.

getting an error while creating index for temporary table
Error starting at line : 1 in command -
CREATE INDEX DUMMY_GLO_IND ON DUMMY_GLO (C1) TABLESPACE SFE_I1
Error at Command Line : 1 Column : 42
Error report -
SQL Error: ORA-14451: unsupported feature with temporary table
14451. 00000 -  "unsupported feature with temporary table"
*Cause:    An attempt was made to create an IOT, specify physical attributes,
           specify partition or parallel clause.
*Action:   do not do that

答

是的，您可以在临时表上创建索引，但是不能为其指定表空间:

Yes you can create an index on a temporary table, but you cannot specify a tablespace for it:

SQL> CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1);

Index created.

我不确定您为什么尝试使用其他会话.与普通表一样，全局临时表及其索引只能创建一次.然后，许多会话可以一次使用该表，而不会看到彼此的数据.

I'm not sure why you are trying to use different sessions. A global temporary table and its index are only to be created once, just like a normal table. Then many sessions can use the table at once, without seeing each other's data.

在全局临时表上创建索引

相关推荐