在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 子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。
猜你喜欢
- 服务器现在同时输出json和xml两种数据,取决于服务程序和页面之间的约定。在程序遇到问题的时候会返回错误信息,也按照相同的约定会返回jso
- 小整数/* interpreter state */#define _PY_NSMALLPOSINTS &nbs
- 概要不要以为 Python 有自动垃圾回收就不会内存泄漏,本着它有“垃圾回收”我有“垃圾代码”的精神,现在总结一下三种常见的内存泄漏场景。无
- 卷积在pytorch中有两种实现,一种是torch.nn.Conv2d(),一种是torch.nn.functional.conv2d(),
- 环境:1 .Windows Server 2016 Datacenter 64位2 .SQL Server 2016 Enterprise
- 有很多对于PHP的抱怨,甚至这些抱怨也出自很多聪明的人。当Jeff Atwood写下对于PHP的另一篇抱怨文章之后,我思考了下PHP的好的方
- 1、绝对导入和相对导入绝对导入:按照sys.path顺序搜索,先主目录(sys.path中第一项''),然后PYTHONPA
- 存储和读取ASCII码形式的byte数据Python可以存byte数据到txt,但不要用str的方式直接存,转成数字列表储存,这样方便读取L
- 相信很朋友因为PyCharm最新激活码或激活补丁的安装参数每月都会失效而烦恼PyCharm最新激活码先分享个PyCharm最新激活码大家可以
- 有这种要求,更新自己本身的字段的某个值进行加或者减常规方法:UPDATE tbl_kintai_print_hisSET &nb
- 在使用Sublime Text3 的时候导numpy的包发现报错,找不到这个包,这是因为要配置pip源才能正常导包,进行from numpy
- 简介python可以做很多事情,虽然它的强项在于进行向量运算和机器学习、深度学习等方面。但是在某些时候,我们仍然需要使用python对外提供
- 目标:目标文件为一个float32型存储的二进制文件,按列优先方式存储。本文使用Python读取该二进制文件并使用matplotlib.py
- 在使用lepus3.7监控MySQL数据库的时候,碰到了以下几个问题,本博客给出了这些问题产生的原因,以及相应的解决办法。1. 问
- 近来在做数据库设计,有时候真弄不清SQL2000里的数据类型,所以摘了这篇文章。 (1)char、varchar、text和nchar、nv
- SQL Server导出表到EXCEL文件的存储过程:*--数据导出EXCEL导出表中的数据到Excel,包含字段名,文件为真正的Excel
- 程序还不是很精简,以后再修改,程序所用的数据库为-- “冯志宏”-- 所写的--“追捕”--软件中所带IP数据库和“国华软件 Guohua
- 如下所示:Uploadfiles = request.FILES.get('参数', '')for i in
- GIT安装访问: https://git-scm.com/downloads ,进入git'下载页面,根据个人操作系统下载对应软件版
- 本文实例讲述了python通过apply使用元祖和列表调用函数的方法。分享给大家供大家参考。具体实现方法如下:def my_fuc(a, b