Oracle 临时表上创建索引

Oracle 11.2官方文档中关于临时表的介绍（https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633）

Creating a Temporary Table

Temporary tables are useful in applications where a result set is to be buffered (temporarily persisted), perhaps because it is constructed by running multiple DML operations. For example, consider the following:

A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.

During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.

The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific, the implications of which are as follows:

ON COMMIT Setting
Implications

DELETE ROWS
This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.

PRESERVE ROWS
This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.

This statement creates a temporary table that is transaction specific:

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

（可以在临时表上创建索引。它们也是临时的，索引中的数据与基础表中的数据具有相同的会话或事务范围。）

SQL> conn test/test
Connected.

SQL> create global temporary table temp_objects on commit delete rows as select * from dba_objects;

Table created.

SQL> select count(*) from temp_objects;

  COUNT(*)
----------
      0

SQL> create global temporary table temp_objects2 on commit preserve rows as select * from dba_objects;

Table created.

SQL> select count(*) from temp_objects2;

  COUNT(*)
----------
      87115

SQL> create index idx_tmp_obj on temp_objects(object_id);

Index created.

SQL> create index idx_temp_obj2 on temp_objects2(object_id);
create index idx_temp_obj2 on temp_objects2(object_id)
                               *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> !oerr ora 14452
14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
// *Cause:  An attempt was made to create, alter or drop an index on temporary
//          table which is already in use.
// *Action: All the sessions using the session-specific temporary table have
//          to truncate table and all the transactions using transaction
//          specific temporary table have to end their transactions.

SQL> select owner,table_name,temporary  from dba_tables
   2  where table_name like 'TEMP%';

OWNER                   TABLE_NAME              T
------------------------------ ------------------------------ -
TEST                   TEMP_OBJECTS2              Y
TEST                   TEMP_OBJECTS              Y

SQL> SELECT OWNER,INDEX_NAME,TEMPORARY FROM DBA_INDEXES
   2  WHERE INDEX_NAME LIKE 'IDX_TMP%';

OWNER                   INDEX_NAME              T
------------------------------ ------------------------------ -
TEST                   IDX_TMP_OBJ              Y

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> create index idx_temp_obj2 on temp_objects2(object_id);
create index idx_temp_obj2 on temp_objects2(object_id)
                               *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select * from v$diag_info
where name='Default Trace File'  2 
   3  ;

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
      1 Default Trace File
/home/oracle/diag_test/diag/rdbms/orcl/orcl/trace/orcl_ora_2682.trc

tkprof /home/oracle/diag_test/diag/rdbms/orcl/orcl/trace/orcl_ora_2682.trc /home/oracle/2682.txt

没看出来什么

基于会话级别临时表创建索引报ORA-14452的测试：

1.创建临时表，如果直接create temporary table会报ORA-14459:missing GLOBAL keyword，说明临时表只有global没有local。

SQL> create temporary table temp_objects3 on commit preserve rows as select * from dba_objects;
create temporary table temp_objects3 on commit preserve rows as select * from dba_objects
        *
ERROR at line 1:
ORA-14459: missing GLOBAL keyword

SQL> create global temporary table temp_objects3 on commit preserve rows as select * from dba_objects;

Table created.

SQL> select count(*) from temp_objects3;

  COUNT(*)
----------
      87118

2. 在当前会话创建索引，提示ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL> create index idx_temp_obj3 on temp_objects3(object_id);
create index idx_temp_obj3 on temp_objects3(object_id)
                               *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#       VALUE
---------- ---------- ----------
     35        0           0

3. 在另一个会话创建索引，竟然成功了

SQL> conn test/test
Connected.
SQL> create index idx_temp_obj3 on temp_objects3(object_id);

Index created.

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#       VALUE
---------- ---------- ----------
    39        0           0

4. 在35的会话中执行查询，看是否会走索引,也会走索引

SQL> explain plan for
   2  select /*+ index(IDX_TEMP_OBJ3) */ * FROM TEMP_OBJECTS3
   3  where object_id=1000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1360050766

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |    207 |      1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP_OBJECTS3 |      1 |    207 |      1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IDX_TEMP_OBJ3 |      1 |        |      1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

Note
-----
    - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> set autotrace on
SQL> select * from temp_objects3
   2  where object_id=1000;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1360050766

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |    207 |      1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEMP_OBJECTS3 |      1 |    207 |      1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IDX_TEMP_OBJ3 |      1 |        |      1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1000)

Note
-----
    - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
       7  recursive calls
       0  db block gets
      55  consistent gets
       0  physical reads
       0  redo size
        1343  bytes sent via SQL*Net to client
     512  bytes received via SQL*Net from client
       1  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
       0  rows processed

4. 查询dba_segments均查不到大小

SQL> select owner,segment_name,bytes from dba_segments
   2  where segment_name='IDX_TEMP_OBJ3';

no rows selected

SQL> select owner,segment_name,bytes from dba_segments
   2  where segment_name='TEMP_OBJECTS3';

no rows selected

SQL> select count(*) from temp_objects3;

  COUNT(*)
----------
      87118