MYSQL网站制作之ORA FAQ 功能调剂系列之――当索引第一...
使用它开发程序也是非常简单的。”索引|功能ORAFAQ功能调剂系列之——
TheOracle(tm)UsersCo-OperativeFAQ
Whywouldareverseindexbeusefulwhentheleadingcolumnoftheindexisgeneratedfromasequence?
当索引第一列由序列发生，一个逆序索引有甚么用？
--------------------------------------------------------------------------------
Authorsname:NormanDunbar
AuthorsEmail:Oracle@bountifulsolutions.co.uk
Datewritten:18/04/2004
Oracleversion(s):9.2.0.3
--------------------------------------------------------------------------------
Whenyoustoredatainanindexedtable,certaincolumnsofdataarecopiedintotheindexalongsidetherowidofthedatarowinthetable.Thedatainthetableisstoredrandomly,oratleast,notnecessarilyintheorderyouputthemthere.
当你在一个索引表中存储数据时，数据的某些列同表中数据行的ROWID被拷贝进索引。表中的数据是“随机地”寄存的，大概最少不必要依照你放进它们的按次。
Theindexentries,ontheotherhand,mustbestoredinorder,otherwisetheusabilityoftheindexisremoved.Ifyoucouldwalkthroughtheentriesinanindex,youwouldseethattheyareinorder,usuallyascending,butsince8i,descendingalsoworks.
而索引项应该按按次存储，不然其毫无用处。假如你深切一个索引的各项，会看到他们是有序的，一般升序，但从8i入手下手，也能够降序。
Entriesarestoredinorderoftheirinternalrepresentation,notnecessarilythesameaswhatyouseeonscreenwhenyouSELECTthecolumnsfromatable.
项目是依据外部暗示的按次存储的，其实不必要与你从表中SELECT列时在屏幕上看到的分歧。
Iftheindexedcolumn(s)containcharacterdata(CHAR,NCHAR,VARCHAR2orNVARCHR2)thenthedatawillappearonscreenexactlyasitdoesintheindex.Forexample,ifthecolumncontainsORACLEtheindexentrywillalsobeORACLE.
若索引列含有字符数据（CHAR,NCHAR,VARCHAR2或NVARCHR2），那末数据在屏幕上的显现将和在索引中分歧。比方，若列中含有“ORACLE”，那末索引项中也是“ORACLE”。
WecanusetheDUMPcommandtoshowustheinternalrepresentationofanydatatype.Thiscommandtakesfourparameters.Thefirstisthedatayouwishtodump,thesecondisthebaseyouwishtodumpitin.Thedefaultis10whichmeansthatDUMPwilldisplaythecharactersindecimal,orbase10.Theotherallowedvaluesare8(Octal),16(Hexadecimal)or17(Characters).
我们可使用DUMP指令来显现任何数据范例的外部暗示。这一命令有四个参数，第一个是要dump的数据，第二个是要dump出的基数，缺省是10，即DUMP将显现十进制字符。其他的可选参数是8（八进制），16（十六进制），17（字符）。
Thethirdparameteristhestartpositioninthedatayouwishtodumpfromandthefinalparameteristheamountofdatayouwishtodump.Allbutthefirstparameterhavesensibledefaults.
第三个参数是数据中要dump的入手下手地位，最初一个参数是要dump的数据量。除第一个参数外，其他都有公道的缺省值。
UsingDUMP,wecanseetheindividualcharactercodesforourORACLEdata:
利用DUMP，我们能够看到“ORACLE”数据的各个字符码：
SQL>selectdump(ORACLE,10)fromdual;
DUMP(ORACLE,10)
-------------------------------
Typ=96Len=6:79,82,65,67,76,69
Wecanprovethatthisiscorrectbyconvertingbackfromdecimalcharactercodestoactualcharacters:
我们能够证实，能够准确的将十进制字符码转换回实在字符：
SQL>selectchr(79),chr(82),chr(65),chr(67),chr(76),chr(69)fromdual;
CCCCCC
------
ORACLE
Wecouldhaveusedbase17todothesamething:
我们可使用17作为基数完成一样的事变：
SQL>selectdump(ORACLE,17)fromdual;
DUMP(ORACLE,17)
-------------------------
Typ=96Len=6:O,R,A,C,L,E
Numericcolumnsareverymuchdifferent.TheinternalformatofanumberisdifferentfromthatwhichappearsonscreenafteraSELECTbecausetheinternalformatisconvertedtoASCIIformatsothatitcanbedisplayed.Wecanseethisinthefollowing,firstincharacterformat:
数字列是分歧的。数的外部格局与SELECT后在屏幕上的显现是分歧的，外部格局转换为ASCII格局才干一般显现出来。我们能够以下观察，起首是字符格局：
SQL>select1234as"1234",
2dump(1234,17)
3fromdual;
1234DUMP(1234,17)
-------------------------
1234Typ=96Len=4:1,2,3,4
Thenininternalformat:
然后外部格局：
SQL>select1234as"anumber",
2dump(1234,17)
3fromdual;
anumberDUMP(1234,17)
------------------------------
1234Typ=2Len=3:c2,^M,#
Thefirstcolumnsinbothexampleslookidentical,butthisisonlybecauseSQLPlushasconvertedtheinternalformatofthenumber1,234intothecharacterformatsothatthedisplaydevice(themonitorscreen)isabletoshowit.Binarycharactershaveanastytendencytodisruptcharacterdeviceslikecomputermonitorswhenrunningintextmode.
两个例子的第一列看起来一样，但这只是由于SQLPlus将数1,234的外部格局转换为了字符格局，从而显现设备（显现器屏幕）能够显现它。二进制字符老是会厌恶的侵扰运转于文本形式的相似盘算机显现器如许的字符设备。
Takealookatthesecondcolumnintheaboveexamplesandnoticethedifference.Inthefirstexampleweseetheindividualcharacters1,2,3and4whilethesecondexampleshowsonlythreebytesintheinternalformatofthenumber1,234.LetschangetheDUMPcallsslightly,anddothewholelotinonecommand:
看看下面例子的第二列并注重分歧点。第一个例子中我们看到一个一个字符1,2,3和4，而第二个例子只显现数1,234外部格局中的三个字节。让我们略微修正一下DUMP的挪用，用一个指令完玉成部对照：
SQL>select1234as"1234",
2dump(1234,10),
31234as"anumber",
4dump(1234,10)
5fromdual;
1234DUMP(1234,10)anumberDUMP(1234,10)
-------------------------------------------------------------
1234Typ=96Len=4:49,50,51,521234Typ=2Len=3:194,13,35
Thistime,weseetheactualcharactercodesusedinternally.Onceagaincolumns2and4differ.Column4isshowingthreebytesandthesethreebytesaretheinternalbinaryrepresentationofthenumber1,234.
此次，我们看到外部真实的字符码。列2和4仍是分歧。列4显现了三个字节，这三个字节就是数1,234的外部二进制暗示。
Itisthisbinaryrepresentationthatisusedintheindexentrywhenanumbercolumnisindexed.
当数字列创建索引时，恰是这个二进制暗示用于索引项中。
Takeafewminutesandexperimentwithdumpingafewothernumbers-sticktointegersfornowasthosearewhatsequencesgenerate.
用几分钟来实行一下dump一些其他数字——如今存眷整数，恰是序列天生的范例。
SQL>createtabletest(anumber);
Tablecreated.
SQL>begin
2forxin1..1e6
3loop
4insertintotestvalues(x,substr(dump(x,10),14));
5endloop;
6end;
7/
PL/SQLproceduresuccessfullycompleted.
Ifwehavealookatthebcolumnofthetable,wecanseethateachentryisascendinginasimilarmannertotheacolumn.Herearethefirst20rows:
假如我们看看表的b列，能够看到每一个条目是递增的，正和a列相似。这里是前20行：
SQL>colbformata20wrap
SQL>selecta,bfromtestwherea<21;
AB
--------------------
1193,2
2193,3
3193,4
4193,5
5193,6
6193,7
7193,8
8193,9
9193,10
10193,11
11193,12
12193,13
13193,14
14193,15
15193,16
16193,17
17193,18
18193,19
19193,20
20193,21
Theentriesareverysimilarandallhavethesameleadingbyte.
各条目十分相似且均有一个前导字节。
Howsequencesaffectindexes.
Asmentionedabove,indexentrieshavetobestoredinorder,however,thetabledataneednotbe.Ifyourindexedcolumnisfedbyasequence,thedatawillbesimilartothe20rowsshownabove.
序列怎样影响索引
如前所述，索引项必需顺次存储，可是表中的数据不必要。若你的索引列来自一个序列，数据将相似于下面显现的20行。
Similarentrieswillgrouptogetherintheindex,sotheindexblockswillsplitasnecessaryandnewentrieswillendupallhittingthesameblockuntilittoofillsupandsplits.
类似的项将在索引中统一组中，以是索引块将按需支解，且新的项将填进统一个块，直至该块已满并支解。
Ifyouhaveonepersonrunningtheapplication,thisisnttoomuchofaproblem.Iftheapplicationismulti-userthenitmeansthateveryuserwilltendtowriteintothesameindexblockandbufferbusywaitswillbetheoutcomeastransactionsqueuetowritedatatothehottestindexblockaround.
若单用户实行使用程序，这不是甚么成绩。若使用是多用户的，则意味着每一个用户将写进统一个索引块。事件“列队”来写数据到最“热”的索引块中将形成缓冲忙守候。
Backinoursmalltest,ifyouselectmoredatafromthetesttable,youwillfindthatinthe1millionrows,thereareonly4differentvaluesfortheleadingbyteontheinternalnumericformatandevenworse,mostoftheentriesintheindexhavethesameleadingbytevalue:
回到我们的小实行，若从测试表选择更多的数据，会发明在一百万行中，外部数据格局的前导（/肇端）字节唯一4个分歧的值，乃至更糟，索引中年夜多半项有不异的前导（/肇端）字节：
SQL>selectsubstr(b,1,3),count(*)
2fromtest
3groupbysubstr(b,1,3);
SUBCOUNT(*)
-------------
19399
1949900
195990000
1961
Icheatedanddiscoveredthattherewasacommainposition4ofeveryrowinthetablethatshowIknewtouseathreecharacterlengthinmySUBSTR.
我讨了个巧并发明表中每行的第4位是一个逗号，这就是为何我晓得在SUBSTR中利用一个三位字符长度。
Whattheaboveshowsisthatinanindexof1millionsequentialentries,thevastmajorityhavethesameleadingbyteandsowillallbetryingtogetintothesameblockintheindex.
下面所示的是在一个一百万序列项的索引中，尽年夜多半有一个不异的前导（/肇端）字节，从而一切（操纵）将利用索引中的统一块。
Howreverseindexescuretheproblem.
Areversekeyindexstoresthebytesoftheindexedcolumn(s)inreverseorder,sothedataORACLEisactuallystoredintheindexasELCARO.Usingareverseindexonacolumnfedbyasequencespreadsthelocationofsequentialnumbersacrossawiderrangeofleafblocksandtheproblemofasinglehotblockisremovedbecausetheindexentriesarestoredinreverseorder.
逆序索引怎样办理这个成绩
一个逆序键索引依照逆序存储索引列的字节，以是数据“ORACLE”在索引中保留为“ELCARO”。在序列添补的列上利用逆序索引将序列值分摊到一个更广的叶块上，因为索引项存储为逆序，一个独自的“热”块的成绩就不存在了。
SQL>altertabletestadd(cvarchar2(30));
Tablealtered.
SQL>updatetestsetc=substr(dump(reverse(a),10),14);
1000000rowsupdated.
SQL>selectsubstr(c,1,instr(c,,)-1),count(*)
2fromtest
3groupbysubstr(c,1,instr(c,,)-1)
4orderbyto_number(substr(c,1,instr(c,,)-1))
SUBCOUNT(*)
-------------
210102
310101
410101
510101
Allothernumbersbetween6and95inclusive,have10,101entrieseach.
9610101
9710101
9810101
9910101
10010101
99rowsselected.
Thistime,our1millionrowindexentryhasitsleadingbytevaluespreadacross99(100ifyouincludeavalueforzero)differentvalues,ratherthanjust4.Inaddition,theactualreversedbytesarefairlyrandomlyscatteredacrosseachofthedifferentvaluestoo.
此次，我们一百万行的索引项将前导（/肇端）字节分摊到99（若包含了0则有100个）个分歧的值，而不是仅为4。进一步的，现实上逆序字节还相称随机的分离在每个分歧的值上。
Asmoreentriesareaddedtotheindex,blockswillbesplittoaccomodatethenewentriesintheirproperlocation.Asthedataisarrivingalmostrandomlybymeansofthereversingoftheactualdatabytesfortheindex,theindexitselfwillbeextendedtoaccomodatethesenewvalues.However,ratherthanalwaysbeingstoredinthesamesinglehotindexblock,newentrieswillbespreadacrossanumberofexistingblocks(assumingtheindexhasbeenaroundforawhile)thusreducingcontention.Ofcourse,blocksplitswillstilloccurontheseblocksasnewvaluesfilluptheexistingblockbutitshappeningallovertheindexnotjustinoneplace.
跟着更多的项目增加到索引中，各块将支解以在符合的地位包容新的项目。因为从索引中数据字节逆序处置来讲，数据是几近“随机”到来的，索引本身扩大以包容新值。可是，与老是存储到统一个“热”块中分歧，新的项目将分摊到多个已有的块（假定索引已运作了一段工夫）中。固然，当新值填满这些块时仍会产生块支解，但这将在索引的各部分产生，而不是一个中央。
Thisisthereasonwhyreversingtheindexwhenitsleadingcolumnisfedbyasequencereducesbuffercontention,removesthehotblockproblemandbydoingso,reducesthepotentialforbufferbusywaitsonamulti-usersystem.
这恰是为什么以后导（/肇端）列由序列天生时索引的逆序处置能够下降缓存争用，办理热块成绩，而且如许做能够在一个多用户体系中削减缓存忙守候的时机。
DrawbackstoReverseKeyIndexes
Ofcourse,therearedrawbacksaswell.Bysettingupareversekeyindexyouareincreasingtheclusteringfactoroftheindex.Theclusteringfactor(fromUSER_INDEXES)isusedbytheoptimiser(CBO)todeterminehowbesttoaccessdatainanINDEXRANGESCAN.IftheclusteringfactorisroughlyequaltoBLOCKSminusFREE_BLOCKSfromUSER_TABLESthenthechancesarethatarangescanwillreadoneindexblock,andlocateall(ornearlyall)ofthedatarowsinneedsinoneormoreadjacentblocksinthetable.
逆序键索引的不敷点
固然，也有不敷的地方。设置一个逆序索引，增添了索引的簇因子（ClusteringFactor）。优化器（CBO）使用簇因子（来自USER_INDEXES）来决意在一次INDEXRANGESCAN中怎样最好的会见数据。若簇因子几近与BLOCKS-FREE_BLOCKS（来自USER_TABLES）邻近，一个区间扫描将大概读一个索引块，然后依据必要在一个或更多的邻近块中定位一切（或靠近一切）数据行。
Ontheotherhand,iftheclusteringfactorisclosetoNUM_ROWSinUSER_TABLESthenthechancesarethattheentriesstoredtogetherinoneindexblockarelikelytobescatteredthroughoutawiderangeoftableblocks-sotheindexrangescanmaynotbechosenasagoodmethodofaccess.
另外一方面，若簇因子靠近USER_TABLES中的NUM_ROWS，一个索引块中存储靠近的项目就极可能分离到良多的表块中——以是索引区间扫描便可能不是一个会见的好办法了。
Obviouslytheaboveappliestoananalysedtableandindex.
明显下面使用于一个剖析了的表和索引。
--------------------------------------------------------------------------------
Inaquicktestonatablewith100,000rowsloadedusingasequence,anormalindexwasusedformostqueriesreturningupto30rows,aswasthereversekeyindex,however,whenthenumberofrowswentupto1,000,000thereversekeyindexwasneverusedandafulltablescanwasusedeverytime.
在一个用一个序列载进并含100,000行的表上做一个疾速的测试，分离用一般索引和逆序索引查询并前往30行数据，当行数增加到1,000,000，将不再利用逆序索引而是每次利用全表扫描。
Furtherreading:
OraclereferencemanualforyourversionofOracle.
进一步浏览：
你的Oracle版本的Oracle参考手册
--------------------------------------------------------------------------------
本文翻译自http://www.jlcomp.demon.co.uk/faq/autotrace_pruning.html译者仅保存翻译版权
MySQL已经为支持所有最流行的Web2.0语言做好了准备，诸如Ruby、Ajax等，当然还有PHP。有的业界分析师说过，“每一个Web2.0公司实质上就是一个数据库公司。 原来公司用过MYSQL自己也只是建个表写个SQL 然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情 如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录. 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。 以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了 需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。 Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。 SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.
页:
[1]