Home » RDBMS Server » Server Administration » Why do they know about the space of leaf block?
Why do they know about the space of leaf block? [message #272829] Sun, 07 October 2007 23:21 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear all!

http://i95.photobucket.com/albums/l130/trantuananh24hg/Coalescing_index1.gif

This image above describes the cost and benefit of coalescing index method. However, I wonder about the space of leaf blocks was merged to free up which is estimated or calculated by 50% of the first and the second one. The behind B-Tree Index was coalesced, and I knew.

According to the dba_indexes data dictionary, I tried to find the formula to calculate them, but I did not.

Can you explain to me?

Thank you very much!

[Updated on: Sun, 07 October 2007 23:22]

Report message to a moderator

Re: Why do they know about the space of leaf block? [message #272830 is a reply to message #272829] Sun, 07 October 2007 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Can you explain to me?

Some folks say that rebuilding indexes is a Good Thing (TM).
Some folks say that rebuilding indexes is a waste of time & CPU cycles.

Which tastes better Coke or Pepsi?

How will you know which response is actually the correct one?
Re: Why do they know about the space of leaf block? [message #272832 is a reply to message #272830] Sun, 07 October 2007 23:48 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
@anna!
First time, I wish the best to you, and thank you for your pleasant comparing between Coke and Pesi, but I'd like to drink fruit.

Of course, the cost and benefit between Rebuild and Coalesce Index are different, then I know about them.

Assuming I has only one storage which contained my datafile, and only one tape to backup, it's absolutely to consider about the indexes, especially large index. So, when using Rebuild Index, I may abide by Oracle Reference

Quote:


Higher cost: Require more disk space



Using Rebuild Index method, I have got effective one..

Quote:


Can change storage and tablespace parameters without having drop original index.



However, I am only a newbie on Oracle environment, like a child gets inquisitive everything around him, when I saw this image, I were surprise and asked myself : "How do they calculate exactly the leaf block space and show it in?". Is there any formula to guess, estimate or do anything else to generate result.

Again, thank you, Ms anna, both of your answer and your article named as "Drop OEM to avoid Database hang".

[Updated on: Sun, 07 October 2007 23:49]

Report message to a moderator

Re: Why do they know about the space of leaf block? [message #272834 is a reply to message #272829] Mon, 08 October 2007 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Is there any formula to guess, estimate or do anything else to generate result.
IMO, there is no such absolute, because IMO it all depends.

Here my reality, if there were ANY absolute tuning truisms, then they could be included in TUNING program.

Since neither Oracle nor any expert has produced a MAGICAL tuning program, then maybe there is NO silver tuning bullet exists.
Re: Why do they know about the space of leaf block? [message #272844 is a reply to message #272834] Mon, 08 October 2007 00:49 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeah!
I understand your implication answer!

Thanks a lot!
Re: Why do they know about the space of leaf block? [message #273099 is a reply to message #272844] Tue, 09 October 2007 01:53 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear!

I have read the Index document below, however, I've not understood about somethings like that "50-50 split block", "90-10 split".. and how did he found the index header section.

Would you like to teach me more?

Additional question: In your situation, how do you identify which indexes will be rebuilt or coalesce?

Thanks for your reply!

[Updated on: Tue, 09 October 2007 02:01]

Report message to a moderator

Re: Why do they know about the space of leaf block? [message #273117 is a reply to message #273099] Tue, 09 October 2007 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't read the document but I can answer the following question:
Quote:

Additional question: In your situation, how do you identify which indexes will be rebuilt or coalesce?

None unless you have a good proof of bad performances resulting from this index.

Regards
Michel
Re: Why do they know about the space of leaf block? [message #273290 is a reply to message #273117] Tue, 09 October 2007 21:11 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you, Michael!

Previous Topic: Session Memory Usage
Next Topic: Split/Link from one DBF to another new DBF
Goto Forum:
  


Current Time: Wed Dec 07 14:37:47 CST 2016

Total time taken to generate the page: 0.08519 seconds