Oracle数据库不能使用索引的原因定位
2012-04-25
00:00中国IT实验室佚名
Oracle数据库有时候出现不能使用索引的现象,出现该现象的原因有很多,该怎么去定位呢?本文我们主要就介绍这一部分内容。
首先,我们要确定数据库运行在何种优化模式下,相应的参数是: optimizer_mode .可在 svrmgrl 中运行"
show parameter optimizer_mode" 来查看。 ORACLE V7 以来缺省的设置应是 "choose"
,即如果对已分析的表查询的话选择 CBO ,否则选择 RBO .如果该参数设为" rule ",则不论表是否分析过,一概选用 RBO
,除非在语句中用 hint 强制。
其次,检查被索引的列或组合索引的首列是否出现在 PL/SQL 语句的 WHERE 子句中,这是"执行计划"能。
第三,看采用了哪种类型的连接方式。 ORACLE 的共有 Sort Merge Join
( SMJ )、 Hash Join ( HJ )和 Nested Loop Join ( NL
)。在两张表连接,且内表的目标列上建有索引时,只有 Nested Loop
才能有效地利用到该索引。 SMJ 即使相关列上建有索引,最多只能因索引的存在,避免数据排序过程。
HJ 由于须做 HASH
运算,索引的存在对数据查询速度几乎没有影响。
第四,看连接顺序是否允许使用相关索引。假设表 emp 的 deptno 列上有索引,表
dept 的列 deptno 上无索引, WHERE 语句有 emp.deptno=dept.deptno 条件。在做NL 连接时,
emp 做为外表,先被访问,由于连接机制原因,外表的数据访问方式是全表扫描, emp.deptno
上的索引显然是用不上,最多在其上。
第五,是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过,可能导致极差的"执行计划。
第六,是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较, ORACLE
会自动将字符型用to_number() 函数进行转换,从而导致第六种现象的发生。
第七,是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析,可用
SQL 语句" analyze table xxxx compute statistics for all indexes;" .
ORACLE 掌。
第八,索引列的选择性不高。 我们假设典型情况,有表emp
,共有一百万行数据,但其中的emp.deptno列,数据只有 4 种不同的值,如 10 、 20 、 30 、 40 .虽然 emp
数据行有很多, ORACLE 缺省认定表中列的值是在所有数据行均匀分布的,也就是说每种 deptno 值各有 25
万数据行与之对应。假设 SQL 搜索条件 DEPTNO=10 ,利用 deptno 列上的索引进行数据搜索效率,往往不比全表扫描的高,
ORACLE 理不是在4 种deptno 值间平均分配,其中有99 万行对应着值10 , 5000 行对应值 20 , 3000
行对应值 30 , 2000 行对应值 40 .在这种数据分布图案中对除值为 10 外的其它 deptno
值搜索时,毫无疑问,如果索引能被应用,那么效率会高出很多。我们可以采用对该索引列进行单独分析,或用 analyze
语句对该列建立直方图,对该列搜集足够的统计数据,使 ORACLE 在搜索选择性较高的值能用上索引。
第九,索引列值是否可为空( NULL )。如果索引列值可以是空值,在 SQL
语句中那些需要返回 NULL 值的操作,将不会用到索引,如 COUNT ( * ),而是用全表扫描。这是因为索引中存储值不能为全空。
第十,看是否有用到并行查询( PQO )。并行查询将不会用到索引。如我们想要用到A
表的IND_COL1 " SELECT * FROM A WHERE COL1 =
XXX;"注意,注释符必须跟在SELECT之后,且注释中的" + "要紧跟着注释起始符" /* "或" -- ",否则 hint
就被认为是一般注释,对 PL/SQL 语句的执行不产生任何影响。
一种是 EXPLAIN TABLE 方式。
用户必须首先在自己的模式( SCHEMA )下,建立 PLAN_TABLE 表,执行计划的每一步骤都将记录在该表中,建表
SQL 脚本为在 ${ORACLE_HOME}/rdbms/admin/ 下的 utlxplan.sql 打开 SQL*PLUS
,输入" SET AUTOTRACE ON ",然后运行待调试的 SQL 语句。在给出查询结果后, ORACLE
将显示相应的"执行计划",包括优化器类型、执行代价、连接方式、连接顺序、数据搜索路径以如果我们不能确定需要跟踪的具体 SQL
语句,比如某个应用使用一段时间后,响应速度忽然变慢。我们这时可以利用 ORACLE 提供的另一个有力工具 TKPROF
,对应用的执行过程全程跟踪。
我们要先在系统视图 V$SESSION 中,可根据 USERID 或 MACHINE
,查出相应的 SID 和 SERIAL# .以SYS 或其他有执行 DBMS_SYSTEM 程序包的用户连接数据库,执行"
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION ( SID , SERIAL# , TRUE
);".然后运行应用程序,这时在服务器端,数据库参"
USER_DUMP_DEST "指示的目录下,会生成 ora__xxxx.trc 文件,其中 xxxx 为被跟踪应用的操作系统进程号。应用程序执行完成后,用命令 tkprof 对该文件进行分析。
命令示例:" tkprof tracefile outputfile explain=userid/password"
.在操作系统 ORACLE 用户下,键入" tkprof ",会有详细的命令帮助。分析后的输出文件 outputfile
中,有每一条 PL/SQL
语句的"执行计划"、 CPU 占用、物理读次数、逻辑读次数、执行时长等重要信息。根据输出文件的信息,我们可以很快发现应用中哪条
PL/SQL 语句是问题的症结所在。
关于Oracle数据库不能使用索引的原因定位就介绍到这里了,希望本次的介绍能够对您有所帮助。
相关推荐
oracle、sqlserver数据库批量删建索引,方便好用,提高数据库查询效率,提升系统运行效率,特别是数据量比较大的情况下
Oracle数据库索引的维护
Oracle数据库中索引的维护,介绍关于索引的维护 oracle 版本 8.0
通过两个图形说明了在oracle数据库中b-tree索引和位图索引的工作原理
oracle数据库中如何建立索引,建立索引有什么优势,索引该如何使用
数据库索引
1.索引的创建与使用 2.创建索引的原则 3.索引的分类 4.创建索引的多种方法 5.管理索引 6.索引优化 7.查看、修改索引属性 8.修改索引名 9.删除索引
导致索引失效: 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。
【完整课程列表】 oracle_系统表.pdf ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
Oracle数据库中的索引管理技术.pdf
oracle数据库索引与sql的优化介绍了访问oracle数据库的一些优化措施
Oracle数据库索引优化方法探析.pdf
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
oracle数据库的优化 数据库的优化 2 概述 2 监控数据库的性能: 2 优化数据库磁盘I/O 2 建立和优化数据库文件的方针: 6 监控磁盘I/O的方法: 7 优化回滚段 7 检测回滚段争用: 7 通过以下公式计算等待比率: 8 若...
详解Oracle数据库中文全文索引
Oracle字段上建立并使用索引 29 用Windows脚本宿主自动化Oracle工具 31 进程结构和内存结构 32 Oracle监控数据库性能的SQL汇总 36 Oracle如何精确计算row的大小 38 PL/SQL编程 39 数据库的分组问题 41 oracle知识 42...
Oracle数据库--索引讲义