小女巫 发表于 2015-1-16 22:42:01

MYSQL编程：Oracle常}集(三)

列举选择MySQL的理由的最困难的地方在于，如何对这些理由进行排序。MySQL学习教程这就如同我们经常争论的故事：先有鸡还是先有蛋?oracle
151.怎样O控SGA中字典n^的射中率？

selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100

"missratio",

(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"

fromv$rowcache

wheregets+getmisses0

groupbyparameter,gets,getmisses;



152.怎样O控SGA中共用^的射中率，小於1%？

selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",

sum(reloads)/sum(pins)*100libcache

fromv$librarycache;

selectsum(pinhits-reloads)/sum(pins)"hit

radio",sum(reloads)/sum(pins)"reloadpercent"

fromv$librarycache;



153.怎样@示一切Y料物件的e和巨细？

selectcount(name)num_instances,type,sum(source_size)

source_size,

sum(parsed_size)parsed_size,sum(code_size)code_size

,sum(error_size)error_size,

sum(source_size)+sum(parsed_size)+sum(code_size)

+sum(error_size)size_required

fromdba_object_size

groupbytypeorderby2;



154.O控SGA中重做日I^的射中率，小於1%

SELECTname,gets,misses,immediate_gets,immediate_misses,

Decode(gets,0,0,misses/gets*100)ratio1,

Decode(immediate_gets+immediate_misses,0,0,

immediate_misses/(immediate_gets+immediate_misses)*100)ratio2

FROMv$latchWHEREnameIN(redoallocation,redocopy);



155.O控w和硬碟的排序比率，最好使它小於.10，增添sort_area_size

SELECTname,valueFROMv$sysstatWHEREnameIN(sorts(memory),

sorts(disk));



156.怎样O控前Y料l在行什麽SQLZ句？

SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb

wherea.sql_address=b.addressorderbyaddress,piece;



157.怎样O控字典n^？

SELECT(SUM(PINS-RELOADS))/SUM(PINS)"LIBCACHE"FROM

V$LIBRARYCACHE;

SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"ROW

CACHE"FROMV$ROWCACHE;

SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILE

EXECUTING"FROMV$LIBRARYCACHE;

後者除之前者,此比率小於1%,靠近0%好。

SELECTSUM(GETS)"DICTIONARYGETS",SUM(GETMISSES)"DICTIONARY

CACHEGETMISSES"

FROMV$ROWCACHE



158.O控MTS

selectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher;

此值年夜於0.5r，敌杓哟

selectsum(wait)/sum(totalq)"dispatcherwaits"fromv$queuewhere

type=dispatcher;

selectcount(*)fromv$dispatcher;

selectservers_highwaterfromv$mts;

servers_highwater靠近mts_max_serversr，敌杓哟



159.怎样晓得前用舻ID

SQL>SHOWUSER;

OR

SQL>selectuserfromdual;



160.怎样检察碎片水平高的表

SELECTsegment_nametable_name,COUNT(*)extents

FROMdba_segmentsWHEREownerNOTIN(SYS,SYSTEM)GROUPBY

segment_name

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUP

BYsegment_name);



162.怎样晓得表在表空g中的存ηr

selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extents

where

tablespace_name=&tablespace_nameandsegment_type=TABLEgroup

bytablespace_name,segment_name;



163.怎样晓得索引在表空g中的存ηr

selectsegment_name,count(*)fromdba_extentswhere

segment_type=INDEXandowner=&owner

groupbysegment_name;



164、怎样晓得利用CPU多的用session

11是cpuusedbythissession

selecta.sid,spid,status,substr(a.program,1,40)

prog,a.terminal,osuser,value/60/100value

fromv$sessiona,v$processb,v$sesstatc

wherec.statistic#=11andc.sid=a.sidanda.paddr=b.addrorderby

valuedesc;



165.怎样晓得O器日I文件

以8I例

$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG



166.怎样晓得O器滴募

以8I例

$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA



167.怎样晓得TNSB接文件

以8I例

$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA



168.怎样晓得Sql*Neth境文件

以8I例

$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA



169.怎样晓得告诫日I文件

以8I例

$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG



170.怎样晓得基础Y

以8I例

$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL



171.怎样晓得创建Y料字典D

以8I例

$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL



172.怎样晓得创建用Y料字典D

