创建基于事务和基于会话的临时表及临时表建索引的实验(二)

Index created

SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

------------------------------ --------------------------- ------------------------------ ------------------------------

IDX_TEMP3 NORMAL BYS TEMP3

回到会话1：查询新建索引的相关信息

14:36:21 SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

------------------------------ --------------------------- ------------------------------ ------------------------------

IDX_TEMP3 NORMAL BYS TEMP3

14:44:02 SQL> select count(*) from temp3;

COUNT(*)

----------

72009

14:44:35 SQL> select * from temp3 where object_id=50;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY NAMESPACE EDITION_NAME

------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------

14:44:58 SQL> select table_name,tablespace_name from user_tables where table_name='TEMP3';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

TEMP3

14:46:19 SQL> select index_name,tablespace_name from user_indexes where table_name='TEMP3';

INDEX_NAME TABLESPACE_NAME

------------------------------ ------------------------------

IDX_TEMP3

14:47:05 SQL> select segment_name,tablespace_name from user_segments where segment_name in('TEMP3','IDX_TEMP3');

SEGMENT_NAME TABLESPACE_NAME

-------------------------------------------------------------------------------- ------------------------------

会话3：在新建的会话上，测试查询是否能用到索引。第一次查询未用到索引，因为表内无数据。第二次查询从执行计划中可以看到使用了索引

SQL> set autotrace on explain;

SQL> select * from temp3 where object_id=111;

未选定行

执行计划

----------------------------------------------------------

Plan hash value: 2448592476

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TEMP3 | 1 | 207 | 2 (0)| 00:00:01 |

---------------------------------------------------------------------------

SQL>

SQL> set autotrace off;

SQL> select index_name,index_type,table_owner,table_name from user_indexes where table_name='TEMP3';

INDEX_NAME INDEX_TYPE

------------------------------ --------------------