基于自定义索引的约束

《基于自定义索引的约束》由会员分享，可在线阅读，更多相关《基于自定义索引的约束（5页珍藏版）》请在金锄头文库上搜索。

1、基于自定义函数索引的约束自定义函数索引的结构特征决定了在使用时应当满足一些必要的制约条件。在基于一般的列创建索引时，只需要确保表中的列值遵循一致性约束就不会存在任何问题。但在基于函数的索引中，由于需要对物理列进行各种各样的加工运算，所在遵循一致性约束并非易事。当然，如果我们为了遵循列值的一致性约束而付出很大的代价，尽管能够达到目的，但从现实的角度来看其实并没有那个必要，因为对列执行简单的运算或轻微的加工并不会对创建基于自定义函数索引构成任何威胁。然而，如果使用了用户自定义的函数对列进行加工，则问题就从根本上发生了变化。这不仅仅是因为用户自定义的函数是用过程化语言编成的，而且还因为它的执行过程中

2、需要参考其他表中的信息，因此它不符合创建基于自定义函数索引的条件。换言之，任何能够以逻辑形式描述的所有表述方式都具有一定的制约条件，用户自定义函数也不例外。首先我们来学习一下基于自定义函数索引的约束条件和遵循事项。1、 只有在基于代价的优化器中才可以使用。2、 创建了基于自定义的函数索引后，必须收集统计信息。3、 必须将用户自定义函数声明为“DETERMINISTIC”类型。4、 必须将参数 QUERY REWRITE_ENABLE 设置为 TRUE。5、 必须拥有下面所列举的用户权限：创建索引的权限；查询重写的权限。6、 在函数或者表达式的返回结果为 NULL 的情况下，无法利用基于自定义函

3、数索引从表中读取数据。7、 在使用了用户自定义函数的情况下，需要注意函数的依赖性：对索引所使用的函数进行重新定义时，索引的状态变为不可用；索引使用权限被回收时，索引的状态变为不可用。8、 如果优化器选择了状态为不可用的索引，则 SQL 的执行就会失败。在此情况下对数据所执行的 DML 操作都将变得无效，必须用如下命令来恢复数据：使用命令“ALTER INDEX ENNABLE”“ALTER INDEX REBUILD”将索引设置为有用歌者重构；使用命令“ALTER INDEX UNUSABLE”可以将索引的态设置为未使用状态和不可用状态有所不同。当索引的状态为未使用时，因为根本就没有被使用，所

4、以没有必要遵循完整性约束。但是只有在参数“SKIP_UNUSABLE_INDEX”已经。被设置为 TRUE 的情况下，SQL 的执行才不会失败。我们可以通过使用重构（Rebuilt）索引或者重建（ Re_creation）索引的方法，将索引的状态恢复为可用。这里所谓的重建是指重新创建索引。9、 无法基于子查询得到的列创建自定义的函数索引。10、 无法基于 SYSDATE、USER、ROWNUM 等函数返回的逻辑列创建索引，因为这些函数返回值会随着情况不同而产生变化。11、 在应用了分区的情况下，不能基于分区键创建自定义函数索引。12、 在计算数字列字符或字符的数字值运算式中即使没有直接说明，在

5、内部也是使用转换函数来进行处理的。13、 由于自定义函数索引是以参数 NLS 为当前基准的，所以如果在会话级别上创建自定义索引，则会得到错误的结果，这一点应该注意。但是会话级别的定义对NLS_SORT 和 NLS_COMP 不会产生任何影响。14、 即使 where 条件中所使用的查询条件表达式与索引中所使用表达式有所不同，只要能够按照互换规则进行逻辑转换，也同样可以对其创建自定义函数索引。15、 在基于逻辑列创建自定义函数索引，因为那样做不仅加重了索引的负担，而且也增加了代价支出。因此在创建索引之前就需要我们以发展的角度，对所基于的列进行全面的分析和评估。对于创建自定义函数索引时所使用的用户

