快捷搜索:  as  2018  FtCWSyGV  С˵  test  xxx  Ψһ  w3viyKQx

和记娱乐的网址_博格自动化网进入



1.选用得当的Oracle优化器

Oracle的优化器共有3种:

a.RULE(基于规则)

b.COST(基于资源)

c.CHOOSE(选择性)

设置缺省的优化器,可以经由过程对init.ora文件中OPTIMIZER_MODE参数的各类声明,如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。你当然也在SQL句级或是会话(session)级对其进行覆盖。

为了应用基于资源的优化器(CBO,Cost-Based Optimizer),你必须常常运行analyze敕令,以增添数据库中的工具统计信息(object statistics)的准确性。

假如数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze敕令有关。假如table已经被analyze过,优化器模式将自动成为CBO,反之,数据库将采纳RULE形式的优化器。

在缺省环境下,Oracle采纳CHOOSE优化器,为了避免那些不需要的全表扫描(full table scan),你必须只管即便避免应用CHOOSE优化器,而直接采纳基于规则或者基于资源的优化器。

2.造访Table的要领Oracle采纳两种造访表中记录的要领:

a.全表扫描

全表扫描便是顺序地造访表中每笔记录。O和记娱乐的网址racle采纳一次读入多个数据块(database block)的要领优化全表扫描。

b. 经由过程ROWID造访表

你可以采纳基于ROWID的造访要领环境,前进造访表的效率,ROWID包孕了表中记录的物理位置信息……Oracle采纳索引(INDEX)实现了数据和寄放数据的物理位置(ROWID)之间的联系。平日索引供给了快速造访ROWID的措施,是以那些基于索引列的查询就可以获得机能上的前进。

3.共享SQL语句

为了不重复解析相同的SQL语句,在第一次解析之后,Oracle将SQL语句寄放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。是以,当你履行一个SQL语句(无意偶尔被称为一个游标)时,假如它和之前的履行过的语句完全相同,Oracle就能很快得到已经被解析的语句以及最好的履行路径。Oracle的这个功能大年夜大年夜地前进了SQL的履行机能并节省了内存的应用。

可惜的是Oracle只对简单的表供给高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询。

数据库治理员必须在init.ora中为这个区域设置相宜的参数,当这个内存区域越大年夜,就可以保留更多的语句,当然被共享的可能性也就越大年夜了。

当你向Oracle提交一个SQL语句,Oracle会首先在这块内存中查找相同的语句。

这里必要注明的是,Oracle对两者采取的是一种严格匹配,要杀青共享,SQL语句必须完全相同(包括空格,换行等)。

共享的语句必须满意三个前提:

A.字符级的对照:

当前被履行的语句和共享池中的语句必须完全相同。

例如:

SELECT * FROM EMP;

和下列每一个都不合

SELECT * from EMP;

Select * From Emp;

SELECT * FROM EMP;

B.两个语句所指的工具必须完全相同:

例如:

用户工签字若何造访

Jack sal_limit private synonym

Work_city public synonym

Plant_detail public synonym

Jill sal_limit private synonym

Work_city public synonym

Plant_detail table owner

斟酌一下下列SQL语句能否在这两个用户之间共享。

SQL能否共享缘故原由

select max(sal_cap) from sal_limit;不能每个用户都有一个private synonym - sal_limit,它们是不合的工具

