远程数据库的表超过20个索引的影响
昨天同事参加了一个研讨会,有提到一个案例。一个通过dblink查询远端数据库,原来查询很快,但是远端数据库增加了一个索引之后,查询一下子变慢了。
经过分析,发现那个通过dblink的查询语句,查询远端数据库的时候,是走索引的,但是远端数据库添加索引之后,如果索引的个数超过20个,就会忽略第一个建立的索引,如果查询语句恰好用到了第一个建立的索引,被忽略之后,只能走Full Table Scan了。
听了这个案例,我查了一下,在oracle官方文档中,关于Managing a Distributed Database有一段话:
Several performance restrictions relate to access of remote objects:
说到,如果远程数据库使用超过20个索引,这些索引将不被考虑。这段话,在oracle 9i起的文档中就已经存在,一直到12.2还有。
那么,超过20个索引,是新的索引被忽略了?还是老索引被忽略了?如何让被忽略的索引让oracle意识到?我们来测试一下。
初始化测试表可以看到,远程表有27个字段,目前还只是在前20个字段建立了索引,且第一个字段是主键。本地表,有6个字段,6个字段都建索引。
第一轮测试,远程表上有20个索引测试场景1:
在远程表20索引的情况下,本地表和远程表关联,用本地表的第一个字段关联远程表的第一个字段:
我们可以看到,对于远程表的执行计划,这是走主键的。
测试场景2:
在远程表20索引的情况下,本地表和远程表关联,用本地表的第一个字段关联远程表的第20个字段:
我们可以看到,对于远程表的执行计划,这是走索引范围扫描的。
测试场景3:
在远程表20索引的情况下,本地表和远程表关联,用本地表的第2个字段关联远程表的第2个字段:
我们可以看到,对于远程表的执行计划,这是走索引范围扫描的。
测试场景4:
在远程表20索引的情况下,本地表和远程表关联,用本地表的第2个字段关联远程表的第20个字段:
我们可以看到,对于远程表的执行计划,这是走索引范围扫描的。
建立第21个索引测试场景1:
我们可以看到,对于远程表的执行计划,如果关联条件是远程表的第一个字段,第一个字段上的索引是被忽略的,执行计划是选择全表扫描的。
测试场景2:
我们可以看到,对于远程表的执行计划,如果关联条件是远程表的第20个字段,这第20个字段上的索引是没有被忽略的,执行计划是走索引。
测试场景3:
我们可以看到,对于远程表的执行计划,如果关联条件是远程表的第2个字段,这第2个字段上的索引是没有被忽略的,执行计划是走索引。
测试场景4:
我们可以看到,对于远程表的执行计划,如果关联条件是远程表的第20个字段,这第20个字段上的索引是没有被忽略的,执行计划是走索引。
我们目前可以总结到,当远程表第21个索引建立的时候,通过dblink关联本地表和远程表,如果关联条件是远程表的第1个建立的索引的字段,那么这个索引将被忽略,从而走全表扫描。如果关联条件是远程表的第2个建立索引的字段,则不受影响。
似乎是有效索引的窗口是20个,当新建第21个,那么第1个就被无视了。
再次重复上面4个测试
测试场景1:
测试场景2:
测试场景3:
上述的测试,其实是可以验证我们的猜测的。oracle对于通过dblink关联访问远程表,只是会意识到最近创建的20个索引的字段。这个意识到索引的窗口是20个,一旦建立了一个新索引,那么最旧的一个索引会被无视。
尝试rebuild索引
rebuild第2个索引
所以我们看到,索引rebuild,是不能起到重新“唤醒”索引的作用。
尝试 drop and recreate 第2个索引我们可以看到,通过drop之后再重建,是可以“唤醒”第二个索引的。这也证明了我们20个索引识别的移动窗口,是按照索引的创建时间来移动的。
对于通过dblink关联本地表和远程表,如果远程表的索引个数增加到21个或以上,那么oracle在执行远程操作的时候,将忽略最早创建的那个索引,但是会以20个为窗口移动,最新建立的索引会被意识到。此时如果查询的关联条件中,使用到最早创建的那个索引的字段,由于忽略了索引,会走全表扫描。
要“唤醒”对原来索引的意识,rebuild索引无效,需要drop create索引。
在本地表数据量比较少,远程表的数据量很大,而索引数量超过20个,且关联条件的字段时最早索引的情况下,可以考虑使用DRIVING_SITE的hint,将本地表的数据全量到远程中,此时远程的关联查询可以意识到那个索引。可见文末的例子。是否使用hint,需要评估本地表数据全量推送到远程的成本,和远程表使用全表扫的成本。
附:在22个索引的情况下,尝试采用DRIVING_SITE的hint:
原文发布时间为:2017-10-26
本文作者:何剑敏
相关文章
- Derwent Innovations Index(1963-)--德温特专利索引数据库(Web of Science)
- 使用数据库引擎优化顾问添加建议索引
- phoenix连接hbase数据库,创建二级索引报错:Error: org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36, exceptions: Tue Mar 06 10:32:02 CST 2018, null, java.net.SocketTimeoutException: callTimeou
- 如何使用T-SQL备份还原数据库及c#如何调用执行? C#中索引器的作用和实现。 jquery控制元素的隐藏和显示的几种方法。 localStorage、sessionStorage用法总结 在AspNetCore中扩展Log系列 - 介绍开源类库的使用(一) span<T>之高性能字符串操作实测
- 【MongoDB数据库】Java MongoDB CRUD Example
- Oracle数据库:oracle数据定义语言DDL,查询表、序列、索引、视图、创建表,修改表的列alter,rename to,删除表数据truncate和表drop
- 数据库系列课程(23)-MongoDB 索引
- 用户调研 | 除了 Redis 外,贵司还部署了哪些数据库?
- 数据库索引对写入操作的影响
- 这样诊断和调优,轻松与数据库"timeout"说再见
- 时序数据库-存储初识1
- 《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.16 创建索引
- 数据库的连接、会话与SQLite
- 数据库的关系代数六大基本操作符
- SQL Server调优系列进阶篇(如何维护数据库索引)
- SQL2005 数据库——查看索引
- gbase数据库是什么?gbase数据库与MySQL区别
- SQL基础知识笔记:概述(层状/网状/关系模型)、数据类型、操作数据库能力(DDL/DML/DQL)、关系模型(主键、联合主键、外键、外键约束-性能影响、一对一、一对多、多对多、索引、索引效率)、实用SQL语句、事务(四个特性、四种隔离级别)
- Java连接MySQL8.0以上版本数据库方式(将驱动改成新版本的解决办法)
- 10gocm->session5->数据库管理实验->GC资源管理器的资源消耗组介绍
- Java开发 - 数据库索引的数据结构
- powerdesigner生成数据库说明文档
- 【MySQL】练习四 数据库安全性
- MySQL 安装mysql数据库