Should i rebuild oracle indexes

If it abends, all is not lost. It does not need temporary space in the tablespace to hold a copy of the index during the rebuild. It is fast. There is really nothing not to like. The 8i education materials stated that a deleted index entry in an index block will not be reused.

That was a mostly false statement, even for that time. It easier to rebuild Indexes than recruiting agencies. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy.

Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox.

Newsletter Topics Select minimum 1 topic. Clark 'the dragon' Willis February 15, 0 Comments. Muthukumar Ponnambalam Posted February 15, 0 Comments. Hello, Usually, if the tables of the indexes are subjected to heavy deletes and inserts, you would want to rebuild indexes. Thanks Muthu. Grasshopper, No the state of the indexes, analyse them. It does not depend on the version of Oracle or Peoplesoft.

I see the mistake was wholly on my side; I apologize for this confusion Best regards Hans. The two Jonathan Lewis links you highlight on your home page make the perfect postscript to this thread. Perhaps this Don Burleson dude will read it all and stop writing until he learns what he's writing about!! October 19, - am UTC. Just for posterity links on the home page come and go Two thumbs up. Try reading some of his responses to these things on the newsgroups.

You can get to them now at groups. Please confirm. October 20, - am UTC. A reader, October 20, - am UTC. I'd like to understand how he became on of the world's top oracle db experts.

Circular reference type of a thing again Hi Tom What about bitmap indexes on the tables with heavy inserts or updates of bitmap column.

After rebuild of index size is reduced. Is it this 0. Thanks Kiril. October 21, - am UTC. A reader, October 21, - am UTC. Tom, Sorry for asking very naive question but have to. What do they or you really mean the word "balance" w. What is balance of an index? An unbalanced index would have it such that some leaf nodes could take 3, or 4, or 5 or 6 or N IO's to reach them.

This answer was quite helpful and Tom, I agreeyou should never rebuild indexes except to move them or because you have moved the underlying table and they have become "unusable". I did rebuild some to get better balance and eventually, they do go back to that "stable size" you mentioned in your first response over 2 and a half years ago. October 21, - pm UTC. Undere what circumstances? Where an index key value has multiple entries, these entries are made into the leaf block in ROWID order.

So all the blocks for the index key value are scanned in turn until an entry is found with a greater ROWID than the new row, and the row is inserted into that block which may cause extra block splitting. I dont understand why rowid is used, if say we have composite index ename, empno , 10 , 20 , 10 , 30 how are these stored? November 01, - am UTC. A reader, November 02, - am UTC. Just noticed Mr Burleson is doing a presentation at Openworld on indexing, topic II d should be good for a laugh ; When it is not appropriate.

Indexing NULLs c. The Need for Statistics d. Rebuilding Indexes Myth vs. Reality Getting Stats for Free on Rebuilds e. Testing Index Effectiveness Oracle to blame? A reader, November 03, - am UTC.

After reading this thread, it appears obvious that Burleson understands little about indexes. Yet he writes articles, Oracle authorized books and presentations at Oracle Open World on the topic.

No wonder us newbies get confused. Shouldn't Oracle take some responsibility or am I missing something? November 03, - am UTC. We cannot legally prevent someone from saying their mind. It is not legal. We cannot discriminate, we cannot blacklist. If we said "we will prevent John Doe from presenting in Oracle forums" -- we would be open to litigation.

Oracle doesn't pick all of the presentations for OOW, they are selected by the user groups as well as I understand it. The people must vote with their pocket books here. We can only be responsible for Oracle produced material. We are private individuals. Poor Don! Jensbob, November 08, - am UTC. December 03, - am UTC. It is a marketing relationship, pure and simply -- just like MS Press. The books are not 'sanctioned', they are not 'subject to approval' -- not anymore than anything by any publisher.

A reader, December 05, - pm UTC. December 06, - am UTC. Oracle Press is a big company -- McGraw Hill. They have a staff of editors. These editors hire tech editors like me -- I've tech edited for them.

These tech editors do "check its matter" as you say. They have upwards of 5 or 6 people reading the book only for technical accuracy eg: i don't correct grammar or flow, just find technical inaccuracies. So, that said, McGraw Hill with a long history and reputation -- does tech edit the books. But the tech editors themselves are human beings -- like me.

With varying levels of "experience". My point is "Oracle the company" doesn't tech edit these books. There are tech editors from time to time that work for Oracle sure. I believe when you buy a book -- you should look at the team that tech edited it to. I was as proud of the tech edit team on the book as I was of the content itself. To the point where I have them point the bios in there so you can read about the people who did it.

In fact -- that may be as important as the author themself. I've been in a situation where I've said to the publisher "if this material is printed -- I want my name removed from the book, I will not have my name associated with this content, people will believe I "believe" it to be true". It is a powerful way to get stuff retracted.

So do lions enjoy eating a turkey? A reader, December 09, - am UTC. So has he changed his tune and learnt something of what he presents or were there a lot of lions with a bad case of indigestion in attendence? I was planning to attend that Burleson session until I discovered who the presenter was! Tom's and Jonathan's sessions were great as expected. BTW, many are still waiting for the hands on lap session that you promised, Tom! December 10, - am UTC.

I had a situation where the size of the index on two columns of a particular table was 1. After rebuilding the index the size of the index came down to MB. The table is however prone to all kinds of DML activities.

I don't understand why the index grow upto 1. Can you please explain? I am using oracle 9. December 10, - pm UTC. Lets say you fill an index up with the numbers Now, you insert rows 1,, Your index is forced to put them on the "right hand side", the number 1,, must go to the RIGHT of 1,, Things in an index have a "place" Things in a heap table do not.

So, some "right handed indexes" like that can benefit from a coalesce from time to time, or maybe even a rebuild Tom, I take it this is one of the special cases that represent only a fractional percent of cases? Yet I see these at almost all my clients. I also noticed in my reading of the Tales form the Oak Table book several of your reading list folks say "rebuilds of indexes" are beneficial.

I agree, they shouldn't be rebuilt on a whim, only when it is determined they have a problem. However it is a valid tool. I also agree that the ultimate fix is to fix the code in many cases.

Unfortunately over half the client sites I visit have 3rd party code they can't fix. In that case we have to treat the symptoms. I would like to apologize to folks who read my articles about rebuild of indexes fixing cluster factors, I believe I recinded that advice where ever I could, however, I have little control over reposting. I do condone reordering of the index columns for a concatenated index to achieve better clustering factors and then apply that reorder to the appropriate SQL.

As to hardware fixes verses SQL fixes, it may be better money spent on older releases and third-party software to use the judicial application of specific technology to fix performance issues, especially if the only fix is to re-write a massive amount of SQL that could cost the company hundreds of thousands of dollars in lost time and productivity. Remember, many companies have bought into poorly designed software that they cannot easily discard.

Again, in this case we have to treat the symptoms again. I wish Oracle would take your practices to heart in all of its applications. I hate to take assignments on Oracle Apps sites because of the complex self joins, bad indexes, poor use of bind variables and cryptic use of undocumented parameters with no explanation or reasoning provided.

I wonder also what would happen if you were to play devils advocate with your examples, instead of designing them to disprove, design them to prove some of these items and see what results you obtain.

Sometimes what we see is what we look for. December 12, - pm UTC. I've never ever said "they are terrible, don't do them" please, if anyone can find where I've said that -- point it out what I'm against -- a scheduled rebuild of every single silly index on the planet -- causing downtime, eating up resources and providing negative benefits in some cases.

I'm not saying "don't rebuild this thing" am I? The ratio of deleted blocks to full blocks? Not sure what to make of your last paragraph there. The examples above clearly showed degenerate cases??!? I carefully crafted them to show a breakdown -- and the type of processing that causes it.

I gave examples to show that space is reused efficiently. Many most actually I would say are "postive" cases -- showing something to be true here. Are you the Mike Ault that works for Don Burleson? A reader, December 13, - am UTC. Because if you are, it explains your comments. Might I suggest you carefully read the excellent presentation by Richard Foot highlighted by Tom and learn why some indexes need rebuilding but why most don't.