select count(*0 from work_city where sdesc like 'NEW%';能两个用户造访相同的工具public synonym - work_city

select a.sdesc,b.location from work_city a,plant_detail b where a.city_id = b.city_id不能用户jack经由过程private synonym造访plant_detail而jill是表的所有者,工具不合。

C.两个SQL语句中必须应用相同的名字的绑定变量(bind variables)

例如:第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不合的(纵然在运行时,赋于不合的绑定变量相同的值)

a.

select pin ,name from people where pin = :blk1.pin;

select pin ,name from people where pin =和记娱乐的网址 :blk1.pin;

b.

select pin ,name from people where pin = :blk1.ot_ind;

select pin ,name from people where pin = :blk1.ov_ind;

4.选择最有效率的表名顺序(只在基于规则的优化器中有效)

Oracle的解析器按照从右到左的顺序处置惩罚FROM子句中的表名,是以FROM子句中写在着末的表(根基表 driving table)将被最先处置惩罚。在FROM子句中包孕多个表的环境下,你必须选择记录条数起码的表作为根基表。当Oracle处置惩罚多个表时,会运用排序及合并的要领连接它们。首先,扫描第一个表(FROM子句中着末的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中着末第二个表),着末将所有从第二个表中检索出的记录与第一个表中相宜记录进行合并。

例如:

表TAB1 16,384 笔记录

表TAB2 1笔记录

选择TAB2作为根基表(最好的措施)

select count(*) from tab1,tab2 履行光阴0.96秒

选择TAB2作为根基表(不佳的措施)

select count(*) from tab2,tab1 履行光阴26.09秒

假如有3个以上的表连接查询,那就必要选和记娱乐的网址择交叉表(intersection table)作为根基表,交叉表是指那个被其他表所引用的表。

例如:EMP表描述了LOCATION表和CATEGORY表的交集。

SELECT *

FROM LOCATION L ,

CATEGORY C,

EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

将比下列SQL更有效率

SELECT *

FROM EMP E ,

LOCATION L ,

CATEGORY C

WHERE E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

5. WHERE子句中的连接顺序

Oracle采纳自下而上的顺序解析WHERE子句,根据这个道理,表之间的连接必须写在其他WHERE前提之前,那些可以过滤掉落最大年夜数量记录的前提必须写在WHERE子句的末端。

例如:

(低效,履行光阴156.3秒)

SELECT …

FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER’

AND 25

(高效,履行光阴10.6秒)

SELECT …

FROM EMP E

WHERE 2550000

AND JOB = ‘MANAGER’;

6.SELECT子句中避免应用‘ * ’

当你想在SELECT子句中列出所有的COLUMN时,应用动态SQL列引用‘*’是一个方便的措施。不幸的是,这是一个异常低效的措施。实际上,Oracle在解析的历程中,会将‘*’依次转换成所有的列名,这个事情是经由过程查询数据字典完成的,这意味着将消费更多的光阴。

7.削减造访数据库的次数

当履行每条SQL语句时,Oracle在内部履行了许多事情:解析SQL语句,估算索引的使用率,绑定变量,读数据块等等。由此可见,和记娱乐的网址削减造访数据库的次数,就能实际上削减Oracle的事情量。

例如,以下有三种措施可以检索出雇员号即是0342或0291的人员。

措施1(最低效)

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 291;

措施2(次低效)

DECLARE

CURSOR C1 (E_NO NUMBER) IS

SELECT EMP_NAME,SALARY,GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1; END;

措施3(高效)

SELECT A.EMP_NAME,A.SALARY,A.GRADE,

B.EMP_NAME, B.SALARY, B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

留意:

在SQL*Plus,SQL*Forms和Pro*C中从新设置ARRAYSIZE参数,可以增添每次数据库造访的检索数据量,建议值为200。

8.应用DECODE函数来削减处置惩罚光阴

应用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

例如:

SELECT COUNT(*),SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE‘SMITH%’;

SELECT COUNT(*),SUM(SAL)

FROM EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE‘SMITH%’;

你可以用DECODE函数高效地获得相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP WHERE ENAME LIKE‘SMITH%’;

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

9.整合简单,无关联的数据库造访

假如你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(纵然它们之间没有关系)

例如:

SELECT NAME

FROM EMP

WHERE EMP_NO = 1234;

SELECT NAME

FROM DPT

WHERE DPT_NO = 10 ;

SELECT NAME

FROM CAT

WHERE CAT_TYPE =‘RD’;

上面的3个查询可以被合并成一个:

SELECT E.NAME , D.NAME , C.NAME

FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))

AND E.EMP_NO(+) = 1234

AND D.DEPT_NO(+) = 10

AND C.CAT_TYPE(+) = ‘RD’;

(译者按:虽然采取这种措施,效率获得前进,然则法度榜样的可读性大年夜大年夜低落,以是读者照样要权衡之间的利弊)

10.删除重复记录

最高效的删除重复记录措施(由于应用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X

WHERE X.EMP_NO = E.EMP_NO);

11.用TRUNCATE替代DELETE

当删除表中的记录时,在平日环境下,回滚段(rollback segments)用来寄放可以被规复的信息。假如你没有COMMIT事务,Oracle会将数据规复到删除之前的状态(准确地说是规复到履行删除敕令之前的状况)

而当运用TRUNCATE时,回滚段不再寄放任何可被规复的信息。当敕令运行后,数据不能被规复。是以很少的资本被调用,履行光阴也会很短。