以8I例

$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL



173.怎样晓得创建快照用Y料字典D

以8I例

$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL

本v次要v的是SQLZ句的化办法!次要基於ORACLE9I的.



174./*+ALL_ROWS*/

标明φZ句Kx窕堕_N的化办法,K@得最好吞吐量,使Y源损耗最小化.

比方:

SELECT/*+ALL+_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHERE

EMP_NO=CCBZZP;



175./*+FIRST_ROWS*/

标明φZ句Kx窕堕_N的化办法,K@得最好回rg,使Y源损耗最小化.

比方:

SELECT/*+FIRST_ROWS*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHERE

EMP_NO=CCBZZP;



176./*+CHOOSE*/

标明假如Y料字典中有L表的yY,⒒堕_N的化办法,K@得最好的吞吐量;

标明假如Y料字典中]有L表的yY,⒒兑t_N的化办法;

比方:

SELECT/*+CHOOSE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHERE

EMP_NO=CCBZZP;



177./*+RULE*/

标明φZ句Kx窕兑t的化办法.

比方:

SELECT/*+RULE*/EMP_NO,EMP_NAM,DAT_INFROMBSEMPMSWHERE

EMP_NO=CCBZZP;



178./*+FULL(TABLE)*/

标明Ρ磉x袢呙璧姆椒.

比方:

SELECT/*+FULL(A)*/EMP_NO,EMP_NAMFROMBSEMPMSAWHERE

EMP_NO=CCBZZP;



179./*+ROWID(TABLE)*/

提醒明_标明χ付ū砀ROWIDM行L.

比方:

SELECT/*+ROWID(BSEMPMS)*/*FROMBSEMPMSWHERE

ROWID>=AAAAAAAAAAAAAA

ANDEMP_NO=CCBZZP;



180./*+CLUSTER(TABLE)*/

提醒明_标明χ付ū磉x翊呙璧脑L办法,它只Υ匚锛行.

比方:

SELECT/*+CLUSTER*/BSEMPMS.EMP_NO,DPT_NOFROMBSEMPMS,BSDPTMS

WHEREDPT_NO=TEC304ANDBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;



181./*+INDEX(TABLEINDEX_NAME)*/

标明Ρ磉x袼饕呙璺椒.

比方:

SELECT/*+INDEX(BSEMPMSSEX_INDEX)USESEX_INDEXBECAUSETHEREARE

FEWMALEBSEMPMS*/FROMBSEMPMSWHERESEX=M;



182./*+INDEX_ASC(TABLEINDEX_NAME)*/

标明Ρ磉x袼饕N绲呙璺椒.

比方:

SELECT/*+INDEX_ASC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHERE

DPT_NO=CCBZZP;



183./*+INDEX_COMBINE*/

指定表x顸cDL路,假如INDEX_COMBINE中]有供应作档乃饕,⑦x癯鳇cD索引的

布林M合体例.

比方:

SELECT/*+INDEX_COMBINE(BSEMPMSSAL_BMIHIREDATE_BMI)*/*FROM

BSEMPMS

WHERESAL<5000000ANDHIREDATE<SYSDATE;<SPAN>



184./*+INDEX_JOIN(TABLEINDEX_NAME)*/

提醒明_命令化器利用索引作L路.

比方:

SELECT/*+INDEX_JOIN(BSEMPMSSAL_HMIHIREDATE_BMI)*/SAL,HIREDATE

FROMBSEMPMSWHERESAL<60000;



185./*+INDEX_DESC(TABLEINDEX_NAME)*/

标明Ρ磉x袼饕绲呙璺椒.

比方:

SELECT/*+INDEX_DESC(BSEMPMSPK_BSEMPMS)*/FROMBSEMPMSWHERE

DPT_NO=CCBZZP;



186./*+INDEX_FFS(TABLEINDEX_NAME)*/

χ付ǖ谋绦锌焖偃饕呙,而不是全表呙璧霓k法.

比方:

SELECT/*+INDEX_FFS(BSEMPMSIN_EMPNAM)*/*FROMBSEMPMSWHERE

DPT_NO=TEC305;



187./*+ADD_EQUALTABLEINDEX_NAM1,INDEX_NAM2,...*/

提醒明_M行绦幸的x,瘟兴饕呙韬掀.

比方:

