学习
实践
活动
专区
工具
TVP
写文章
专栏首页bisal的个人杂货铺表扫描与索引扫描返回的行数不一致

表扫描与索引扫描返回的行数不一致

某个应用最近总出现死锁,其中一些是因为报了索引和数据行存在不匹配的问题,MOS中有如下文档可以参考。 ORA-1499. Table/Index row count mismatch(文档 ID 563070.1) 现象: 使用“validate structure cascade”分析表时报ORA-1499的错误,trace文件中包含“Table/Index row count mismatch”的错误信息。例如: SQL> analyze table test validate structure cascade; analyze table test validate structure cascade * ERROR at line 1: ORA-01499: table/index cross reference failure - see trace file  trace文件中包含: Table/Index row count mismatch  table 6559 : index 10000, 0  Index root = tsn: 6 rdba: 0x01400091 意味着扫描表返回6559行数据,索引扫描返回10000行数据。“Index root”是索引的段头信息。rdba: 0x01400091是相对于数据块地址的索引段头。他是十进制的20971665,Rfile#=5,Block#=145。

SQL> select dbms_utility.data_block_address_file(20971665)  "Rfile#"  ,dbms_utility.data_block_address_block(20971665) "Block#"  from dual;    Rfile#     Block#    ---------- ----------    5          145

运行下面的查询明确关联的索引:

SQL> select owner, segment_name, segment_type  from  dba_segments  where header_file = 5  and header_block = 145;  

OWNER    SEGMENT_NAME    SEGMENT_TYPE  -------- --------------- ------------------  SCOTT    I_TEST          INDEX 

这种逻辑不一致性也能通过10g以上版本的ORA-600 [kdsgrp1]错误或低版本的ORA-600 [12700]错误来说明。

原因

这是一种表与索引之间的逻辑不一致。这种逻辑不一致通常是因为表上的高水位(HWM)出现了问题,全表扫描比索引扫描返回了更少的行。这种不一致性也可能是由于Oracle的defect或会引起IO丢失的OS/硬件问题导致的。

解决方案

可以通过下面的语句查询出全表扫描时未扫出的索引行:

select /*+ INDEX_FFS(<tablename> <indexname identified in 2.1>) */ rowid        , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno        , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block  from <tablename>  where <indexed column> is not null  minus  select /*+ FULL(<tablename>)*/ rowid       , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno       , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block  from <tablename>;  实例:

select /*+ INDEX_FFS(TEST I_TEST) */ rowid        , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno        , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block  from test  where c2 is not null  minus  select /*+ FULL(TEST)*/ rowid        , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno        , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block  from test; 

使用下面PLSQ中的索引,可以将全表扫描丢失的行存储到另一张表中:

drop table test_copy;  create table test_copy as select * from test where 1=2;  declare    cursor missing_rows is          select /*+ INDEX_FFS(TEST I_TEST) */ rowid rid          from test           where c2 is not null          minus          select /*+ FULL(TEST)*/ rowid rid           from test;  begin    for i in missing_rows loop        insert into TEST_COPY            select /*+ ROWID(TEST) */ * from TEST where rowid = i.rid;     end loop;  end;  /

- 当索引返回的比表记录少时,重建索引可以解决这个问题。

- 当索引返回的比表记录多时,重建索引或执行虚拟insert插入该表的操作以提高HWM,可以最终解决这种逻辑错误。在以上这个案例中,修复了逻辑错误,但这些行也还是可能丢失了,因为是在执行这里提到的方法之前运行了上述PLSQL脚本。

如果从Oracle Support需要额外的帮助,请提供:

1. analyze语句分析的trace文件。

2. 第一个查询语句的结果。

3. dump基表段头产生的trace文件。

select header_file, header_block, tablespace_name from   dba_segments 

where  owner = upper('&table_owner') and segment_name = upper('&table_name'); alter system dump datafile &header_file block &header_block;

trace文件用来明确HWM。