Considering your guest appearence at the start of the presentation, it might do both you and Don Burleson some good. Keep up the great work Tom and showing us how it really is. To the last "A reader" Gabe, December 13, - am UTC. December 13, - am UTC.

I encourage discussion on any and all topics myself. The last thing about Don Burleson. Walter, December 25, - am UTC. December 25, - am UTC. Mike, December 26, - pm UTC. Tom, you have stated" I'm not saying "don't rebuild this thing" am I? If so, what are your criterions? Please illustrate them with examples as you have used to. December 26, - pm UTC. January 05, - am UTC. At the end of the write, we convert the temporary extents into permanent ones.

I know you're not Mr. I bought the book because it was on a subject that is not widely covered so I was limited to what I could pick from AND because it was Oracle Press. Come on Oracle, pull your finger out!

I will probably not buy another Oracle Press book again and feel free to contact me to arrange a refund! How we can make text index rebuild faster? March 14, - am UTC. Oracle Press books are of varying quality, as are those published by other groups. Oracle provides tech reviewers and sometimes authors but the books are ultimately published by a division of McGraw-Hill. In publishing parlance "Oracle Press" is an imprint.

The books published under the Oracle Press imprint have Oracle part numbers, and the standard label on the cover lists them as "official" and "authorized". Regardless of the publisher, all of the parties involved in publishing a book have an interest in providing the highest quality product possible. If you have issues with a book's quality, take it up with the publishers so they'll do better next time. They aren't trolling discussion boards for feedback; if you want your comments about an author or book to influence the future, tell the publisher.

March 15, - am UTC. I'm very proud of the people who have reviewed my books. They would not be what they are without them. I'm not sure that all technical books have even been through tech edits! A reader, March 15, - pm UTC. Not a technical question - just interested in knowing whether you and Don get on well - or at all? I presume he knows of your disdain for some of his theories. I bet, that if he would come to us, his first recommendation would be "Hey guys, first I would rebuild the indexes" - Ok than he sit very important in front of the terminal and waits for the end of the rebuild process.

And after hours he earned a lot of money without nothing! Hi Tom could you please let me know the way to measure the query speed improvements or any other improvement that result from Index Rebuilding.

I did this on my database and i released 2gb out of 3gb indexes space. April 01, - am UTC. Let me know what other information is missing so i can collect before index rebuild. April 04, - am UTC. You would like to also get the statistics for the queries making use of this index. I have cocurrent users. When I see queries on that table using that primary key index, I see lot of contention and users wating to get the lookup on indexe.

I have rebulded the index so many times but the problem still remains. Any ideas how do I reduce contention on this particular index? Thanks, PRS. April 05, - pm UTC. Hi From one of your posts you mentioned that by coalsecing an index we can reclaim space.

In your example you said this "Lets say you fill an index up with the numbers The index grows " Then you say "So, some "right handed indexes" like that can benefit from a coalesce from time" I dont understand how coalesce can reclaim space in this case since coalesce only merges "leaf blocks within same branch of tree". The new index entries will always go right hand side if the index is sequential, how can a coalesce help here?

May 04, - am UTC. So, it lets leaf blocks from the left go over to the right. Tom: You are doing a great service to the Oracle community. The discussion on Index rebuilding has cleared lot of myths. I was also thinking that indexes do not re-use space. I have a follow-up question on this topic. After Archiver is run, most of the old data is deleted from these tables. Under these circumstances, do I need to do anything to maintain Indexes and their used space? May 10, - am UTC. Tom: Thanks for quick response as always.

Do you recommend coalesce over rebuilding index. Does coalesce require downtime? What is the best way of doing index coalesce?

Decision is not in my hands not to do. Do you recommend rebuilding the index or drop the original one and create the new one? Disk space is not a problem. It does not require 2x the space an index rebuild can be done online EE and requires 2 times the space or more or less, depends , it requires an exclusive lock on the index before and after the rebuild so on a busy system, be very very careful and will have overhead associated with it during the rebuild as it must remember what has changed in the table too.

The space released by the old value can never be used again. As indexed values are updated or deleted, the amount of unusable space within the index increases, a condition called index stagnation.

Because a stagnated index contains a mixture of data and empty areas, scans of the index will be less efficient. Would this be a situation when an index should be rebuilt as the manual suggests?

Regards, C. May 18, - pm UTC. I have filed a documentation bug as that statement could not be any further from reality. That statement is totally false. Reason for regular rebuild? Michael, May 24, - am UTC. Hi Tom! I know that's not a good idea to "archive".

Support told us to rebuild this index and other indexes on this table at regular intervals because the archiver also runs at regular intervals. Is this a case where a regular index rebuild is inevitable? May 24, - am UTC. A coalesce may well solve the issue as well without necessitating a full rebuild.

Coalesce is a lower impact online operation than a rebuild online. Suggestion from Oracle, Advance Product Services group. We used to re-build indexes on a weekly basis and after learning from this site we stoppped doing that and moved to a specific problem solving approach.

We recently had a database audit done by one consultant from APS, Oracle. During the audit, the topic of index rebuilding had come up and he was saying we should periodically rebuild index. When asked why, he said its because you need to re-use the free space when entries get deleted from the index.

He's gone as far as putting it in his recommendations document Recommendation Indexes should be rebuilt periodically. The following query can be used to check the need of index rebuilding. Shouldn't something be done about it??? And we pay substantial amount for the product support. What do you suggest?

I have their audit report with me in which this is mentioned. I can send it to you if you want to have a look at it yourself.. When I see it in the documentation, I get it fixed. When I see it in writing, I point it out. May 25, - am UTC. If this index is on a field that is regularly updated -- I wouldn't be worrying. As you update "A" to "M", you'll get a deleted leaf row in the "A" section of the index and either create a new leaf row OR reuse an existing deleted leaf row in the "M"'s.

But it would be a candidate. Is that possible to file a documentation bug on this? We shouldn't let a myth like this spread I can understand why people rebuild index when they experience performance problem But recommanding people rebuild index even if they don't have any problem is really evil May 25, - pm UTC.

I've been reading a lot on this subject of index balance lately - read all of Richard Foote's excellent paper and much of the others and it's occurred to me that much of this argument may be due to the ambiguity of the word "balance".

Tom and many others describe balance as the number of levels between the root and the leaves, which is of course always consistent throughout the index so, by this meaning, an index is most certainly always balanced. However, if you define balance as the density throughout the leaf blocks, then by this definition an index can certainly become unbalanced. Tom gives a good example of the "sweeper" with the monotonically increasing values on the right side and sparse values remaining on the left.

June 09, - pm UTC. But, if by "out of balance", someone means "this phenomena", with no further explaination It takes like 10 minutes extra. And it is worth it. And remember, it depends on how you use the index. Take that 5 and If you do keyed reads -- guess what? It doesn't matter. If you don't have a maintenance window accommodate this task at least once a week, then you need to pay close attention to how your indexes are faring.

In Oracle, you can use the Alter Index Rebuild command to rebuild indexes. It rebuilds a spatial index or a specified partition of a partitioned index. Navicat for Oracle 's Maintain Index facilities has a couple of useful options for handling unusable indexes:. To re-create an existing index or one of its partitions or subpartitions. If the index is marked unusable, then a successful rebuild will mark it usable. This is a good candidate for rebuilding.

Notice that the total number of leaf rows went from to , which is a difference of 62 leaf rows This index should provide better performance for the application.

Script to rebuild indexes:. It is very difficult to write a script that will identify indexes that will benefit from rebuilding because it depends on how the indexes are used. For example, indexes that are always accessed vis an index unique scan" will never need rebuilding, because the "dead space" does not interfere with the index access. Only indexes that have a high number of deleted leaf blocks and are accessed in these ways will benefit from rebuilding:.

Getting statistically valid: proof from a volatile production system would be a phenomenal challenge. In a large production system, it would be a massive effort to trace LIO from specific queries to specific indexes before and after the rebuild.