SELECT/*+INDEX_FFS(BSEMPMSIN_DPTNO,IN_EMPNO,IN_SEX)*/*FROM

BSEMPMSWHEREEMP_NO=CCBZZPANDDPT_NO=TDC306;



188./*+USE_CONCAT*/

Σ樵中的WHERE後面的ORl件M行DQUNIONALL的M合查.

比方:

SELECT/*+USE_CONCAT*/*FROMBSEMPMSWHEREDPT_NO=TDC506AND

SEX=M;

189./*+NO_EXPAND*/

WHERE後面的OR大概IN-LIST的查Z句,NO_EXPAND⒆柚蛊浠化器ζ溥M行U展.

比方:

SELECT/*+NO_EXPAND*/*FROMBSEMPMSWHEREDPT_NO=TDC506AND

SEX=M;



190./*+NOWRITE*/

克制Σ樵K的查重操纵.



191./*+REWRITE*/

能够⒁D作.



192./*+MERGE(TABLE)*/

能σD的各查M行相暮.

比方:

SELECT/*+MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMSA

(SELETDPT_NO

,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHERE

A.DPT_NO=V.DPT_NO

ANDA.SAL>V.AVG_SAL;



193./*+NO_MERGE(TABLE)*/

队锌珊愕囊D不再合.

比方:

SELECT/*+NO_MERGE(V)*/A.EMP_NO,A.EMP_NAM,B.DPT_NOFROMBSEMPMS

A(SELETDPT_NO

,AVG(SAL)ASAVG_SALFROMBSEMPMSBGROUPBYDPT_NO)VWHERE

A.DPT_NO=V.DPT_NO

ANDA.SAL>V.AVG_SAL;



194./*+ORDERED*/

根沓霈F在FROM中的序,ORDERED使ORACLE依此序ζ溥B接.

比方:

SELECT/*+ORDERED*/A.COL1,B.COL2,C.COL3FROMTABLE1A,TABLE2

B,TABLE3C

WHEREA.COL1=B.COL1ANDB.COL1=C.COL1;



195./*+USE_NL(TABLE)*/

⒅付ū砼c嵌套的B接的行源M行B接,K把指定表作炔勘.

比方:

SELECT/*+ORDEREDUSE_NL(BSEMPMS)*/

BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAMFROMBSEMPMS,BSDPTMS

WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;



196./*+USE_MERGE(TABLE)*/

⒅付ǖ谋砼c其他行源通^合闩判蜻B接体例B接起.

比方:

SELECT/*+USE_MERGE(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMS

WHERE

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;



197./*+USE_HASH(TABLE)*/

⒅付ǖ谋砼c其他行源通^哈希B接体例B接起.

比方:

SELECT/*+USE_HASH(BSEMPMS,BSDPTMS)*/*FROMBSEMPMS,BSDPTMSWHERE

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;



198./*+DRIVING_SITE(TABLE)*/

制cORACLE所x竦奈恢貌煌谋磉M行查绦.

比方:

SELECT/*+DRIVING_SITE(DEPT)*/*FROMBSEMPMS,DEPT@BSDPTMSWHERE

BSEMPMS.DPT_NO=DEPT.DPT_NO;



199./*+LEADING(TABLE)*/

⒅付ǖ谋碜B接序次中的首表.



200./*+CACHE(TABLE)*/

M行全表呙r,CACHE提醒能⒈淼z索K安排在n^嬷凶罱钌倭斜LRU的比来利用端

比方:

SELECT/*+FULL(BSEMPMS)CAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;



201./*+NOCACHE(TABLE)*/

M行全表呙r,CACHE提醒能⒈淼z索K安排在n^嬷凶罱钌倭斜LRU的比来利用端

比方:

SELECT/*+FULL(BSEMPMS)NOCAHE(BSEMPMS)*/EMP_NAMFROMBSEMPMS;



202./*+APPEND*/

间接拔出到表的最後,能够进步速率.

insert/*+append*/intotest1select*fromtest4;



203./*+NOAPPEND*/

通^在拔出Z句保存期韧ＶK行形式映Ｒ迦.

insert/*+noappend*/intotest1select*fromtest4;



ORACLE化器

.x用m合的ORACLE化器



ORACLE的化器共有3N:

a.RULE(基於t)b.COST(基於本钱)c.CHOOSE(x裥)



