oracle sql性能优化 (下)(1)
26. 索引的操作 27. 基础表的选择
Java
Asp
PHP
.Net
XML
C/C++
CGI
VB
Jsp
J2ee
J2se
J2me
EJB
Servlet
Tomcat
Resin
Struts
Weblogic
Eclipse
ANT
GUI
JMS
Web servise
IDEA
Webphere
Hibernate
Spring
Jboss
Applet
Swing
Socket
Javamail
Perl
Ajax
P2P
安全
模式
框架
测试
开源
游戏
Windows XP
Windows 2000
Windows 2003
Windows Me
Windows 9.x
Linux
UNIX
注册表
操作系统
服务器
应用服务器
oracle对索引有两种访问模式.
索引唯一扫描 ( index unique scan)
大多数情况下, 优化器通过where子句访问index.
例如: 表lodging有两个索引 : 建立在lodging列上的唯一性索引lodging_pk和建立在manager列上的非唯一性索引lodging$manager.
select *
from lodging
where lodging = ‘rose hill';
在内部 , 上述sql将被分成两步执行, 首先 , lodging_pk 索引将通过索引唯一扫描的方式被访问 , 获得相对应的rowid, 通过rowid访问表的方式 执行下一步检索.
如果被检索返回的列包括在index列中,oracle将不执行第二步的处理(通过rowid访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.
下面sql只需要index unique scan 操作.
select lodging
from lodging
where lodging = ‘rose hill';
索引范围查询(index range scan)
适用于两种情况:
1. 基于一个范围的检索
2. 基于非唯一性索引的检索
例1:
select lodging
from lodging
where lodging like ‘m%';
where子句条件包括一系列值, oracle将通过索引范围查询的方式查询lodging_pk . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描低一些.
例2:
select lodging
from lodging
where manager = ‘bill gates';
这个sql的执行分两步, lodging$manager的索引范围查询(得到所有符合条件记录的rowid) 和下一步同过rowid访问表得到lodging列的值. 由于lodging$manager是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.
由于sql返回lodging列,而它并不存在于lodging$manager索引中, 所以在索引范围查询后会执行一个通过rowid访问表的操作.
where子句中, 如果索引列所对应的值的第一个字符由通配符(wildcard)开始, 索引将不被采用.
select lodging
from lodging
where manager like ‘％hanman';
在这种情况下，oracle将使用全表扫描.
基础表(driving table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, sql语句中基础表的选择是不一样的.
如果你使用的是cbo (cost based optimizer),优化器会检查sql语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.
如果你用rbo (rule based optimizer) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是from 子句中列在最后的那个表.
举例:
select a.name , b.manager
from worker a,
lodging b
where a.lodging = b.loding;
由于lodging表的loding列上有一个索引, 而且worker表中没有相比较的索引, worker表将被作为查询中的基础表.
28. 多个平等的索引
当sql语句的执行路径可以使用分布在多个表上的多个索引时, oracle会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.
在oracle选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有当where子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的.
如果不同表中两个想同等级的索引将被引用, from子句中表的顺序将决定哪个会被率先使用. from子句中最后的表的索引将有最高的优先级.
如果相同表中两个想同等级的索引将被引用, where子句中最先被引用的索引将有最高的优先级.
举例:
deptno上有一个非唯一性索引,emp_cat也有一个非唯一性索引.
select ename,
from emp
where dept_no = 20
and emp_cat = ‘a';
这里,deptno索引将被最先检索,然后同emp_cat索引检索出的记录进行合并. 执行路径如下:
table access by rowid on emp
and-equal
index range scan on dept_idx
index range scan on cat_idx
29. 等式比较和范围比较
当where子句中有索引列, oracle不能合并它们,oracle将用范围比较.
举例:
deptno上有一个非唯一性索引,emp_cat也有一个非唯一性索引.
select ename
from emp
where deptno > 20
and emp_cat = ‘a';
这里只有emp_cat索引被用到,然后所有的记录将逐条与deptno条件进行比较. 执行路径如下:
table access by rowid on emp
index range scan on cat_idx
30. 不明确的索引等级
当oracle无法判断索引的等级高低差别,优化器将只使用一个索引,它就是在where子句中被列在最前面的.
举例:
deptno上有一个非唯一性索引,emp_cat也有一个非唯一性索引.
select ename
from emp
where deptno > 20
and emp_cat > ‘a';
这里, oracle只用到了dept_no索引. 执行路径如下:
table access by rowid on emp
index range scan on dept_idx
译者按: 我们来试一下以下这种情况:
sql> select index_name, uniqueness from user_indexes where table_name = 'emp';
index_name uniquenes
------------------------------ ---------
empno unique
emptype nonunique
sql> select * from emp where empno >= 2 and emp_type = 'a' ;
no rows selected
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (by index rowid) of 'emp'
2 1 index (range scan) of 'emptype' (non-unique)
虽然empno是唯一性索引,但是由于它所做的是范围比较, 等级要比非唯一性索引的等式比较低!
关键字 本文所属关键字
相关 与本文相关文章
分类 所有文章关键字导航
源码编程相关
操作系统/服务器相关
标准 网站致力的规范
