ralph_rea
asked on
Oracle index fragmentation
Hi experts,
my Oracle DB version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I've 150 indexes with high fragmentation and now I'd like to remove or to reduce index fragmentation (Shrinking Indexes), but I need that this operation be done completely online.
Which SQL script I can use to reduce the index defragmentation?
Thanks in advance!
my Oracle DB version is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I've 150 indexes with high fragmentation and now I'd like to remove or to reduce index fragmentation (Shrinking Indexes), but I need that this operation be done completely online.
Which SQL script I can use to reduce the index defragmentation?
Thanks in advance!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My understanding of shrink is that it would only work with completely empty blocks. A rebuild would also reclaim space in blocks that still have data in them. With a table it isn't as much of an issue because partial blocks could be reused, but that is not the case with index blocks (they must be completely empty before they go back on the free list).
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, Could I know how much redo will be generated as a result of this Coalesce or Shrink Space?
Maybe one of these will help:
usage of "v$mystat": http://www.dbi-services.com/index.php/blog/entry/what-about-alter-index-shrink-space-
http://www.orafaq.com/wiki/Oracle_database_FAQ#How_full_is_the_current_redo_log_file.3F
usage of "v$mystat": http://www.dbi-services.com/index.php/blog/entry/what-about-alter-index-shrink-space-
http://www.orafaq.com/wiki/Oracle_database_FAQ#How_full_is_the_current_redo_log_file.3F
ASKER
To avoid to generate redo, I thought to run this script for each index:
ALTER INDEX <myindex> NOLOGGING;
ALTER INDEX <myindex> SHRINK SPACE COMPACT;
ALTER INDEX <myindex> SHRINK SPACE CASCADE;
ALTER INDEX <myindex> LOGGING;
What do you think?
ALTER INDEX <myindex> NOLOGGING;
ALTER INDEX <myindex> SHRINK SPACE COMPACT;
ALTER INDEX <myindex> SHRINK SPACE CASCADE;
ALTER INDEX <myindex> LOGGING;
What do you think?
If you decide to perform this on productive/live site, I'd suggest you'd better take a backup before and after that operation!
The NOLOGGING options is a very powerful "switch" considering time/performance issues, BUT it may be quite "harmful" within productive environments, SO think twice before you use it ;-)
The NOLOGGING options is a very powerful "switch" considering time/performance issues, BUT it may be quite "harmful" within productive environments, SO think twice before you use it ;-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER