select owner,index_name,index_type from dba_indexes where table_name='XXX_XX_XX_XXXXX_OLD' and owner='XXXXXMIN';
OWNER INDEX_NAME INDEX_TYPE
------------------------------------------- ---------------------------
XXXXXMIN PK_XXX_XX_XX_XXXXX NORMAL
--索引创建语句如下：
DBMS_METADATA.GET_DDL(UPPER('INDEX'),UPPER('PK_XXX_XX_XX_XXXXX'),UPPER('XXXXXMIN'))
-----------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX "XXXXXMIN"."PK_XXX_XX_XX_XXXXX" ON "XXXXXMIN"."XXX_XX_XX_XXXXX_OLD" ("AX_ID", "BX_ID")
根据"XXXXXMIN"."PK_XXX_XX_XX_XXXXX"索引的创建语句，该索引为B树索引，它是基于二叉树的,由分支块和叶子块组成，包括每个索引列的值和行所对应的ROWID。
通过下面的语句查询出全表扫描时和索引扫描时存在差异的行：
1 select /*+ INDEX_FFS(t pk_xxx_xx_xx_xxx) */ rowid,
2 dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno,
3 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
4 from XXXXXMIN.XXX_XX_XX_XXXXX_OLD t where (t.AX_ID is not null or BX_ID is not null)
5 minus
6 select /*+ FULL(t1)*/ rowid,
7 dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno,
8 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block from XXXXXMIN.XXX_XX_XX_XXXXX_OLD t1;
查询结果如下：
ROWID RELATIVE_FNO BLOCK
------------------ ------------ ----------
AABOd5AAIAAAfQ1AAP 8 128053
AABOd5AAIAAAfQ1AAQ 8 128053
AABOd5AAIAAAfQ1AAR 8 128053
AABOd5AAIAAAfQ1AAY 8 128053
AABOd5AAIAAAfQ1AAZ 8 128053
AABOd5AAIAAAfQ1AAa 8 128053
AABOd5AAIAAAfQ1AAb 8 128053
AABOd5AAIAAAfQ1AAc 8 128053
AABOd5AAIAAAfQ1AAd 8 128053
AABOd5AAIAAAfQ1AAe 8 128053
AABOd5AAIAAAfQ1AAf 8 128053
AABOd5AAIAAAfQ1AAg 8 128053
AABOd5AAIAAAfQ1AAq 8 128053
AABOd5AAIAAAfQ1AAr 8 128053
AABOd5AAIAAAfQ1AAs 8 128053
15 rows selected.
5、验证该表全表扫描与索引扫描时存在差异行
根据下面语句找出差异的数据：
select e.*,rowid from XXXXXMIN.XXX_XX_XX_XXX_OLD e where e.rowid > (select min(x.rowid) from XXXXXMIN.XXX_XX_XX_XXX_OLD x where x.AX_ID=e.AX_ID and x.BX_ID=e.BX_ID);
AX_ID BX_ID COMMITED_XXXXX UNCOMMITED_XXXXX UNSHARED_XXXXX UPDATED_T ROWID
---------- ---------- -------------- ---------------- -------------- --------- ------------------
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAM
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAN
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAP
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAL
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAQ
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACA
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACl
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACk
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAB
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAE
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACC
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACm
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAD
**** ** ********** *************** ********** ********* AABOd5AAIAABFRCACB
**** ** ********** *************** ********** ********* AABOd5AAIAAAzAPAAO
15 rows selected.
取其中一条数据来验证走全表扫描和索引扫描时的差异