在OracleE数据库的字段上建立索引的方法
来源:asp之家 发布时间:2009-02-26 10:34:00
当where子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引。
通常情况下,如果在WHERE子句中不使用诸如UPPER、REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的条件。
但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间。 庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询。这篇文章介绍了涉及的技术,并说明了在两种典型情况下怎样实现。
大小写混合情况 在讨论由于函数修改了列的内容,如何强制使用索引前,让我们首先看看为什么Oracle优化器在这种情况下不能使用索引。假定我们要搜寻包含了大小写混合的数据,如在表1中ADDRESS表的NAME列。因为数据是用户输入的,我们无法使用已经统一改为大写的数据。
为了找到每一个名为john的地址,我们使用包含了UPPER子句的查询语句。如下所示: SQL> select address from address where upper(name) like 'JOHN';在运行这个查询语句前,如果我们运行了命令"set autotrace on", 将会得到下列结果,其中包含了执行过程: ADDRESS cleveland 1 row selected. Execution Plan SELECT STATEMENT TABLE ACCESS FULL ADDRESS可以看到,在这种情况下,Oracle优化器对ADDRESS 表作了一次完整的扫描,而没有使用NAME 列的索引。
这是因为索引是根据列中数据的实际值建立的,而UPPER 函数已经将字符转换成大写,即修改了这些值,因此该查询不能使用这列的索引。优化器不能与索引项比较"JOHN",没有索引项对应于"JOHN"-只有"john" 。
值得庆幸的是,如果在这种情况下想要强制使用索引,有一种简便的方法:只要在WHERE 子句中增加一个或多个特定的条件,用于测试索引值,并减少需要扫描的行,但这并没有修改原来SQL 编码中的条件。
以下列查询语句为例:
SQL> select address from address where upper(name) like 'JO%' AND (name like 'J%' or name like 'j%');
使用这种查询语句(已设置AUTOTRACE),可得到下列结果:
ADDRESS cleveland 1 row selected. Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I
现在,优化器为WHERE 子句中AND 联结的两个语句中每一个语句确定的范围进行扫描----第二个语句没有引用函数,因而使用了索引。在两个范围扫描后,将运行结果合并。
在这个例子中,如果数据库有成百上千行,可以用下列方法扩充WHERE 子句,进一步缩小扫描范围:
select address from address where upper(name) like 'JOHN' AND (name like 'JO%' or name like 'jo%' or name like 'Jo' or name like 'jO' );
得到的结果与以前相同,但是,其执行过程如下所示,表明有4个扫描范围。
Execution Plan SELECT STATEMENT CONCATENATION TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I TABLE ACCESS BY INDEX ROWID ADDRESS INDEX RANGE SCAN ADDRESS_I
如果试图进一步提高查询速度,我们可以在特定的"name like"条件中指明3个或更多的字符。然而,这样做会使得WHERE子句十分笨重。因为需要大小写字符所有可能的组合-joh ,Joh,jOh,joH等等。除此之外,指定一个或两个字符已足以加快查询的运行速度了。 现在让我们看看,当我们引用不同的函数时,怎样运用这个基本技术。
使用REPLACE的情况 正如名字不总是以大写输入一样,电话号码也会以许多格式出现: 如 123-456-7890, 123 456 7890,(123)456-7890 等等。
如果在列名为 PHONE_NUMBER中搜寻上述号码时,可能需要使用函数REPLACE以保证统一的格式。如果在PHONE_NUMBER列中只包含空格、连字符和数字,where 子句可以如下所示: WHERE replace(replace(phone_number , '-' ) , ' ' ) = '1234567890'WHERE子句两次使用REPLACE 函数去掉了连字符和空格,保证了电话号码是简单的数字串。然而,该函数阻止了优化器在该列使用索引。
因此,我们按如下方法修改WHERE子句,以强制执行索引。 WHERE replace(replace(phone_number, '-' ) , ' ' ) = '1234567890'AND phone_number like '123% '如果我们知道数据中可能包含圆括号,WHERE 子句会稍微复杂一点。
我们可以再增加REPLACE 函数(去掉圆括号、连字符和空格),按如下所示扩充增加的条件: WHERE replace(replace(replace(replace(phone_number , ' - ' ) ,' '), '( ' ) , ' ) ' ) = '1234567890' AND (phone number like ' 123% ' or phone_number like ' (123% ' ) '该例强调了巧妙地选用WHERE 子句条件的重要性,而且,这些条件不会改变查询结果。你的选择应基于完全了解该列中存在的信息类型。在该例中,我们需要知道 PHONE_NUMBER 数据中存在几种不同的格式,这样,我们能够修改WHERE 子句而不会影响查询结果。
正确的条件
以后当你遇到包含CHARACTER 数据修改函数列的WHERE 子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。
猜你喜欢
- 我们以用户查询语句为 https://www.aspxhome.com/chunfeng.asp为例来查询用户资料将从数据库Contact1
- ASP获取远程文件的通过header头信息,并返回远程文件大小信息,远程文件可以是网页或RAR,EXE任何格式的文件。以下是具体代码:<
- 我用FSO生成了unicode的文件,但这不是我想要的。FSO可以生成utf-8格式编码的html文件吗?用什么办法才能生成?FSO的编码问
- 对于任何一个开发项目来说最大的错误可能就是没有计划。最近,有些人认为开始前无需计划,一个优秀的开发者需要的是随机应变。我敢肯定这样的做法最后
- 这篇文章介绍了 5 个实用的 CSS 属性。你应该很熟悉,但很可能很少会使用到。我并不是在谈论展望全新的 CSS3 属性,我指的是旧的 CS
- 找到一句可以获得当前最新ID的语句,如下:conn.execute("insert into member (user,code)
- 英文原文:http://www.myinkblog.com/2009/03/21/4-principles-of-good-design-f
- 我们进行CSS网页布局的时候,都知道它需要符合XHTML1.0规范。如果我们在进行CSS网页布局的时候,还在使用被W3C废弃的元素,那就失去
- 很多人一直都有个想法,要是可以随心所欲的操作iframe就好了。这样静态页面也就有了相当于后台动态页面php,jsp,asp中include
- “用户体验”作为舶来品在国内风靡已经有几个年头了,而且从目前情况来看仍旧会继续风靡一段时间。当某产品发布会上,发言人张口闭口就
- 一、 网页设计中的对比原则一件网页设计作品由许多元素构成,它们的重要性各不相同,其中一些元素的重要性要高于其它元素。一些相互关联,而另一些则
- rss.asp格式的 下面代码保存为rss.asp 代码如下:<!--#include file="conn.as
- 假设要生成一千万个随机数,常规的做法如下:var numbers = [];for (var&nbs
- 这个可应用于所有浏览器中.<SCRIPT language=javascript>var leave=true; functio
- 为 Web页指定 DOCTYPE 会影响浏览器呈现页的方式。Internet Explorer、Mozilla Firefox 和 Oper
- 五、过渡转化的使用在《mind hack》一书中,揭示了人脑鲜为人知的工作原理。其中提到了“突然的移动或闪烁会吸引人的注意力,这正是负责视觉
- 之前整理发表了《XMLHTTPRequest的属性和方法简介》,它ajax要使用的核心的技术之一,现在就来实际运用它。这个Ajax标签导航,
- 最近碰到一个mysql5数据库的问题。就是一个标准的servlet/tomcat网络应用,后台使用mysql数据库。问题是待机一晚上后,第二
- Win7或Windows server 2008中IIS7支持ASP+Access解决方法:1. 让IIS7支持ASPWin7或Window
- 页面中header部分一般位于最上端(有时根据需要也会位于某一侧,不管它在页面中的位置如何,在XHTML代码中它总是优于其它元素先加载),内