6、自定义函数需要再给予进一步说明。自定义函数索引的使用方法和约束条件会随着数据库管理系统版本的不同而不同，在使用时应当多加注意。在以前的数据库版本中如果自定义函数被重新创建了，则索引的状态就会变成不可用。在自定义函数创建结束之后，为了恢复索引的状态就必须重构索引。但是随着版本的升级（ORACLE 10G） ，恢复操作已经变得没有必要，在新的版本中即使重新创建了自定义函数也不会对自定义函数索引的可用状态产生任何影响。上面所介绍的内容究竟是什么意思呢？为了帮助各位读者对这部分内容有一个更好的理解，接下来对其进一步研究的说明。自定义函数索引类似于物化视图，其中所存储的数据都是加工过的列值。如果在 SE

7、LECT-list 中使用了索引中使用的表达式，则直接从索引中读取这些数据而不用再从表中读取。而当索引所基于的函数发生变化时，可以索引的状态设置为不可用。或者重建索引，或者直接提供原来没有加工过的列值，除此之外别无他法。在这里由于第三种方法无法使用，所以只能选择第一或第二种方法。从实际应用观点来看，在有些情况下，也可以把函数视为变化的，也就是把原函数所返回的数据值以自变量的形式输入到新的数据值的情况。如果无法通过这种方式来达到目的，那么也就只能将函数的状态设置为不可用或对索引进行重构。由此可见，新版本中的解决方法与老版本中的相比并不是不同，而是选择幅度变得更大了。当用户自定义函数需要参考其他表

8、中的数据时，可以采用相同的办法予以解决。假设某个函数需要参考其他多个表中的列，从索引所基于的表的立场来看，当对表中的数据进行插入或修改时，不仅函数值随之自动发生变化，而且索引中存储的数据值也同样发生改变，而我们根本就感觉不到其中的任何变化。如果在被参考表中执行了插入、修改、删除等操作，这些操作对自定义函数索引不会产生太大的影响。然而比较难处理的问题是当被参考表中的数据发生变化时，无法预测拥有自定义函数索引的表中那个行将受此变化而影响。如果一定要遵循一致性约束，那么只要参考表中的数据稍微变化就必须全面检查基于自定义函数索引中的所有列，并对受影响的行做出相应的改变，此项操作需要付出很大的代价。如果

9、每次操作需要执行此项校检操作，那么为其所付出的代价我们无论如何都无法接受。在过去的版本中所采用的方案就是强制性地不允许被参考表中的数据发生任何变化，这种做法岂不是使用问题变得更严重了吗？参考表也只是把自己的列借给其他表使用，没想到却要忍受自己数据不能更新操作的痛苦。另外，自定义函数索引所基于的表也会对索引产生一定的影响，即在自己不知不觉的情况下所参考的表中的数据发生了变化，而索引却仍然继续使用以前的数据。此时索引中所存储的一部分数据已经是错误数据了，所以该索引也变成了无用之物。所以，这种情况是无论如何都无法接受的。如果我们对这些问题进行归纳总结之后就会发现，其实问题只有一个，即当被参考表中的数

10、据发生变化时，如何确保存储在自定义函数索引中的数据随之发生变化。当然，如果不希望在这个问题上花费太多的时间和精力，那么每当变化发生时就可以将自定义函数索引的状态设置为不可用，或者选择对自定义函数索引进行重构。因此，这里并不推荐用需要参考其他表中的数据函数来创建自定义函数索引，尤其不推荐基于数据修改频繁的表创建自定义函数索引。但是像数据几乎不发生任何变化的表，确实有需要，为其创建自定义函数索引也无妨。基于自定义函数索引的灵活应用在实际工作中，自定义函数索引并未被广泛使用，当然我们也不推荐大量使用它。然而，如果各位读者能够在理解和掌握它的基础上，在合适的情况下正确运用它，将会是一个非常理想的解决方

11、案。在这里希望各位通过对以下几种运用类型进行学习，能够对自定义函数索引的使用方法有一个新的认识。一、解决表设计存在的问题由表设计上的不足而引发的各种问题在过去是很难解决的，现在利用自定义函数索引就可以得到很好的解决。尽管关系型数据库已经商业化很久了，但截至目前，仍有很多系统没有遵循它的基本原则。在这里我们所要说明的问题都是在数据模型被正确设计的情况下所不会出现的问题。由此可见，数据模型的设计在整个系统构建的过程中所起的重要作用。但遗憾的是，在实际应用中，数据模型没有被正确设计的案例超出了我们的想象，我们将对此情况下所经常出现的问题以案例的形式给予详细说明。首先要说明的第一个案例就是关于没有遵循