(译者按:TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

12.只管即便多应用COMMIT

只要有可能,在法度榜样中只管即便多应用COMMIT,这样法度榜样的机能获得前进,需求也会由于COMMIT所开释的资本而削减:COMMIT所开释的资本:

a.回滚段上用于规复数据的信息。

b.被法度榜样语句得到的锁

c.redo log buffer中的空间

d.Oracle为治理上述3种资本中的内部花费

(译者按:在应用COMMIT时必须要留意到事务的完备性,现实中效率和事务完备性每每是鱼和熊掌弗成得兼)

13.谋略记录条数

和一样平常的不雅点相反,count(*) 比count(1)稍快,当然假如可以经由过程索引检索,对索引列的计数仍然是最快的。例如 COUNT(EMPNO)

(译者按:在CSDN论坛中,曾经对此有过相称热烈的评论争论,作者的不雅点并不十分准确,经由过程实际的测试,上述三种措施并没有显明的机能区别)

14.用Where子句调换HAVING子句

避免应用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处置惩罚必要排序,总计等操作。假如能经由过程WHERE子句限定记录的数目,那就能削减这方面的开销。

例如:

低效:

SELECT REGION,AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION REGION !=‘SYDNEY’

AND REGION !=‘PERTH’

高效:

SELECT REGION,AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION REGION != ‘SYDNEY’

AND REGION != ‘PERTH’

GROUP BY REGION

(译者按:HAVING中的前提一样平常用于对一些聚拢函数的对照,如COUNT()等等。除此而外,一样平常的前提应该写在WHERE子句中)

15.削减对表的查询

在含有子查询的SQL语句中,要分外留意削减对表的查询。

例如:

低效

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME

FROM TAB_COLUMNS

WHERE VERSION = 604)

AND DB_VER= ( SELECT DB_VER

FROM TAB_COLUMNS

WHERE VERSION = 604)

高效

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME,DB_VER)

= ( SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS

WHERE VERSION = 604)

Update 多个Column 例子:

低效:

UPDATE EMP

SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

高效:

UPDATE EMP

SET (EMP_CAT, SAL_RANGE)

= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)

FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

16.经由过程内部函数前进SQL效率。

SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)

FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H

WHERE H.EMPNO = E.EMPNO

AND H.HIST_TYPE = T.HIST_TYPE

GROUP BY H.EMPNO,E.ENAME,和记娱乐的网址H.HIST_TYPE,T.TYPE_DESC;

经由过程调用下面的函数可以前进效率。

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2

AS

TDESC VARCHAR2(30);

CURSOR C1 IS

SELECT TYPE_DESC

FROM HISTORY_TYPE

WHERE HIST_TYPE = TYP;

BEGIN

OPEN C1;

FETCH C1 INTO TDESC;

CLOSE C1;

RETURN (NVL(TDESC,’?’));

END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2

AS

ENAME VARCHAR2(30);

CURSOR C1 IS

SELECT ENAME

FROM EMP

WHERE EMPNO=EMP;

BEGIN

OPEN C1;

FETCH C1 INTO ENAME;

CLOSE C1;

RETURN (NVL(ENAME,’?’));

END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)

FROM EMP_HISTORY H

GROUP BY H.EMPNO , H.HIST_TYPE;

(译者按:常常在论坛中看到如‘能不能用一个SQL写出…。’的贴子,殊不知繁杂的SQL每每就义了履行效率。能够掌握上面的运用函数办理问题的措施在实际事情中是异常故意义的)

17.应用表的又名(Alias)

当在SQL语句中连接多个表时,请应用表的又名并把又名前缀于每个Column上。这样一来,就可以削减解析的光阴并削减那些由Column歧义引起的语法差错。

(译者注:Column歧义指的是因为SQL中不合的表具有相同的Column名,当SQL语句中呈现这个Column时,SQL解析器无法判断这个Column的归属)

18.用EXISTS替代IN

在许多基于根基表的查询中,为了满意一个前提,每每必要对另一个表进行联接。在这种环境下,应用EXISTS(或NOT EXISTS)平日将前进查询的效率。

低效:

SELECT *

FROM EMP (根基表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC =‘MELB’)

高效:

SELECT *

FROM EMP (根基表)

WHERE EMPNO > 0

AND EXISTS (SELECT‘X’

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC =‘MELB’)

(译者按:相对来说,用NOT EXISTS调换NOT IN将更显明地前进效率,下一节中将指出)

19.用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将履行一个内部的排序和合并。无论在哪种环境下,NOT IN都是最低效的 (由于它对子查询中的表履行了一个全表遍历)。为了避免应用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

例如:

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO

FROM DEPT

WHERE DEPT_CAT=’A’);

为了前进效率。改写为:

(措施一:高效)

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO = B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+) = ‘A’

(措施二:最高效)

SELECT ….

FROM EMP E

WHERE NOT EXISTS (SELECT ‘X’

FROM DEPT D

WHERE D.DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A’);

20.用表连接调换EXISTS

平日来说,采纳表连接的要领比EXISTS更有效率

SELECT ENAME

FROM EMP E

WHERE EXISTS (SELECT ‘X’

FROM DEPT

WHERE DEPT_NO = E.DEPT_NO

AND DEPT_CAT = ‘A’);

(更高效)

SELECT ENAME

FROM DEPT D,EMP E

WHERE E.DEPT_NO = D.DEPT_NO

AND DEPT_CAT = ‘A’ ;

(译者按:在RBO的环境下,前者的履行路径包括FILTER,后者应用NESTED LOOP)

