表扫描与索引扫描返回的行数不一致(二)
2014-11-24 12:57:22
·
作者:
·
浏览: 1
0d6 ext#: 3 blk#: 5 ext size: 8
@
@ So, HWM is located at RDBA 0x014000d6 in extent_id=5 and block#=5 in that extent.
4. 这个查询结果可以明确索引多返回的行的区id：
select rid, a.relative_fno, a.block, e.owner, e.segment_name, e.segment_type, e.extent_id, e.blocks
from (select /*+ INDEX_FFS(
) */ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from
where is not null
minus
select /*+ FULL(
)*/ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from
) a
, dba_extents e
where a.relative_fno=e.relative_fno
and e.tablespace_name = upper('&tablespace_name')
and v.ts#=&tablespace_number
and (a.block between e.block_id and e.block_id+blocks-1);
注意：
- 以上SQL中请替换owner、表名、索引名和索引列。
- 表空间是上述步骤3提供的。
- 这个SQL查询提供了索引返回行位置的区extent。