12、列的分割原则而出现的问题的解决方案。列值中间部分查询过去曾有一段时间，很多人认为编码非常重要，因此对键值的各个位都赋予了不同的意义，从而构成了比较长的每个数字或字母都代表不同意义的编码。这就像解读遗传因子一样，很多用户在应用程序中通过编定特定算法来分解这种编码。目前很多地方仍然保留这种残余，这种做法如同给数据加密一样，将不同的内容加密到一个编码中去。在设计数据模型时有一个需要遵循的原则，也就是列的原子性。在设计中如果没有遵循这一原则，则必然要在 SQL 语句中使用 SUBSTR 函数来对数据进行处理。如果需要处理的列没有基于其创建索引，则尽管问题比较简单，但当其中的某个列必须被使用在查询的中间

13、时，由于无法将其作为先行查询条件来使用，所以必须会导致索引无法被使用或不必要的数据被读取。如下所示，在这种情况下要么只基于列值中的一部分值创建自定义函数索引，要么基于这种列值与经常需要被一起使用的列创建组合索引，通过使用这种方法就可以在某种程度上解决此类问题。CREATE INDEX FROM_LOC_IDX ON ORDERS (SUBSTR(SHP_ID,5,3);CREATE INDEX REPAIR_ORD_IDX ON ORDERS (SUBSTR(SHIP_ID,3,2),ORD_DATE);连接条件列不相互对应问题在数据模型被正确设计的情况下是不会出现这种问题的，但在实际应用中此

14、类问题并不少见， 。假设父表是“品种类型(item_group) ”为了进行详细管理而将列分为 “CLASS1”“CLASS2”和“CLASS3” ，在子表中把父表所继续的属性命名为“组编码（GROPU_CD） ”。如果按照下面描述的条件执行表连接，则不论使用两种连接类型中的哪一种，都会由于一边无法使用索引而影响表连接的性能。FROM item_group x ,items y where x.class1|x.class2|x.class3=y.group_cdFROM item_group x ,items y where x.class1 =substr(y.group_cd,1,2)a

15、nd x.class2=substr(y.group_cd,3,2)and x.class3=substr(y.group_cd,5,3)如下所示，在此情况下可能通过为数据量较少的父表创建基于自定义函数索引的方法来解决此业问题。CREATE INDEX GROUP_CD_IDX ON ITEM_GROUP(class1|class2|class3);日期列分割问题虽然现在不会经常出现这种的问题，但是在关系型数据库被使用的初期，将日期随意分割为“年、月、日”的情况屡见不鲜。由于关系型数据库不使用指针，所以按照查询条件例如“”开始读取第一个满足条件的行之后无法再继续执行下一个扫描。在这种情况下可以

16、创建类似于以上例子的索引来解决类此问题。连接列数据类型不同的问题在系统构建的过程中，如果遵循了数据标准化规则并且事先对域进行了系统化管理，则不会出现这种问题。但遗憾的是在实际中，大多此类数据库设计者并没有遵循这些规则，而是随心所欲地设计创建表。也正是由于这个原因，对导致经常会出现某个表的主键的数据类型与继承该主键的外键数据类型不一致的现象。如果采用数据类型不同的列执行表连接，则由于在内部需要对列的数据类型进行转换，从而无法使得我们基于需要转换的列创建索引。在执行连接时如果没能指定出正确的表连接方向，则表连接的速度就会很大程度上受到影响。例如 DEPT 表中列 DEPTNO 的类型为数值型，而 EMP 的 DEPTNO 的类型为字符型，使用如下方法可以创建索引。CREATE INDEX DEPTNO_IDX ON EMP(TO_NUMBER(DEPTNO);连接列随着具体情况的不同而不同的表连接问题结合父表的列创建组合索引的问题二、解决错误数据查询的问题大小写或空格相互混合存在于列值中的查询问题替换 NULL 值查询