O置缺省的化器,能够通^init.ora文件中OPTIMIZER_MODE档母鞣N明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS

.你然也在SQL句或是(session)ζ溥M行覆w.

了利用基於本钱的化器(CBO,Cost-BasedOptimizer),你必常行analyze

命令,以增添Y估中的物件yY(objectstatistics)的蚀_性.

假如Y料的化器形式O置x裥(CHOOSE),那麽H的化器形式⒑褪欠襁行^analyze命令有P.

假如table已被analyze^,化器形式⒆映CBO,反之,Y料裼RULE情势的化器.



在缺省情r下,ORACLE裼CHOOSE化器,了制止那些不用要的全表呙(fulltablescan),

你必量制止利用CHOOSE化器,而间接裼没兑t大概基於本钱的化器.



2.LTable的体例



ORACLE裼煞NL表中的体例:



a.全表呙

全表呙杈褪琼序地L表中每l.ORACLE裼靡淮巫x进多Y料K(databaseblock)的体例化全表呙.



b.通^ROWIDL表

你能够裼没ROWID的L体例情r,进步L表的效力,,

ROWID包括了表中的物理地位Y..ORACLE裼盟饕(INDEX)F了Y料和寄存Y料的物理地位(ROWID)之g的M.

一般索引供应了疾速LROWID的办法,因而那些基於索引列的查就能够失掉功能上的进步.



3.共用SQLZ句



了不重剖析不异的SQLZ句,在第一次剖析之後,ORACLESQLZ句寄存在w中.@K位於系y全局^域SGA(system

globalarea)的共用池(sharedbufferpool)中的w能够被一切的Y料用艄灿.

因而,你绦幸SQLZ句(有r被Q一游)r,假如它

和之前的绦羞^的Z句完整不异,ORACLE就可以很快@得已被剖析的Z句和最好的

绦新.ORACLE的@功效年夜年夜地进步了SQL的绦行阅K省了w的利用.

惋惜的是ORACLE只蔚谋硖峁└咚倬n(cachebuffering),@功效K不m用於多表B接查.

Y料办理T必在init.ora中@^域O置合m的,@w^域越年夜,就能够保存更多的Z句,然被共用的大概性也就越年夜了.

你向ORACLE提交一SQLZ句,ORACLE紫仍谶@Kw中查找不异的Z句.

@Y必要说明的是,ORACLE烧袢〉氖且环N栏衿ヅ,要_成共用,SQLZ句必

完整不异(包含空格,Q行等).

共用的Z句必M足三l件:



A.字元的比^:

前被绦械末路Z句和共用池中的Z句必完整不异.

比方:

SELECT*FROMEMP;

和以下每都分歧

SELECT*fromEMP;

Select*FromEmp;

SELECT*FROMEMP;

B.Z句所指的物件必完整不异:

比方:

用物件名怎样L

Jacksal_limitprivatesynonym

Work_citypublicsynonym

Plant_detailpublicsynonym



Jillsal_limitprivatesynonym

Work_citypublicsynonym

Plant_detailtableowner



考]一下以下SQLZ句可否在@用糁g共用.

SQL

可否共用

缘故原由

selectmax(sal_cap)fromsal_limit;

不克不及

每用舳加幸privatesynonym-sal_limit,它是分歧的物件