21. 用EXISTS调换DISTINCT

当提交一个包孕一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中应用DISTINCT。一样平常可以斟酌用EXIST调换

例如:

低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X’

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS使查询更为迅速,由于RDBMS核心模块将在子查询的前提一旦满意后,立即返回结果。

22.识别‘低效履行’的SQL语句

用下列SQL对象找出低效SQL:

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS

(译者按:虽然今朝各类关于SQL优化的图形化对象层出不穷,然则写出自己的SQL对象来办理问题始终是一个最好的措施)

23.应用TKPROF对象来查询SQL机能状态

SQL trace对象网络正在履行的SQL的机能状态数据并记录到一个跟踪文件中。这个跟踪文件供给了许多有用的信息,例如解析次数。履行次数,CPU应用光阴等。这些数据将可以用来优化你的系统。

设置SQL TRACE在会话级别:

有效

ALTER SESSION SET SQL_TRACE TRUE

设置SQL TRACE在全部数据库有效仿,你必须将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数阐清楚明了天生跟踪文件的目录

(译者按:这一节中,作者并没有提到TKPROF的用法,对SQL TRACE的用法也不敷准确,设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS,这样才能获得那些紧张的光阴状态。天生的trace文件是弗成读的,以是要用TKPROF对象对其进行转换,TKPROF有许多履行参数。大年夜家可以参考Oracle手册来懂得详细的设置设置设备摆设摆设。)

24.用EXPLAIN PLAN 阐发SQL语句

EXPLAIN PLAN是一个很好的阐发SQL语句的对象,它以致可以在不履行SQL的环境下阐发语句。经由过程阐发,我们就可以知道Oracle是怎么样连接表,应用什么要领扫描表(索引扫描或全表扫描)以及应用到的索引名称。

你必要按照从里到外,从上到下的序次解读阐发的结果。EXPLAIN PLAN阐发的结果是用缩进的款式排列的, 最内部的操作将被最先解读,假如两个操作处于同一层中,带有最小操作号的将被首先履行。

NESTED LOOP是少数不按照上述规则处置惩罚的操作,精确的履行路径是检查对NESTED LOOP供给数据的操作,此中操作号最小的将被最先处置惩罚。

译者按:经由过程实践,认为照样用SQLPLUS中的SET TRACE功能对照方便。

举例:

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不显示履行结果*/

SQL> /

14 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT ptimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

经由过程以上阐发,可以得出实际的履行步骤是:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

注:今朝许多第三方的对象如TOAD和Oracle本身供给的对象如OMS的SQL Analyze都供给了极其方便的EXPLAIN PLAN对象。大概爱好图形化界面的同伙们可以选用它们。

25.用索引前进效率

索引是表的一个观点部分,用来前进检索数据的效率。实际上,Oracle应用了一个繁杂的自平衡B-tree布局。平日,经由过程索引查询数据比全表扫描要快。当Oracle找出履行查询和Update语句的最佳路径时,Oracle优化器将应用索引。同样在联络多个表时应用索引也可以前进效率。另一个应用索引的好处是,它供给了主键(primary key)的独一性验证。

除了那些LONG或LONG RAW数据类型,你可以索引险些所有的列。平日,在大年夜型表中应用索引分外有效。当然,你也会发明,在扫描小表时,应用索引同样能前进效率。

虽然应用索引能获得查询效率的前进,然则我们也必须留意到它的价值。索引必要空间来存储,也必要按期掩护,每当有记录在表中增减或索引列被改动时,索引本身也会被改动。这意味着每笔记录的INSERT,DELETE,UPDATE将为此多付出4、5次的磁盘I/O。由于索引必要额外的存储空间和处置惩罚,那些不需要的索引反而会使查询反映光阴变慢。

译者按:按期的重构索引是有需要的。

ALTER INDEXREBUILD

26.索引的操作

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)开始,索引将不被采纳。在这种环境下,Oracle将应用全表扫描。

SELECT LODGING

FROM LODGING

WHERE MANAGER LIKE‘%HANMAN’;

27.根基表的选择

根基表(Driving Table)是指被最先造访的表(平日以全表扫描的要领被造访)。根据优化器的不合,SQL语句中根基表的选择是不一样的。

假如你应用的是CBO (COST BASED OPTIMIZER),优化器会反省SQL语句中的每个表的物理大年夜小,索引的状态,然后选用花费最低的履行路径。

假如你用RBO (RULE BASED OPTIMIZER),并且所有的连接前提都有索引对应,在这种环境下,根基表便是FROM 子句中列在着末的那个表。blog

举例:

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 ptimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE)

虽然EMPNO是独一性索引,然则因为它所做的是范围对照,等级要比非独一性索引的等式对照低!

您可能还会对下面的文章感兴趣: