Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
when should i rebuild db indexes?
when should i rebuild db indexes.. we do it on a scheduled intervals. i would like some recommendations for when to rebuild db indexes
Answers
-
-
Rarely, if ever.
-
below two are the conditions when we rebuild indexes
1) If the index has height greater than four, rebuild the index.
2) The deleted leaf rows should be less than 20%.
cheers,
Sachin
-
934697aa-8735-4679-af67-dc6ad208430a wrote: when should i rebuild db indexes.. we do it on a scheduled intervals. i would like some recommendations for when to rebuild db indexes
When it rains on the first Monday of the month.(is that when you flush the shared pool?)
But seriously most indexes will NEVER benefit from rebuilding. If you find rebuilding an index on a schedule is benefiting you then you probably have a design problem.
read about the truth in Richard Foote's presentation https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/ Richard Foote is widely considered the expert in this field.
-
Sachin,
Please resist the temptation to recite this piece of stupidity in the future; and if you can't resist the temptation, kindly justify the claim and don't forget to highlight when your suggestions might be particularly counter-productive.
Regards
Jonathan Lewis
-
-
Hi, If your table frequently dml transaction happening mean you can do rebuild index
example -deletion once you rebuild your index you get back deleted record space.
-
... until you insert and delete more rows, and you're right back where you started, after having done lots of extra work.
-
Hi Jonathan,
Kindly forgive me if I am wrong, that is what I read , could you please correct me, i'll be highly thankful to you,
Identifying which Oracle Indexes to Rebuild
I mean no disrespect I just want you to correct me as I am an entry level DBA, so that I can learn from your experience
Regards,
Sachin
-
Sachin, a lot of people disagree violently with many things on that site.
As others have said in this thread - there is rarely a benefit to rebuilding indexes, certainly I would NEVER be rebuilding indexes on a schedule. About the only instance I can think of where I might consider rebuilding an index is if I had a situation where a table grew to have lots and lots of rows and then lots and lots of rows were PERMANENTLY deleted - say if you had a table that you were supposed to be purging every month and you forgot to for 10 years. In such a case, you are permanently and forever shrinking the table and the index, so there may be a benefit to shrinking the space.