当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 子句时,应考虑怎样利用增加一个或两个特定的条件,迫使优化器使用索引。适当地选择一组特定的条件能减少扫描行,并且强制使用索引不会影响查询结果----但却提高了查询的执行速度。
分享到:
相关推荐
综上所述,Oracle数据库提供了多种确保字段唯一性的方法,包括唯一约束和唯一索引,它们在确保数据完整性、提高查询效率以及处理重复值方面都有各自的特点和适用场景。开发者可以根据具体需求和性能考虑选择合适的...
在Oracle数据库中,索引是一种重要的优化工具,用于加速对数据表的查询操作。然而,当在`WHERE`子句中使用特定的函数时,比如`UPPER`、`REPLACE`或`SUBSTR`,Oracle优化器可能无法有效地利用索引来提升查询速度。这...
本文将详细介绍Oracle数据库的对象管理、数据类型、触发器、索引和分区策略,以及备份与恢复的相关知识。 首先,关于Oracle数据库对象管理,主要包括创建和修改表结构、管理索引、维护表分区、创建和管理视图及序列...
因此,需要通过统计分析应用系统中的操作语句,以确定哪些字段应建立索引,实现索引的合理配置。 接着,文章探讨了数据库物理结构的优化。物理结构优化主要关注磁盘读写的并行性和效率。ORACLE系统本身具备一定的...
本入门教材主要针对初学者,旨在帮助你建立起对Oracle数据库的基本理解和操作技能。 一、Oracle数据库简介 Oracle数据库是由美国Oracle公司开发的,它提供了一套完整的数据管理和分析工具,包括数据库服务器、开发...
Oracle数据库还提供了强大的索引机制来优化查询性能,如B树索引、位图索引和函数索引等。另外,事务管理和并发控制是保证数据一致性和完整性的关键,需要了解事务的ACID特性,以及锁定和行级锁定机制。 在深入学习...
总结而言,Oracle数据库查询优化的方法主要集中在索引的合理运用和SQL语句的优化上。合理创建和管理索引,正确使用临时表,以及编写高效的SQL语句,都是提升数据库查询性能的重要手段。在实际操作中,还需要根据具体...
这一步骤涉及将E-R模型转换为具体的表结构,定义每个表的字段,建立表与表之间的关联,如学生表、课程表、考勤表、请假表等,并设置适当的主键、外键和索引,以保证数据的完整性和一致性。 在整个课程设计过程中,...
4. **JDBC驱动**:确保Logstash运行环境已包含Oracle的JDBC驱动(通常为ojdbc.jar),这允许Logstash与Oracle数据库建立连接并执行SQL查询。 5. **Elasticsearch索引管理**:导入数据时,你可能需要预先在Elastic...
Oracle数据库中的索引管理技术 索引是 Oracle 数据库中的一个重要对象,它可以大大提高数据库中的数据检索速度。索引是包含表中选定字段的信息的对象,这些信息以某种规律排序,有一个指针指向存放实际数据的记录。...
每个表包含一系列行(记录)和列(字段),并且表之间可以通过公共字段建立关联。 5. **表**:关系数据库的基本存储单元。一个表包含一个或多个字段(列)和多个记录(行)。 #### 四、主流关系型数据库简介 1. **...
建立索引的基本原则包括:在经常与其他表连接的字段上建立索引;在频繁出现在Where子句的字段上建立索引,尤其是大数据表中的字段;索引应建在选择性高的字段上,同时应避免在大文本字段上建立索引;对于复合索引的...
之后,需要分析这些语句中Where条件子句所涉及的字段及其组合方式,并基于此初步判断哪些表的哪些字段应该建立索引。 建立索引时,通常需要遵循一些规则,例如: - 表的主键(Primary Key)和外键(Foreign Key)...
2. **调整查询逻辑**:例如,假设表`T1`的`name`字段建立了索引,那么将查询条件`'name' LIKE 'A%'`改为`'name' = 'A'`可以显著提高查询效率。 3. **添加额外的筛选条件**:适当增加WHERE子句中的筛选条件可以帮助...
Oracle数据库设计是IT领域中一项至关重要的技能,尤其对于企业级应用来说,Oracle以其稳定性、安全性以及强大的功能而广受青睐。本教程主要涵盖了Oracle数据库的安装配置、用户访问管理、表的创建与管理、操作符的...
- 不应该在含有NULL值的列上建立索引,因为Oracle优化器不会在where子句中使用包含`IS NULL`或`IS NOT NULL`的索引。这会导致即使列有索引,系统也会进行全表扫描。 2. **联接列**: - 在联接查询中,如果where...
Oracle数据库常用的字段类型包括CHAR、VARCHAR2、NUMBER、DATE等。CHAR类型是固定长度的字符串,而VARCHAR2是可变长度的字符串。NUMBER类型用于存储数字数据,可以指定总位数和小数点后的位数。DATE类型用于存储日期...
Oracle数据库在执行SQL查询时,有时会选择不使用已经创建的索引,这可能是由于多种因素导致的。以下是一些常见的原因及其详细解释: 1. **INDEX SKIP SCAN**: 当创建了一个组合索引,但是查询只使用了索引的非第一...