selectcount(*0fromwork_citywheresdesclikeNEW%;

能

用粼L不异的物件publicsynonym-work_city

selecta.sdesc,b.locationfromwork_citya,plant_detailbwhere

a.city_id=b.city_id

不克不及

用jack通^privatesynonymLplant_detail而jill是表的一切者,物件分歧.

C.SQLZ句中必利用不异的名字的定(bindvariables)



比方：



第一M的SQLZ句是不异的(能够共用),而第二M中的Z句是分歧的(即便在行r,x於分歧的定迪嗤闹)

a.

selectpin,namefrompeoplewherepin=:blk1.pin;

selectpin,namefrompeoplewherepin=:blk1.pin;



b.

selectpin,namefrompeoplewherepin=:blk1.ot_ind;

selectpin,namefrompeoplewherepin=:blk1.ov_ind;

4.x褡钣行实谋砻序(只在基於t的化器中无效)

ORACLE的剖析器依照右到左的序FROM子句中的表名,因而FROM子句中在最後的表(基A表driving

table)⒈蛔钕忍.在FROM子句中包括多表的情r下,你必x裼l底钌俚谋碜基A表.ORACLE矶表r,

用排序及合愕姆绞竭B接它.起首,呙璧谝表(FROM子句中最後的那表)KτM行派序,然後呙璧诙表(FROM子句中最後第二表),最後⑺第二表中z索出的c第一表中合mM行合.



比方:

表TAB116,384l

表TAB21l



xTAB2作基A表(最好的办法)

selectcount(*)fromtab1,tab2绦rg0.96秒



xTAB2作基A表(欠安的办法)

selectcount(*)fromtab2,tab1绦rg26.09秒



假如有3以上的表B接查,那就必要x窠徊姹(intersectiontable)作基A表,

交织表是指那被其他表所援用的表.



比方:



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子句中的B接序．



ORACLE裼米韵露系捻序剖析WHERE子句,根@道理,表之g的B接必在其他WHEREl件之前,

那些能够^V失落最悍贼坑的l件必在WHERE子句的开端.



比方:



(低效,绦rg156.3秒)

SELECT…

FROMEMPE

WHERESAL>50000

ANDJOB=‘MANAGER’

AND25<(SELECTCOUNT(*)FROMEMP

WHEREMGR=E.EMPNO);



(高效,绦rg10.6秒)

SELECT…

FROMEMPE

WHERE25<(SELECTCOUNT(*)FROMEMP

WHEREMGR=E.EMPNO)

ANDSAL>50000

ANDJOB=‘MANAGER’;

6.SELECT子句中制止利用‘*‘

你想在SELECT子句中列出一切的COLUMNr,利用BSQL列援用‘*’

是一便利的办法.不幸的是,@是一十分低效的办法.H上,ORACLE在剖析的^程中,’*’顺次DQ成一切的列名,

@事情是通^查Y料字典完成的,@意味著⒑馁M更多的rg.

7.p少LY料的次

绦忻lSQLZ句r,ORACLE在炔绦辛嗽S多事情:剖析SQLZ句,预算索引的使用率,定,xY料K等等.

由此可,p少LY料的次,就可以H上p少ORACLE的事情量.



比方,

以下有三N办法能够z索出雇T等於0342或0291的T.



办法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中从头O置ARRAYSIZE,能够增添每次Y料L的z索Y料量

,建h值200



8.利用DECODE函p少rg

利用DECODE函悼梢员苊庵呙柘嗤或重B接不异的表.



比方:

SELECTCOUNT(*)，SUM(SAL)

FROM　EMP

WHEREDEPT_NO=0020

ANDENAMELIKE　‘SMITH%’;



SELECTCOUNT(*)，SUM(SAL)

FROM　EMP

WHEREDEPT_NO=0030

ANDENAMELIKE　‘SMITH%’;



你能够用DECODE函蹈咝У氐玫较嗤Y果



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.整合,oP的Y料L



假如你有蔚馁Y料查Z句,你能够把它整合到一查中(即便它之g]有PS)

比方:



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’;



(g者按:m然袢∵@N办法,效力失掉进步,可是程式的可x性年夜年夜下降,以是x者是要嗪庵g的利害)



10.h除重

最高效的h除重办法(因利用了ROWID)



DELETEFROMEMPE

WHEREE.ROWID>(SELECTMIN(X.ROWID)

FROMEMPX

WHEREX.EMP_NO=E.EMP_NO);



11.用TRUNCATE替换DELETE

h除表中的r,在一般情r下,回L段(rollbacksegments)用泶娣趴梢员换的Y.

假如你]有COMMIT事,ORACLE①Y料恢到h除之前的B(蚀_地f是

恢到绦h除命令之前的r)



而用TRUNCATEr,

回L段不再寄存任何可被恢的Y.命令行後,Y料不克不及被恢.因而很少的Y源被{用,绦rg也芏.



(g者按:TRUNCATE只在h除全表m用,TRUNCATE是DDL不是DML)

12.量多利用COMMIT



只需有大概,在程式中量多利用COMMIT,@映淌降男阅艿玫教岣,需求也COMMIT所放的Y源而p少:

COMMIT所放的Y源:

a.回L段上用於恢Y料的Y.

b.被程式Z句@得的i

c.redologbuffer中的空g

d.ORACLE办理上述3NY源中的炔炕ㄙM



(g者按:在利用COMMITr需要注重到事盏耐暾,F中效力和事胀暾酝囚~和熊掌不成得兼)



13.算l

和一样平常的^c相反,count(*)比count(1)稍快,然假如能够通^索引z索,λ饕械挠等耘f是最快的.比方

COUNT(EMPNO)



(g者按:在CSDN,曾Υ擞羞^相崃业挠,

作者的^cK不非常蚀_,通^H的y,上述三N办法K]有@著的功能差e)