@ Example from a block dump not using ASSM (Automatic Segment Space Management):   @   @ Extent Control Header   @ -----------------------------------------------------------------   @ Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 31   @ last map 0x00000000 #maps: 0 offset: 4128   @ Highwater:: 0x014000d6 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(<table name> <index name>) */ rowid rid              , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno              , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block        from <table owner.table name>        where <indexed column> is not null        minus        select /*+ FULL(<table name>)*/ rowid rid              , dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno              , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block        from <table owner.table name>       ) 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。

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!
本文分享自作者个人站点/博客:http://blog.csdn.net/bisal复制
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • 续《表扫描与索引扫描返回的行数不一致》

    上篇文章主要介绍了如何从分析表得到的报错,以及trace中的信息,判断表返回的记录与索引返回记录不一致时的处理方式。下面这篇文章则介绍了针对ORA-1499进行...

    bisal
  • 使用索引快速全扫描(Index FFS)避免全表扫描的若干场景

    2. Index FFS只能通过CBO(Index hint强制使用CBO)获得。

    bisal
  • 什么是Oracle索引扫描的批量回表操作?

    我们知道,优化器是关系型数据库SQL执行的关键,决定了执行路径和效率。Oracle的优化器,随着不同版本,同样做着演进。

    bisal
  • @dbsnake-用合适的函数索引来避免看似无法避免的全表扫描

    昨天听了@dbsnake的SQL方法论,感觉比第一次要更有感觉,希望对实际工作能有帮助。

    bisal
  • 这个大表走索引字段查询的 SQL 怎么就成全扫描了,我TM人傻了

    今天收到运营同学的一个 SQL,有点复杂,尤其是这个 SQL explain 都很长时间执行不出来,于是我们后台团队帮忙解决这个 SQL 问题,却正好发现了一个...

    干货满满张哈希
  • 多对多业务,数据库水平切分架构一次搞定

    本文将以“好友中心”为例,介绍“多对多”类业务,随着数据量的逐步增大,数据库性能显著降低,数据库水平切分相关的架构实践。 一、什么是多对多关系 所谓的“多对多”...

    架构师之路
  • MySQL查询优化-基于EXPLAIN

    含有子查询的时候,表明各语句执行的先后顺序,如果数字相同,则按照先后顺序执行,如果为 null,则代表是结果集,不需要查询。

    用户2987604
  • 百亿关系链,架构如何设计?

    粉丝与关注,社交好友,都是典型的“多对多关系”的业务,这类业务的核心服务是好友中心,当关系链达到百亿之后,好友中心架构设计要考虑哪些因素,是本文将要分享的内容。

    架构师之路
  • 举一反三-分区裁剪作用的“新”发现

    作者介绍 ? 赵勇 云和恩墨北区技术工程师 专注于SQL审核和优化相关工作。曾经服务的客户涉及金融保险、电信运营商、政府、生产制造等行业。 分区裁剪的定义 分区...

    数据和云
  • 什么是MySQL的执行计划(Explain关键字)?

    本文作者王良辰,京东中台架构师,擅长分布式系统及高可用、高并发系统架构与设计。曾经为企业开发过多个通用脚手架,推崇以技术手段提升开发效率、约束开发行为。

    小灰
  • SQL 优化必懂知识点

    单个列唯一键(distict_keys)的数量叫做基数。比如性别列,该列只有男女之分,抛开中性,所以这一列基数就是主键列的基数等于表的总行数。基数的高低影响列的...

    田维常
  • mysql查询性能优化

    WindWant
  • 高效sql性能优化极简教程

    一,sql性能优化基础方法论 对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为: cpu消耗 ...

    架构师小秘圈
  • PostgreSQL统计信息的几个重要视图

    比如tup_returned值明显大于tup_fetched,历史SQL语句很多是全表扫描,存在没有使用索引的SQL,可结合pg_stat_statments查...

    yzsDBA
  • 面试官:count(*) 怎么优化?

    哈喽,我是狗哥。小伙伴都知道我最近换工作了,薪资、工作内容什么的都是我比较满意的。五月底也面试了有 6、7 家公司,应该拿了有 5 个 offer。这段时间也被...

    JavaFish
  • MySQL优化看这篇就对了

    我们在面试的时候经常被问到你如何对数据库优化?动不动就分库分表,但是实际上有几个有分库分表的经验呢?下面我们将介绍优化数据库的各个阶段。

    丁D
  • 索引与慢sql剖析

    `user_id` int(9) NOT NULL AUTO_INCREMENT,

    虞大大
  • 工作中遇到的99%SQL优化,这里都能给你解决方案(三)

    表示从表employees 中取出从10000行开始的5行记录。看似只查询5条记录,实际这条SQL是先读取10005条记录,然后抛弃前10000条记录,然后读...

    程序员小强
  • Oracle执行计划详解

    简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 ...

    java达人

扫码关注腾讯云开发者

领取腾讯云代金券