不可用索引

中国IT实验室收集整理

佚名

2008-4-1

保存本文

推荐给好友

收藏本页

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

select rowid,a.* from temp_jp a;
ROWID                    COL1 COL2
------------------ ---------- --------------------
AAA16tAAEAAAADoAAA          1 RAMA
AAA16tAAEAAAADoAAB          3 SITA
AAA16tAAEAAAADoAAC          2 LAKSHMAN

　　在移动表数据之前，我们先来保存从temp_jp表得到的当前RowID，以便稍后进行对照参考。

　　create table temp_rowids_jp as select col1,rowid as before_move_rowid from temp_jp;

　　从RowID找出相应的对象id、文件id和行号：

select dbms_rowid.rowid_object(rowid) "object",
      dbms_rowid.rowid_relative_fno(rowid) "file",
      dbms_rowid.rowid_block_number(rowid) "block",
      dbms_rowid.rowid_row_number(rowid) "row" from temp_jp
    /
    object       file      block        row
---------- ---------- ---------- ----------
    220845          4        232          0
    220845          4        232          1
    220845          4        232          2

　　接着，从dba_object中找到ID为220845的对象所有者、对象名和对象类型。

select object_id,owner, object_name, object_type,created from dba_objects where object_id = 220845;
OBJECT_ID   OWNER OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ --------------- ------------------- ---------
   220845   SCOTT TEMP_JP          TABLE             30-MAR-08
select object_id,owner,object_name,object_type,created
   from dba_objects where created > trunc(sysdate) -1/24;
OBJECT_ID   OWNER OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ --------------- ------------------- ---------
   220846   SCOTT TEMP_JP_IDX       INDEX              30-MAR-08
   220845   SCOTT TEMP_JP           TABLE              30-MAR-08
   220847   SCOTT TEMP_ROWIDS_JP    TABLE              30-MAR-08
select object_id,owner,object_name,object_type,created
from dba_objects   where object_id in (select max(object_id) from dba_objects);
OBJECT_ID   OWNER OBJECT_NAME      OBJECT_TYPE         CREATED
---------   ------ --------------- ------------------- ---------
   220847   SCOTT TEMP_ROWIDS_JP    TABLE              30-MAR-08

接下来，让我们用当前的RowID来查询表temp_jp的数据。

select * from temp_jp where rowid = 'AAA16tAAEAAAADoAAB';
COL1 COL2
---------- --------------------
3 SITA

　　结果显示，RowID正确地指向了表temp_jp相应行的位置。表temp_jp的temp_jp_idx索引存储了该表数据的正确RowID。从RowID算得的object_id已经转换到正确的表了。使用RowID可以准确的访问该表的数据。

　　这样，我们就可以开始测试了，将该表移动到另外一个表空间，然后看看会出现什么情况。

上一页 [1] [2] [3] [4] [5] 下一页