ࡱ> ac`#` 0<bjbj\.\. rP>D>D4 @ @ @ $d 4d 72\rrrr!!!>7@7@7@7@7@7@7$8hY;d7@ "!!""d7 rry7.'.'.'" Rr@ r>7.'">7.'.'4' "@ 6rP p<#F66"7707|6~=<%`=<l6=<@ 6(!"7!.'O!c!!!!d7d7'!!!7""""d d d d d d d d d  When should you rebuild an index ? In an earlier article about B-tree indexes, I ended with the comment Resist the argument that you need to rebuild Indexes regularly because "they become unbalanced". It isn't a valid argument. Some time later, I received an email message pointing out that there were other, valid, reasons for rebuilding indexes. This is true, and rebuilding B-tree indexes does sometimes have benefits so I thought Id write a short article investigating the reasons for rebuilding B-tree indexes. Why rebuild an index ? There are only two reasons for rebuilding indexes. One is driven by the question: "Will the performance gain be worth the cost of rebuilding this index?" the other is driven by a similar question: Is the administrative benefit worth the cost?. One convenient aspect of phrasing the questions like this is that if the cost is effectively zero then even in the case where rebuilding an index is a pointless exercise, Im not going to insist that you stop doing it. Of course, the time and effort may not really be zero, and just occasionally an index rebuild can cause subsequent short-term performance problems so do carry on reading the rest of the article. The standard argument for rebuilding indexes is based on the fact that B-tree indexes tend to degenerate (i.e. become less space-efficient) as they are populated over time. And the argument is true but only because the standard theory predicts that B-tree indexes will tend to operate at 75% packing, and Oracle creates them, by default, at 90% packing. (In fact, Oracle does not coalesce adjacent, low-usage, leaf blocks automatically, so its packing will often be a few per cent lower than the theoretical value). So the question of the rebuild generally comes down to the following implicit question: If I think the index is running at X% packing, should I rebuild it to 90% ? Lets start by considering the significance of the change from X% before we worry about specific values for the magic number X. Potential Benefits When you rebuild an index, the physical effect is to reduce the size of the index (especially the leaf block count). This may result in three possible benefits: The optimiser calculations may produce a smaller value for the cost of using the index, so the optimiser may use the index in more execution plans. Typical queries that use the index may have to visit fewer index blocks (leaf blocks especially), and therefore run more efficiently. Because the index is smaller, it may become a better survivor in the buffer cache, i.e. blocks from the index are found in the buffer more frequently, which then means fewer blocks from other objects are flushed to allow index blocks to be reloaded. If this occurs, system-level I/O is reduced, resulting in a possible performance gain for everyone. So lets check each effect in turn, and see what the different benefits look like in different circumstances. Well start with the optimiser cost calculations and a simple worked example. Optimiser Costing If youve read the article Why isnt Oracle using my index ? you will be familiar with the formula originally published by Wolfgang Breitling at IOUG-2002 which gives the general cost of accessing a table through an index as: blevel + selectivity * leaf_blocks + selectivity * clustering_factor. Consider an index on 10,000,000 rows, with an average index entry size of 40 bytes. This would give us about 200 entries per leaf block if we use an 8K block size. Assume that the index (according to some statistics in the view index_stats) is running at 50% efficiency and is therefore, according to common dictat, a good candidate for rebuilding (to the default 90% - but we will use 100% in our example). In other words, the index is currently running with about 100 entries per block. Lets do the arithmetic, first before the rebuild: 10,000,000 rows at 100 rows per block => 100,000 leaf blocks 100,000 leaf blocks (at 50% packing) => 1,000 level 1 branch blocks 1,000 level 1 branch blocks (at 50% packing) => 10 level 2 branch blocks 10 level 2 branch blocks (at 50% packing) => 1 level 3 branch block Now after the rebuild (with pctfree = 0): 10,000,000 rows at 200 rows per block => 50,000 leaf blocks 50,000 leaf blocks (at 100% packing) => 250 level 1 branch blocks 250 level 1 branch blocks (at 100% packing) => 2 level 2 branch blocks 2 level 2 branch blocks (at 100% packing) => 1 level 3 branch block So the index has blevel = 3 before and after rebuilding, but 100,000 leaf_blocks before the rebuild dropping to 50,000 afterwards. Does this tell us anything about the optimisers new cost estimate? Not really, although it is an important little detail that the blevel, which is one component of the cost calculation, rarely changes on rebuilding an index. The exponential relationship between the blevel and the maximum number of rows that could fit into the index at that blevel makes this almost inevitable. We need to examine not just the leaf_blocks, but the entire cost component: selectivity * leaf_blocks. How much does this change? Well, that depends on how many rows you have for each index value. Lets try a couple of numbers, one for a high-precision index (5 rows per value), and one for a lower quality index (50 per value). First the high-precision example: Rows per value = 5 => selectivity = 1/2,000,000 Selectivity * leaf_blocks (old) = 100,000 / 2,000,000 = 0.05 Selectivity * leaf_blocks (new) = 50,000 / 2,000,000 = 0.025 Because of rounding (I believe Oracle always rounds up at this point in the formula) this component of the cost estimate does not change. Now the low quality example: Rows per key = 50 => selectivity = 1/200,000 Selectivity * leaf_blocks (old) = 100,000 / 200,000 = 0.5 Selectivity * leaf_blocks (new) = 50,000 / 200,000 = 0.25 Again this component of the formula shows no change. In fact, it is not until each key value represents 100 rows in the table that the cost estimate is reduced as a result of our rebuilding the index to pack it from 50% to 100% efficiency. So, there are cases where rebuilding an index will change the optimisers opinion of the usefulness of that index - when there are a significant number of rows per key values - but those cases may be fairly rare in your system and such indexes tend to be prime suspects in the hunt for inappropriate indexes anyway. Typical Queries Even if the optimiser isnt convinced that your index has improved, maybe the end-user queries that use that index will run better anyway. As ever, it is important to know the data and know the application. When you start to think about rebuilding an index because index_stats says it is running at 50%, the first thing you should do is to ask yourself if (for example) that 50% means that half the index is nearly packed and the other half is nearly empty one magic number for the whole index may not be giving you any useful information. (In the absence of knowledge, you might try a rather expensive treedump to check the details). Then, when you have decided what the number means, you have to decide if rebuilding would actually have any perceptible effect for your users. Depending on the way the table and index are used, the answer may be no. Consider this example. The table is addressed by an Object Oriented system, which always uses meaningless numeric keys. Every query uses a key value to collect data. One trip down the index fetches one rowid from a leaf block, and then one row from the table. Rebuilding the index to pack 200 rowids per leaf_block doesnt make any difference if you want only one row. How about an example closer to the other end of the spectrum: a child table where one foreign key value picks up100 rows. Packing the index means that you only visit one leaf_block instead of two for those rowids. Terrific except maybe those 100 table rows come from 100 table blocks, so the effort of rebuilding the index makes a difference of slightly less than one percent to the end-user. You have to check out the figures when trying to work out whether or not its going to be worth rebuilding an index, especially if theres some reason that make it awkward to do the rebuild. Buffering Benefits Clearly, if you have a well packed index it is likely to cause less pollution in the buffer cache - there are fewer index blocks so the whole index may get into the buffer, and because there are fewer blocks to knock other data blocks out of the buffer, there may be a general reduction in I/O. On the down-side, a well-packed index may be subject to extra contention on inserts, updates and deletes. Its harder to measure a case like this, but its probably a good enough reason to rebuild an index (partition) thats about to be made read-only. Here are three thoughts to consider, though. If its a really popular index, then the LRU and touch count could be keeping it buffered all the time anyway, so whilst it may be wasting space in the buffer, it may not be causing any extra I/O. Secondly, for many queries the significant cost of the query is the cost of visiting table blocks which have a low re-visit rate and cause much more aggressive buffer flushing, so worrying about indexes may be distracting you from the problem you should be addressing. Finally, it is possible that by rebuilding indexes too frequently, you may be exaggerating the problem rather than fixing the problem you might like to try the following test (expect it to take a few minutes) on a system with an 8K block size - and not in an ASSM tablespace if you are using Oracle 9. drop table t1; create table t1(n1 number(38)); create index i1 on t1(n1); execute dbms_random.seed(0) begin for i in 1..400000 loop insert into t1 values( trunc(power(10,14) * dbms_random.value) ); commit; end loop; end; / -- default pctfree for rebuild alter index i1 rebuild pctfree 10; begin for i in 1..100000 loop insert into t1 values( trunc(power(10,14) * dbms_random.value) ); commit; end loop; end; / analyze index i1 validate structure; select lf_blks from index_stats; Check the number of leaf blocks in the index when you do the rebuild in the middle of the test, then the repeat the experiment without the rebuild. You may be surprised at the difference in results. (In my case, the count with the performance-enhancing rebuild was 2227, but when I didnt do the rebuild the count stayed down at 1778 blocks). Please note, this test does not prove (and is not intended to prove) that you should not rebuild indexes. It shows only that it is possible for a rebuild to have a counter-intuitive effect. There are scenarios, based on data patterns, where this type of effect is likely to appear in critical subsections of an index shortly after a rebuild. The point I am making is that you should always think carefully before deciding to rebuild an index. Reminder Throughout this article, I have been discussing B-tree indexes. There is a completely different argument about bitmap indexes that I have already covered in various articles about bitmap indexes previously published on  HYPERLINK http://www.dbazine.com www.dbazine.com I have also ignored the possibility that you could choose to rebuild indexes (or at least index partitions) to 100% packing (pctfree = 0) just before you make a tablespace read-only. Even then, it might not be worth the effort if it causes problems whilst you are doing the rebuild (remember, there was a fairly serious bug in online index rebuilds which may still be present in be recent versions of Oracle). Finally, there are always some indexes which, for application-dependent reasons, behave in a completely catastrophic manner there are always special cases where a regular rebuild is likely to be a good idea. Even then, you should check whether a regular coalesce might be a better idea in the short term, with a strategic redesign involving function-based indexes as a longer term solution. Conclusion There is only one sound argument for rebuilding an index: Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily. BUT, if you have a regular window when the system is not in use, have a simple batch job that runs in that window without putting pressure on other batch tasks, and that batch job can never fail then you might as well rebuild all the safe indexes you want to; there is often a slight performance gain to be had, and if that gain costs you nothing, then you might as well have it. Jonathan Lewis is a freelance consultant with more than 16 years experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of 'Practical Oracle 8I - Designing Efficient Databases' published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, presentations and seminars and tutorials can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups. #i  a e U`[f L]hp  0<.j bk* > ^ '!!!E"Q"##c%h%p%z%%%%%c&o&&&&&((((*h\h=56 h=56h\ h=6h=Y#$ !   de/ o#<<3sKLvB o#,jQR(!)!9!!!##$$ & &W(X(Y(l(* o#***++,,----..1.2.8.Q.j.........../gd4W o#********++++++,,,-----///0 0!0R0Y0w1{111H2T23333333x4444%636666699::::<<<<ȷ h=6 h=0Jjh=Ujh=Uh=OJQJh4Wh4W] h4W]h4Wh=] h=56h4Wh=h\>//3/^/c/m/x/}//////!1"122233557 7+7f7g7 & F o#g777"9#9:::<<<<<< & F  & F^ 7 0P@PBP. A!"#8$8%88 7 0P@PBP. A!"#8$8%88 DyK www.dbazine.comyK 0http://www.dbazine.com/H@H NormalCJOJQJ_HhmH sH tH F@F Heading 1$./@&a$5>*8@8 Heading 2@&5>*6@6 Heading 3@&5v@v Heading 47$&$d%d&d'd/+D@&5>*CJOJQJt@t Heading 57$&$d%d&d'd/+D@&5CJOJQJDA@D Default Paragraph FontViV  Table Normal :V 44 la (k(No List F@F Normal Indent0^`08+8  Endnote TextCJ4@4 Header  o#B"B  Footnote Text ^5>O2> Double Indent ^.OB. Action5>*6"6 Caption xx56Ob6 program CJOJQJl-@rl  Macro Text"  ` @ OJQJ_HhmH sH tH jB@j Body Text1&$d%d&d'd/+D CJOJQJ0U@0 Hyperlink>*B*@C@@ Body Text Indent6O bullet pointx & F hh>T^h`@V@@ FollowedHyperlink>*B* <O< aw list body  & F&O& n  & F4 4 P 9P#$  !de/     3sKLvB,jQR()9  W X Y l """##$$%%%%&&1&2&8&Q&j&&&&&&&&&&&''3'^'c'm'x'}''''''!)")***++--/ /+/f/g///"1#1222444440000000 0 0 0 0 0 0 0 0 000 0 0 000000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00)0)0)0)0)0)0)0)0)0)0)00Y 0Y 0Y 000000Y 000000000000000000000000000000000000000000000000000000000*<%*/g7< "#$&'<!+++4Xqr/Xr$9C_iyuUir$߀M'$,,Gi# L0e0e     A5% 8c8c     ?1 d0u0@Ty2 NP'p<'pA)BCD|E||@q 0(  B S  ?4;| ;$3343349*urn:schemas-microsoft-com:office:smarttagsplaceB*urn:schemas-microsoft-com:office:smarttagscountry-region )i r  HNhoci;FyEPch%%&-&=&>&m&r&&&&&&&''6';'K'\'''''x,,,,0044" 7?`c EJ,1%%%%%%&&2&7&9&<&S&Y&m&s&&&&&&&&&&&&''''"'6'<'e'k'n'q'x'{'''''443333333333333333333333333333333333333   L ]   0<@@.j  ^' """"####$$ (!(++224444|&4}誂J~ީ3\4Qd$"en_Y`~z~ |( Pj|ޑ bL@ ^ )ކSN30GJcy>kyEڟ_~eCs) :?w0F-g} ^`.^`.^`.^`. ^`OJQJo( ^`OJQJo( ^`OJQJo( ^`OJQJo(hh^h`. hh^h`OJQJo(*hh^h`.h^`OJQJo(hHh^`OJQJ^Jo(hHohpp^p`OJQJo(hHh@ @ ^@ `OJQJo(hHh^`OJQJ^Jo(hHoh^`OJQJo(hHh^`OJQJo(hHh^`OJQJ^Jo(hHohPP^P`OJQJo(hH hh^h`OJQJo(@hh^h`. hh^h`OJQJo( hh^h`OJQJo(@hh^h`B*OJQJo(h ^`hH.h ^`hH.h pLp^p`LhH.h @ @ ^@ `hH.h ^`hH.h L^`LhH.h ^`hH.h ^`hH.h PLP^P`LhH.@hh^h`B*OJQJo( hh^h`OJQJo( hh^h`OJQJo(hh^h`.8]~}|cy>_~eCsSN30@F-g}~:?w^ )| |( kyED]@h h^h`OJQJo(                  4W|n=\!(4%o@ 4@UnknownGz Times New Roman5Symbol3& z Arial?5 z Courier New;Wingdings"CPh+-rf5ù&fF ,_,_!P2d442QPHP(?\2Index Rebuilds (JL Comp))Oracle indexes rebuild performance tuningJonathan LewisJonathan Lewisl                   Oh+'0t      Index Rebuilds (JL Comp)Jonathan Lewis,Oracle indexes rebuild performance tuningPThere are special cases where you might need to rebuild Oracle B-tree indexes Normal.dotJonathan Lewis267Microsoft Office Word@ @3@N@vot,՜.+,D՜.+,@ px  !Oracle performance tuningJL Computer Consultancy_4 Index Rebuilds (JL Comp) Title 8@ _PID_HLINKSAhg,http://www.dbazine.com/  !"#$%&'(*+,-./023456789:;<=>?@ABCDEFGHIJKLMNOQRSTUVWYZ[\]^_bRoot Entry F@(MdData )1Table1<WordDocumentrPSummaryInformation(PDocumentSummaryInformation8XCompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q