14.用Where子句替QHAVING子句



制止利用HAVING子句,HAVING只z索出一切之後才Y果集M行^V.@硇枰判,等操纵.

假如能通^WHERE子句限定的的,那就可以p少@方面的_N.



比方:



低效:

SELECTREGION，AVG(LOG_SIZE)

FROMLOCATION

GROUPBYREGION

HAVINGREGIONREGION!=‘SYDNEY’

ANDREGION!=‘PERTH’



高效

SELECTREGION，AVG(LOG_SIZE)

FROMLOCATION

WHEREREGIONREGION!=‘SYDNEY’

ANDREGION!=‘PERTH’

GROUPBYREGION

(g者按:HAVING中的l件一样平常用於σ恍┘虾档谋容^,如COUNT()等等.

除此而外,一样平常的l件在WHERE子句中)



15.p少Ρ淼牟樵

在含有子查的SQLZ句中,要特e注重p少Ρ淼牟樵.



比方:

低效

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;



(g者按:常在锌吹饺’能不克不及用一SQL出….’的N子,却不知}s的SQL常常奚绦行.

能蛘莆丈厦娴倪用函到Q}的办法在H事情中长短常成心x的)



17.利用表的e名(Alias)

在SQLZ句中B接多表r,

利用表的e名K把e名字首於每Column上.@右,就能够p少剖析的rgKp少那些由Column歧x引发的Z法e`.



(g者注:

Column歧x指的是由於SQL中分歧的表具有不异的Column名,SQLZ句中出F@Columnr,SQL剖析器o法判噙@Column的w)



18.用EXISTS替换IN

在S多基於基A表的查中,了M足一l件,常常必要α硪表M行接.在@N情r下,利用EXISTS(或NOT

EXISTS)一般⑻岣卟樵的效力.



低效:

SELECT*

FROMEMP(基A表)

WHEREEMPNO>0

ANDDEPTNOIN(SELECTDEPTNO

FROMDEPT

WHERELOC=‘MELB’)



高效:

SELECT*

FROMEMP(基A表)

WHEREEMPNO>0

ANDEXISTS(SELECT‘X’

FROMDEPT

WHEREDEPT.DEPTNO=EMP.DEPTNO

ANDLOC=‘MELB’)

(g者按:相碚f,用NOTEXISTS替QNOTIN⒏@著地进步效力,下一中⒅赋)

19.用NOTEXISTS替换NOTIN

在子查中,NOTIN子句绦幸炔康呐判蚝秃.o在哪N情r下,NOTIN都是最低效的

(因它ψ硬樵中的表绦辛艘全表遍).了制止利用NOTIN,我能够把它改成外B接(OuterJoins)或NOT

EXISTS.



比方:

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.用表B代替QEXISTS



一般碚f,裼帽磉B接的体例比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’;



(g者按:在RBO的情r下,前者的绦新包含FILTER,後者利用NESTEDLOOP)
只需每年花费2000到5000美元。无论你是自掏腰包来创建一个新兴公司，还是得到了风险投资商的赞助，使用MySQL都可以降低你所需要的人力成本。

再见西城 发表于 2015-1-19 21:19:45

外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

飘灵儿 发表于 2015-1-25 17:15:04

代替了原来VB式的错误判断。比Oracle高级不少。

若天明 发表于 2015-2-3 12:00:47

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

不帅 发表于 2015-2-8 20:45:08

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

小女巫 发表于 2015-2-26 09:36:09

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

再现理想 发表于 2015-3-8 13:15:37

相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

冷月葬花魂 发表于 2015-3-15 23:58:25

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

柔情似水 发表于 2015-3-22 18:10:27

我们学到了什么？思考问题的时候从表的角度来思考问
页: [1]
查看完整版本: MYSQL编程：Oracle常}集(三)