MYSQL编程：ORACLE优化器
也许最好的策略是以不变应万变：给客户他们所需要的，不多也不少。如果MySQL学习教程适合他们，他们就不应该买别的工具。事实上，云计算产业一直推崇自助服务，但提供这些服务的公司已经开始认识到解决方案提供商推销他们商品的价值。oracle|优化ORACLE的优化器共有3种:a.RULE(基于划定规矩)b.COST(基于本钱)c.CHOOSE(选择性)
设置缺省的优化器,能够经由过程对init.ora文件中OPTIMIZER_MODE参数的各类声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.你固然也在SQL句级或是会话(session)级对其举行掩盖.
为了利用基于本钱的优化器(CBO,Cost-BasedOptimizer),你必需常常运转analyze命令,以增添数据库中的工具统计信息(objectstatistics)的正确性.
假如数据库的优化器形式设置为选择性(CHOOSE),那末实践的优化器形式将和是不是运转过analyze命令有关.假如table已被analyze过,优化器形式将主动成为CBO,反之,数据库将接纳RULE情势的优化器.
在缺省情形下,ORACLE接纳CHOOSE优化器,为了不那些不用要的全表扫描(fulltablescan),你必需只管制止利用CHOOSE优化器,而间接接纳基于划定规矩大概基于本钱的优化器.
2.会见Table的体例
ORACLE接纳两种会见表中纪录的体例:
a.全表扫描
全表扫描就是按次地会见表中每笔记录.ORACLE接纳一次读进多个数据块(databaseblock)的体例优化全表扫描.
b.经由过程ROWID会见表
你能够接纳基于ROWID的会见体例情形,进步会见表的效力,,ROWID包括了表中纪录的物理地位信息..ORACLE接纳索引(INDEX)完成了数据和寄存数据的物理地位(ROWID)之间的接洽.一般索引供应了疾速会见ROWID的办法,因而那些基于索引列的查询就能够失掉功能上的进步.
3.共享SQL语句
为了不反复剖析不异的SQL语句,在第一次剖析以后,ORACLE将SQL语句寄存在内存中.这块位于体系全局地区SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存能够被一切的数据库用户共享.因而,当你实行一个SQL语句(偶然被称为一个游标)时,假如它
和之前的实行过的语句完整不异,ORACLE就可以很快取得已被剖析的语句和最好的
实行路径.ORACLE的这个功效年夜年夜地进步了SQL的实行功能并节俭了内存的利用.
惋惜的是ORACLE只对复杂的表供应高速缓冲(cachebuffering),这个功效其实不合用于多表毗连查询.
数据库办理员必需在init.ora中为这个地区设置符合的参数,当这个内存地区越年夜,就能够保存更多的语句,固然被共享的大概性也就越年夜了.
当你向ORACLE提交一个SQL语句,ORACLE会起首在这块内存中查找不异的语句.
这里必要说明的是,ORACLE对二者接纳的是一种严厉婚配,要告竣共享,SQL语句必需
完整不异(包含空格,换行等).
共享的语句必需满意三个前提:
A.字符级的对照:
以后被实行的语句和共享池中的语句必需完整不异.
比方:
SELECT*FROMEMP;
和以下每个都分歧
SELECT*fromEMP;
Select*FromEmp;
SELECT*FROMEMP;
B.两个语句所指的工具必需完整不异:
比方:
用户工具名怎样会见
Jacksal_limitprivatesynonym
Work_citypublicsynonym
Plant_detailpublicsynonym
Jillsal_limitprivatesynonym
Work_citypublicsynonym
Plant_detailtableowner
思索一下以下SQL语句可否在这两个用户之间共享.
SQL
可否共享
缘故原由
selectmax(sal_cap)fromsal_limit;
不克不及
每一个用户都有一个privatesynonym-sal_limit,它们是分歧的工具
selectcount(*0fromwork_citywheresdesclikeNEW%;
能
两个用户会见不异的工具publicsynonym-work_city
selecta.sdesc,b.locationfromwork_citya,plant_detailbwherea.city_id=b.city_id
不克不及
用户jack经由过程privatesynonym会见plant_detail而jill是表的一切者,工具分歧.
C.两个SQL语句中必需利用不异的名字的绑定变量(bindvariables)
比方：
第一组的两个SQL语句是不异的(能够共享),而第二组中的两个语句是分歧的(即便在运转时,赋于分歧的绑定变量不异的值)
a.
selectpin,namefrompeoplewherepin=:blk1.pin;
selectpin,namefrompeoplewherepin=:blk1.pin;
b.
selectpin,namefrompeoplewherepin=:blk1.ot_ind;
selectpin,namefrompeoplewherepin=:blk1.ov_ind;
4.选择最无效率的表名按次(只在基于划定规矩的优化器中无效)
ORACLE的剖析器依照从右到左的按次处置FROM子句中的表名,因而FROM子句中写在最初的表(基本表drivingtable)将被开始处置.在FROM子句中包括多个表的情形下,你必需选择纪录条数起码的表作为基本表.当ORACLE处置多个表时,会使用排序及兼并的体例毗连它们.起首,扫描第一个表(FROM子句中最初的谁人表)并对纪录举行派序,然后扫描第二个表(FROM子句中最初第二个表),最初将一切从第二个表中检索出的纪录与第一个表中符合纪录举行兼并.
比方:
表TAB116,384笔记录
表TAB21笔记录
选择TAB2作为基本表(最好的办法)
selectcount(*)fromtab1,tab2实行工夫0.96秒
选择TAB2作为基本表(欠安的办法)
selectcount(*)fromtab2,tab1实行工夫26.09秒
假如有3个以上的表毗连查询,那就必要选择交织表(intersectiontable)作为基本表,交织表是指谁人被其他表所援用的表.
比方:
EMP表形貌了LOCATION表和CATEGORY表的交集.
SELECT*
FROMLOCATIONL,
CATEGORYC,
EMPE
WHEREE.EMP_NOBETWEEN1000AND2000
ANDE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
将比以下SQL更无效率
SELECT*
FROMEMPE,
LOCATIONL,
CATEGORYC
WHEREE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
ANDE.EMP_NOBETWEEN1000AND2000
5.WHERE子句中的毗连按次．
ORACLE接纳自下而上的按次剖析WHERE子句,依据这个道理,表之间的毗连必需写在其他WHERE前提之前,那些能够过滤失落最年夜数目纪录的前提必需写在WHERE子句的开端.
比方:
(低效,实行工夫156.3秒)
SELECT…
FROMEMPE
WHERESAL>50000
ANDJOB=‘MANAGER’
AND25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO);
(高效,实行工夫10.6秒)
SELECT…
FROMEMPE
WHERE25<(SELECTCOUNT(*)FROMEMP
WHEREMGR=E.EMPNO)
ANDSAL>50000
ANDJOB=‘MANAGER’;
6.SELECT子句中制止利用‘*‘
当你想在SELECT子句中列出一切的COLUMN时,利用静态SQL列援用‘*’是一个便利的办法.不幸的是,这是一个十分低效的办法.实践上,ORACLE在剖析的过程当中,会将’*’顺次转换成一切的列名,这个事情是经由过程查询数据字典完成的,这意味着将泯灭更多的工夫.
7.削减会见数据库的次数
当实行每条SQL语句时,ORACLE在外部实行了很多事情:剖析SQL语句,预算索引的使用率,绑定变量,读数据块等等.因而可知,削减会见数据库的次数,就可以实践上削减ORACLE的事情量.
比方,
以下有三种办法能够检索出雇员号即是0342或0291的人员.
办法1(最低效)
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=342;
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=291;
办法2(次低效)
DECLARE
CURSORC1(E_NONUMBER)IS
SELECTEMP_NAME,SALARY,GRADE
FROMEMP
WHEREEMP_NO=E_NO;
BEGIN
OPENC1(342);
FETCHC1INTO…,..,..;
…..
OPENC1(291);
FETCHC1INTO…,..,..;
CLOSEC1;
END;
办法3(高效)
SELECTA.EMP_NAME,A.SALARY,A.GRADE,
B.EMP_NAME,B.SALARY,B.GRADE
FROMEMPA,EMPB
WHEREA.EMP_NO=342
ANDB.EMP_NO=291;
注重:
在SQL*Plus,SQL*Forms和Pro*C中从头设置ARRAYSIZE参数,能够增添每次数据库会见的检索数据量,倡议值为200
8.利用DECODE函数来削减处置工夫
利用DECODE函数能够制止反复扫描不异纪录或反复毗连不异的表.
比方:
SELECTCOUNT(*)，SUM(SAL)
FROM EMP
WHEREDEPT_NO=0020
ANDENAMELIKE ‘SMITH%’;
SELECTCOUNT(*)，SUM(SAL)
FROM EMP
WHEREDEPT_NO=0030
ANDENAMELIKE ‘SMITH%’;
你能够用DECODE函数高效地失掉不异了局
SELECTCOUNT(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
FROMEMPWHEREENAMELIKE‘SMITH%’;
相似的,DECODE函数也能够使用于GROUPBY和ORDERBY子句中.
9.整合复杂,有关联的数据库会见
假如你有几个复杂的数据库查询语句,你能够把它们整合到一个查询中(即便它们之间没有干系)
比方:
SELECTNAME
FROMEMP
WHEREEMP_NO=1234;
SELECTNAME
FROMDPT
WHEREDPT_NO=10;
SELECTNAME
FROMCAT
WHERECAT_TYPE=‘RD’;
下面的3个查询能够被兼并成一个:
SELECTE.NAME,D.NAME,C.NAME
FROMCATC,DPTD,EMPE,DUALX
WHERENVL(‘X’,X.DUMMY)=NVL(‘X’,E.ROWID(+))
ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,D.ROWID(+))
ANDNVL(‘X’,X.DUMMY)=NVL(‘X’,C.ROWID(+))
ANDE.EMP_NO(+)=1234
ANDD.DEPT_NO(+)=10
ANDC.CAT_TYPE(+)=‘RD’;
(译者按:固然接纳这类办法,效力失掉进步,可是程序的可读性年夜年夜下降,以是读者仍是要衡量之间的利害)
10.删除反复纪录
最高效的删除反复纪录办法(由于利用了ROWID)
DELETEFROMEMPE
WHEREE.ROWID>(SELECTMIN(X.ROWID)
FROMEMPX
WHEREX.EMP_NO=E.EMP_NO);
11.用TRUNCATE替换DELETE
当删除表中的纪录时,在一般情形下,回滚段(rollbacksegments)用来寄存能够被恢复的信息.假如你没有COMMIT事件,ORACLE会将数据恢复到删除之前的形态(正确地说是
恢复到实行删除命令之前的情况)
而当使用TRUNCATE时,回滚段不再寄存任何可被恢复的信息.当命令运转后,数据不克不及被恢复.因而很少的资本被挪用,实行工夫也会很短.
(译者按:TRUNCATE只在删除全表合用,TRUNCATE是DDL不是DML)
12.只管多利用COMMIT
只需有大概,在程序中只管多利用COMMIT,如许程序的功能失掉进步,需求也会由于COMMIT所开释的资本而削减:
COMMIT所开释的资本:
a.回滚段上用于恢单数据的信息.
b.被程序语句取得的锁
c.redologbuffer中的空间
d.ORACLE为办理上述3种资本中的外部消费
(译者按:在利用COMMIT时必需要注重到事件的完全性,实际中效力和事件完全性常常是鱼和熊掌不成得兼)
13.盘算纪录条数
和一样平常的概念相反,count(*)比count(1)稍快,固然假如能够经由过程索引检索,对索引列的计数仍然是最快的.比方COUNT(EMPNO)
(译者按:在CSDN论坛中,已经对此有过相称强烈热闹的会商,作者的概念其实不非常正确,经由过程实践的测试,上述三种办法并没有明显的功能不同)
14.用Where子句交换HAVING子句
制止利用HAVING子句,HAVING只会在检索出一切纪录以后才对了局集举行过滤.这个处置必要排序,总计等操纵.假如能经由过程WHERE子句限定纪录的数量,那就可以削减这方面的开支.
比方:
低效:
SELECTREGION，AVG(LOG_SIZE)
FROMLOCATION
GROUPBYREGION
HAVINGREGIONREGION!=‘SYDNEY’
ANDREGION!=‘PERTH’
高效
SELECTREGION，AVG(LOG_SIZE)
FROMLOCATION
WHEREREGIONREGION!=‘SYDNEY’
ANDREGION!=‘PERTH’
GROUPBYREGION
(译者按:HAVING中的前提一样平常用于对一些汇合函数的对照,如COUNT()等等.除此而外,一样平常的前提应当写在WHERE子句中)
15.削减对表的查询
在含有子查询的SQL语句中,要出格注重削减对表的查询.
比方:
低效
SELECTTAB_NAME
FROMTABLES
WHERETAB_NAME=(SELECTTAB_NAME
FROMTAB_COLUMNS
WHEREVERSION=604)
AND DB_VER=(SELECTDB_VER
FROMTAB_COLUMNS
WHEREVERSION=604)
高效
SELECTTAB_NAME
FROMTABLES
WHERE(TAB_NAME,DB_VER)
=(SELECTTAB_NAME,DB_VER)
FROMTAB_COLUMNS
WHEREVERSION=604)
Update多个Column例子:
低效:
UPDATEEMP
SETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),
SAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
高效:
UPDATEEMP
SET(EMP_CAT,SAL_RANGE)
=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)
FROMEMP_CATEGORIES)
WHEREEMP_DEPT=0020;
16.经由过程外部函数进步SQL效力.
SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROMHISTORY_TYPET,EMPE,EMP_HISTORYH
WHEREH.EMPNO=E.EMPNO
ANDH.HIST_TYPE=T.HIST_TYPE
GROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
经由过程挪用上面的函数能够进步效力.
FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2
AS
TDESCVARCHAR2(30);
CURSORC1IS
SELECTTYPE_DESC
FROMHISTORY_TYPE
WHEREHIST_TYPE=TYP;
BEGIN
OPENC1;
FETCHC1INTOTDESC;
CLOSEC1;
RETURN(NVL(TDESC,’?’));
END;
FUNCTIONLOOKUP_EMP(EMPINNUMBER)RETURNVARCHAR2
AS
ENAMEVARCHAR2(30);
CURSORC1IS
SELECTENAME
FROMEMP
WHEREEMPNO=EMP;
BEGIN
OPENC1;
FETCHC1INTOENAME;
CLOSEC1;
RETURN(NVL(ENAME,’?’));
END;
SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROMEMP_HISTORYH
GROUPBYH.EMPNO,H.HIST_TYPE;
(译者按:常常在论坛中看到如’能不克不及用一个SQL写出….’的贴子,却不知庞大的SQL常常就义了实行效力.可以把握下面的使用函数办理成绩的办法在实践事情中长短常成心义的)
17.利用表的别号(Alias)
当在SQL语句中毗连多个表时,请利用表的别号并把别号前缀于每一个Column上.如许一来,就能够削减剖析的工夫并削减那些由Column歧义引发的语法毛病.
(译者注:Column歧义指的是因为SQL中分歧的表具有不异的Column名,当SQL语句中呈现这个Column时,SQL剖析器没法判别这个Column的回属)
18.用EXISTS替换IN
在很多基于基本表的查询中,为了满意一个前提,常常必要对另外一个表举行连接.在这类情形下,利用EXISTS(或NOTEXISTS)一般将进步查询的效力.
低效:
SELECT*
FROMEMP(基本表)
WHEREEMPNO>0
ANDDEPTNOIN(SELECTDEPTNO
FROMDEPT
WHERELOC=‘MELB’)
高效:
SELECT*
FROMEMP(基本表)
WHEREEMPNO>0
ANDEXISTS(SELECT‘X’
FROMDEPT
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB’)
(译者按:绝对来讲,用NOTEXISTS交换NOTIN将更明显地进步效力,下一节中将指出)
19.用NOTEXISTS替换NOTIN
在子查询中,NOTIN子句将实行一个外部的排序和兼并.不管在哪一种情形下,NOTIN都是最低效的(由于它对子查询中的表实行了一个全表遍历).为了不利用NOTIN,我们能够把它改写成外毗连(OuterJoins)或NOTEXISTS.
比方:
SELECT…
FROMEMP
WHEREDEPT_NONOTIN(SELECTDEPT_NO
FROMDEPT
WHEREDEPT_CAT=’A’);
为了进步效力.改写为:
(办法一:高效)
SELECT….
FROMEMPA,DEPTB
WHEREA.DEPT_NO=B.DEPT(+)
ANDB.DEPT_NOISNULL
ANDB.DEPT_CAT(+)=‘A’
(办法二:最高效)
SELECT….
FROMEMPE
WHERENOTEXISTS(SELECT‘X’
FROMDEPTD
WHERED.DEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A’);
20.用表毗连交换EXISTS
一般来讲,接纳表毗连的体例比EXISTS更无效率
SELECTENAME
FROMEMPE
WHEREEXISTS(SELECT‘X’
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A’);
(更高效)
SELECTENAME
FROMDEPTD,EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A’;
(译者按:在RBO的情形下,前者的实行路径包含FILTER,后者利用NESTEDLOOP)
21.用EXISTS交换DISTINCT
当提交一个包括一对多表信息(好比部门表和雇员表)的查询时,制止在SELECT子句中利用DISTINCT.一样平常能够思索用EXIST交换
比方:
低效:
SELECTDISTINCTDEPT_NO,DEPT_NAME
FROMDEPTD,EMPE
WHERED.DEPT_NO=E.DEPT_NO
高效:
SELECTDEPT_NO,DEPT_NAME
FROMDEPTD
WHEREEXISTS(SELECT‘X’
FROMEMPE
WHEREE.DEPT_NO=D.DEPT_NO);
EXISTS使查询更加敏捷,由于RDBMS中心模块将在子查询的前提一旦满意后,立即前往了局.
22.辨认’低效实行’的SQL语句
用以下SQL工具找出低效SQL:
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC;
(译者按:固然今朝各类关于SQL优化的图形化工具层见叠出,可是写出本人的SQL工具来办理成绩一直是一个最好的办法)
23.利用TKPROF工具来查询SQL功能形态
SQLtrace工具搜集正在实行的SQL的功能形态数据并纪录到一个跟踪文件中.这个跟踪文件供应了很多有效的信息,比方剖析次数.实行次数,CPU利用工夫等.这些数据将能够用来优化你的体系.
设置SQLTRACE在会话级别:无效
ALTERSESSIONSETSQL_TRACETRUE
设置SQLTRACE在全部数据库无效仿,你必需将SQL_TRACE参数在init.ora中设为TRUE,USER_DUMP_DEST参数申明了天生跟踪文件的目次
(译者按:这一节中,作者并没有提到TKPROF的用法,对SQLTRACE的用法也不敷正确,设置SQLTRACE起首要在init.ora中设定TIMED_STATISTICS,如许才干失掉那些主要的工夫形态.天生的trace文件是不成读的,以是要用TKPROF工具对其举行转换,TKPROF有很多实行参数.人人能够参考ORACLE手册来懂得详细的设置.)
24.用EXPLAINPLAN剖析SQL语句
EXPLAINPLAN是一个很好的剖析SQL语句的工具,它乃至能够在不实行SQL的情形下剖析语句.经由过程剖析,我们就能够晓得ORACLE是怎样毗连表,利用甚么体例扫描表(索引扫描或全表扫描)和利用到的索引称号.
你必要依照从里到外,从上到下的序次解读剖析的了局.EXPLAINPLAN剖析的了局是用缩进的格局分列的,最外部的操纵将被开始解读,假如两个操纵处于统一层中,带有最小操纵号的将被起首实行.
NESTEDLOOP是多数不依照上述划定规矩处置的操纵,准确的实行路径是反省对NESTEDLOOP供应数据的操纵,个中操纵号最小的将被开始处置.
译者按:
经由过程理论,感应仍是用SQLPLUS中的SETTRACE功效对照便利.
举例:
SQL>list
1SELECT*
2FROMdept,emp
3*WHEREemp.deptno=dept.deptno
SQL>setautotracetraceonly/*traceonly能够不显现实行了局*/
SQL>/
14rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10NESTEDLOOPS
21TABLEACCESS(FULL)OFEMP
31TABLEACCESS(BYINDEXROWID)OFDEPT
43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
2dbblockgets
30consistentgets
0physicalreads
0redosize
2598bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
14rowsprocessed
经由过程以上剖析,能够得出实践的实行步骤是:
1.TABLEACCESS(FULL)OFEMP
2.INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)
3.TABLEACCESS(BYINDEXROWID)OFDEPT
4.NESTEDLOOPS(JOINING1AND3)
注:今朝很多第三方的工具如TOAD和ORACLE自己供应的工具如OMS的SQLAnalyze都供应了极为便利的EXPLAINPLAN工具.大概喜好图形化界面的伴侣们能够选用它们.
25.用索引进步效力
索引是表的一个观点部分,用来进步检索数据的效力.实践上,ORACLE利用了一个庞大的自均衡B-tree布局.一般,经由过程索引查询数据比全表扫描要快.当ORACLE找出实行查询和Update语句的最好路径时,ORACLE优化器将利用索引.一样在联合多个表时利用索引也能够进步效力.另外一个利用索引的优点是,它供应了主键(primarykey)的独一性考证.
除那些LONG或LONGRAW数据范例,你能够索引几近一切的列.一般,在年夜型表中利用索引出格无效.固然,你也会发明,在扫描小表时,利用索引一样能进步效力.
固然利用索引能失掉查询效力的进步,可是我们也必需注重到它的价值.索引必要空间来
存储,也必要按期保护,每当有纪录在表中增减或索引列被修正时,索引自己也会被修正.这意味着每笔记录的INSERT,DELETE,UPDATE将为此多支付4,5次的磁盘I/O.由于索引必要分外的存储空间和处置,那些不用要的索引反而会使查询反响工夫变慢.
译者按:
按期的重构索引是有需要的.
ALTERINDEX<INDEXNAME>REBUILD<TABLESPACENAME>
26.索引的操纵
ORACLE对索引有两种会见形式.
索引独一扫描(INDEXUNIQUESCAN)
年夜多半情形下,优化器经由过程WHERE子句会见INDEX.
比方:
表LODGING有两个索引:创建在LODGING列上的独一性索引LODGING_PK和创建在MANAGER列上的非独一性索引LODGING$MANAGER.
SELECT*
FROMLODGING
WHERELODGING=‘ROSEHILL’;
在外部,上述SQL将被分红两步实行,起首,LODGING_PK索引将经由过程索引独一扫描的体例被会见,取得绝对应的ROWID,经由过程ROWID会见表的体例实行下一步检索.
假如被检索前往的列包含在INDEX列中,ORACLE将不实行第二步的处置(经由过程ROWID会见表).由于检索数据保留在索引中,单单会见索引就能够完整满意查询了局.
上面SQL只必要INDEXUNIQUESCAN操纵.
SELECTLODGING
FROMLODGING
WHERELODGING=‘ROSEHILL’;
索引局限查询(INDEXRANGESCAN)
合用于两种情形:
1.基于一个局限的检索
2.基于非独一性索引的检索
例1:
SELECTLODGING
FROMLODGING
WHERELODGINGLIKE‘M%’;
WHERE子句前提包含一系列值,ORACLE将经由过程索引局限查询的体例查询LODGING_PK.因为索引局限查询将前往一组值,它的效力就要比索引独一扫描
低一些.
例2:
SELECTLODGING
FROMLODGING
WHEREMANAGER=‘BILLGATES’;
这个SQL的实行分两步,LODGING$MANAGER的索引局限查询(失掉一切切合前提纪录的ROWID)和下一步同过ROWID会见表失掉LODGING列的值.因为LODGING$MANAGER是一个非独一性的索引,数据库不克不及对它实行索引独一扫描.
因为SQL前往LODGING列,而它其实不存在于LODGING$MANAGER索引中,以是在索引局限查询后会实行一个经由过程ROWID会见表的操纵.
WHERE子句中,假如索引列所对应的值的第一个字符由通配符(WILDCARD)入手下手,索引将不被接纳.
SELECTLODGING
FROMLODGING
WHEREMANAGERLIKE‘％HANMAN’;
在这类情形下，ORACLE将利用全表扫描.
27.基本表的选择
基本表(DrivingTable)是指被开始会见的表(一般以全表扫描的体例被会见).依据优化器的分歧,SQL语句中基本表的选择是纷歧样的.
假如你利用的是CBO(COSTBASEDOPTIMIZER),优化器会反省SQL语句中的每一个表的物理巨细,索引的形态,然后选用消费最低的实行路径.
假如你用RBO(RULEBASEDOPTIMIZER),而且一切的毗连前提都有索引对应,在这类情形下,基本表就是FROM子句中列在最初的谁人表.
举例:
SELECTA.NAME,B.MANAGER
FROM WORKERA,
LODGINGB
WHERE A.LODGING=B.LODING;
因为LODGING表的LODING列上有一个索引,并且WORKER表中没有比拟较的索引,WORKER表将被作为查询中的基本表.
28.多个同等的索引
当SQL语句的实行路径可使用散布在多个表上的多个索引时,ORACLE会同时利用多个索引并在运转时对它们的纪录举行兼并,检索出仅对全体索引无效的纪录.
在ORACLE选择实行路径时,独一性索引的品级高于非独一性索引.但是这个划定规矩只要
当WHERE子句中索引列和常量对照才无效.假如索引列和其他表的索引类比拟较.这类子句在优化器中的品级长短常低的.
假如分歧表中两个想一律级的索引将被援用,FROM子句中表的按次将决意哪一个会被领先利用.FROM子句中最初的表的索引将有最高的优先级.
假如不异表中两个想一律级的索引将被援用,WHERE子句中开始被援用的索引将有最高的优先级.
举例:
DEPTNO上有一个非独一性索引,EMP_CAT也有一个非独一性索引.
SELECTENAME,
FROMEMP
WHEREDEPT_NO=20
ANDEMP_CAT=‘A’;
这里,DEPTNO索引将被开始检索,然后同EMP_CAT索引检索出的纪录举行兼并.实行路径以下:
TABLEACCESSBYROWIDONEMP
AND-EQUAL
INDEXRANGESCANONDEPT_IDX
INDEXRANGESCANONCAT_IDX
29.等式对照和局限对照
当WHERE子句中有索引列,ORACLE不克不及兼并它们,ORACLE将用局限对照.
举例:
DEPTNO上有一个非独一性索引,EMP_CAT也有一个非独一性索引.
SELECTENAME
FROMEMP
WHEREDEPTNO>20
ANDEMP_CAT=‘A’;
这里只要EMP_CAT索引被用到,然后一切的纪录将逐条与DEPTNO前提举行对照.实行路径以下:
TABLEACCESSBYROWIDONEMP
INDEXRANGESCANONCAT_IDX
30.不明白的索引品级
当ORACLE没法判别索引的品级上下不同,优化器将只利用一个索引,它就是在WHERE子句中被列在最后面的.
举例:
DEPTNO上有一个非独一性索引,EMP_CAT也有一个非独一性索引.
SELECTENAME
FROMEMP
WHEREDEPTNO>20
ANDEMP_CAT>‘A’;
这里,ORACLE只用到了DEPT_NO索引.实行路径以下:
TABLEACCESSBYROWIDONEMP
INDEXRANGESCANONDEPT_IDX
译者按:
我们来试一下以下这类情形:
SQL>selectindex_name,uniquenessfromuser_indexeswheretable_name=EMP;
INDEX_NAMEUNIQUENES
---------------------------------------
EMPNOUNIQUE
EMPTYPENONUNIQUE
SQL>select*fromempwhereempno>=2andemp_type=A;
norowsselected
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS(BYINDEXROWID)OFEMP
21INDEX(RANGESCAN)OFEMPTYPE(NON-UNIQUE)
固然EMPNO是独一性索引,可是因为它所做的是局限对照,品级要比非独一性索引的等式对照低
需要处理因此带来的更多的支持工作，这有可能会带来成本上的提高。在这种情况下，一些MySQL学习教程发行商可能倾向于选择别的开源数据库，例如遵循BSD授权的PostgreSQL。 我们学到了什么？思考问题的时候从表的角度来思考问 同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。 但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右） 至于淘汰的问题，只能说在你的项目周期之内，微软应该都不会倒闭。 Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。 一个是把SQL语句写到客户端,可以使用DataSet进行加工; 如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。 其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！
页:
[1]