-
Performance drag on alter index coalesce?
What's the performance drag on "alter index R393k3h4 coalesce;"?
R393k3h4 has 40 gig of sequential values and at least 1/2 of the keys have been deleted. I don't want to rebuild, just make the space reusable.
Ken
-
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
not sure if coalesce can improve performance but I guess it will lead to less index splits since there are freespaces for new index entries
-
Good question abhaysk.
Rebuilding the index isn't looking like our best option because:
1. The key is too big to rebuild online.
2. Downtime on this table has a suprisingly direct effect on stock price, happy users and my resume.
Seriously, after a recent archival process, I have many sparsely populated index blocks and I understand that coalesce will merge these and free up empty blocks for (sequential index) re-use.
I'm just curios how resource demanding coalesce will be.
I'm also curious if I can stop the process and restart it later.
-Ken
-
-
Pando,
I'm not having a problem with index splits because this is a sequential index. My indexes are simply growing into new blocks.
I'm most interested in re-using sparcely populated blocks.
Guess you posted before getting a chance to read the "key too big" reason for not rebuilding online.
Yea, my first impulse was to rebuild online to a new tablespace.
-Ken
-
rebuild online may not be advisible here.. due to 2 reasons
* online rebuild triggers FTS.. and since you say ur index if of the order 40GB, i suspect your table might be much bigger.. may be of order 150GB?
* online rebuild will have to record all the changes in the table during rebuild and may choke up ur DMLs..
Rebuild parallelly would be good option, as oracle reads existing index with FFS option. But draw back is it will hold locks for any DMLs and has to wait until rebuild completes.. You can rebuild when there is minimal DML activity on this table.. or better when there is no DML activity.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Abhay, Yea, all that, and it's not possible because the key is bigger than the block size.
OK, let's just agree that I'm not going to rebuild. And let's assume that I wanted to, say, "alter index .. coalesce".
How would you characterize general system load on this operation?
A. As intense as a rebuild.
B. Negligible. 5 Minutes max. Run it anytime.
C. Moderate, an index that size should coalesce in a few hours.
D. None of the above (please explain).
Ken
-
Originally posted by KenEwald
Pando,
I'm not having a problem with index splits because this is a sequential index. My indexes are simply growing into new blocks.
I'm most interested in re-using sparcely populated blocks.
Guess you posted before getting a chance to read the "key too big" reason for not rebuilding online.
Yea, my first impulse was to rebuild online to a new tablespace.
-Ken
if your index is sequential, i.e increase only then I dont think coalesce can make you reuse the index blocks since coalsce only merges leaf blocks in same branch
-
Pando,
Sorry, Maybe I didn't mention that I recently removed about 1/2 of the keys and left the index looking like the teeth of a back-woods southern lawyer.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|