一种Oracle数据库索引优化方法与流程

本发明涉及一种数据库搜索方法，具体涉及一种oracle数据库索引的使用方法的改进。

背景技术：

索引是一种对数据库表中一列或多列的值进行排序的一种存储结构，是某个表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。创建索引可以大大加快数据的检索速度，可以加速表和表之间的连接，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是，随着数据库中索引的增加，也会带来一些不利的一个方面。

不利方面主要包括：1)创建索引和维护索引要耗费时间；2)索引需要占物理空间，索引越多，需要的空间就会更大；3)在数据库长期使用过程中会出现由于休眠索引或索引缺失导致的性能问题，大量的无效索引既影响数据的插入与更新效率又浪费存储空间，同时一些字段的索引缺失也会对数据的关联查询效率带来严重的影响。

因此，亟需要对数据库长期使用过程中所出现的由于休眠索引或索引缺失导致的性能与存储浪费问题进行修正。

技术实现要素：

本发明主要实现oracle数据库索引一定程度的自动化创建与回收，本发明提出的一种针对oracle数据库索引的优化方法，通过对索引、数据以及执行过的sql语句进行分析，对数据库长期使用过程中所出现的由于休眠索引或索引缺失导致的性能与存储浪费问题进行修正。

本发明的技术方案如下：

一种oracle数据库索引优化方法，所述优化方法包括如下步骤：步骤s1：回收低使用率索引；步骤s2：回收索引权重低的索引；步骤s3：多表关联；

其中，所述回收低使用率索引包括以下步骤：

第一步：扫描整个实例下索引使用频率；扫描数据库数据字典v$sql，分析中近期执行sql的执行计划，从执行计划中提取索引使用信息，计算sql中所涉及索引的使用次数；

第二步：将90天内未使用的索引列举出来；根据第一步计算得到的索引使用频率统计数据，提取出90天内未使用过的索引；

第三步：将90天内使用过的非低使用率索引进行保留；

第四步：对90天内未使用过的索引进行删除操作；执行ddldropindex删除索引；

第五步：重新扫描，确认无误，程序退出，操作完成。

进一步地，所述回收索引权重低的索引，包括以下步骤：

第一步：计算索引字段的离散度，具体包括如下步骤：

1)扫描索引字段；通过count(distinctinx及count(*)计算字段索引不重复值的个数(d)与该字段数据的个数(s)；

2)计算索引权重；

第二步：根据索引权重w判断离散度，索引权重w＞1的索引为离散度低的索引，索引权重w≤1的索引为非离散度低的索引；

第三步：将非离散度低的索引进行保留；

第四步：删除离散度低的索引：执行ddldropindex删除索引；

第五步：操作完成。

进一步地，所述多表关联包括以下步骤：

第一步：获取sql执行时间，执行计划信息；通过oraclesql执行计划提取相关执行信息；

第二步：判断sql效率高低；对于超过数据量十万数据集以上的sql，在简单关联下，计算操作记录数s(s，单位：万)与操作时间t(t，单位：秒)的比值w，若则sql效率低；

第三步：低效率sql判断关联字段是否有索引；通过关键字select/from/where将sql字符串拆分成三部分，分别对from/where中的连接条件进行分解，提取出关联字段与表名称，通过查询dba_ind_columns数据字典判断关联字段是否存在索引；

第四步：如果无索引，创建索引；执行ddlcreateindex创建索引；

第五步：创建后重新查看执行计划，达到阈值后通过，操作完成。

进一步地，所述阈值的标准为执行计划中的cost值减少15％以上。

本发明与现有技术相比，其核心优点在于：

1)本发明通过对索引、数据以及执行过的sql语句进行分析，对数据库长期使用过程中所出现的由于休眠索引或索引缺失导致的性能与存储浪费问题进行修正。

2)本发明通过对整个实例的索引使用频率的检测，将3个月内未使用过的索引定义为低使用率索引，对识别出的低使用率索引进行索引失效或删除操作。

3)本发明根据索引字段无重复值的个数(d)与该字段数据的个数(s)，计算得出索引权重w，将索引权重w＞1的索引进行失效或删除。

4)本发明对多表关联的sql，分析其执行计划，对关键字段创建索引，提升sql执行效率。

附图说明

附图1为低使用率索引回收的流程图；

附图2为权重低的索引回收的流程图：

附图3为多表关联创建索引的流程图。

具体实施方式

下面结合具体实施例来对本发明进行进一步说明，但并不将本发明局限于这些具体实施方式。本领域技术人员应该认识到，本发明涵盖了权利要求书范围内所可能包括的所有备选方案、改进方案和等效方案。

一种oracle数据库索引优化方法，所述方法包括如下步骤：步骤s1：回收低使用率索引；步骤s2：回收索引权重低的索引；步骤s3：多表关联。

如图1所示，所述回收低使用率索引包括以下步骤：

第一步：扫描整个实例下索引使用频率；扫描数据库数据字典v$sql，分析中近期执行sql的执行计划，从执行计划中提取索引使用信息，计算sql中所涉及索引的使用次数；

第二步：将90天内未使用的索引列举出来；根据第一步计算得到的索引使用频率统计数据，提取出90天内未使用过的索引；

第三步：将90天内使用过的非低使用率索引进行保留；

第四步：对90天内未使用过的索引进行删除操作；执行ddldropindex删除索引；

第五步：重新扫描，确认无误，程序退出，操作完成。

如图2所示，所述回收索引权重低的索引，包括以下步骤：

第一步：计算索引字段的离散度，具体包括如下步骤：

1)扫描索引字段；通过count(distinctinx及count(*)计算字段索引不重复值的个数(d)与该字段数据的个数(s)；

2)计算索引权重；

第二步：根据索引权重w判断离散度，索引权重w＞1的索引为离散度低的索引，索引权重w≤1的索引为非离散度低的索引；

第三步：将非离散度低的索引进行保留；

第四步：删除离散度低的索引：执行ddldropindex删除索引；

第五步：操作完成。

如图3所示，所述多表关联包括以下步骤：

第一步：获取sql执行时间，执行计划信息；通过oraclesql执行计划提取相关执行信息；

第二步：判断sql效率高低；对于超过数据量十万数据集以上的sql，在简单关联(一个超千万大表，不超过五个十万以下小表)下，计算操作记录数s(s，单位：万)与操作时间t(t，单位：秒)的比值w，若则sql效率低；

第三步：低效率sql判断关联字段是否有索引；通过关键字select/from/where将sql字符串拆分成三部分，分别对from/where中的连接条件进行分解，提取出关联字段与表名称，通过查询dba_ind_columns数据字典判断关联字段是否存在索引；

第四步：如果无索引，创建索引；执行ddlcreateindex创建索引；

第五步：创建后重新查看执行计划，达到阈值(执行计划中的cost值减少15％以上)后通过，操作完成。

如上所示，本发明虽然已参照有限的实施例和附图进行了说明，但在本发明所属领域中具备通常知识的人均可以从此记载中进行各种修改和变形。由此，其他实施例及权利要求书与等同物均属于权利要求的保护范围。