Toad World® Forums

Is it true no more fragmentation?

I’m new to Oracle and our Sr. DBA told me there is no need to defrag an Oracle 10g/11g database because it uses bitmap technology for Locally managed files. I understand that for clustered tables but what about non-clustered indexes? He also said that Oracle 11g doesn’t want you to defrag, update statistic or integrity check the db. Is that true?

Hi Smitty,

I’m new to Oracle and our Sr. DBA told me there is no need to defrag an
Oracle 10g/11g database because it uses bitmap technology for Locally
managed files. I understand that for clustered tables but what about
non-clustered indexes? He also said that Oracle 11g doesn’t want you to
defrag, update statistic or integrity check the db. Is that true?

From 8i onwards, any tablespace that was Locall Managed uses bitmaps to
determine free space. No need to defrag.

You almost never ever ever ever (!) need to defrag an Oracle Index.
Regardless of what you may find on certain web sites to the contrary two
will be of most help - Ask Tom and Richard Foote’s Blog.

Oracle Indexes are balanced tree indexes (BTree) and not a Binary Tree
index. Confusion often arises on this matter.

The first thing that almost always happens when you rebuild/defrag an
index is that Oracle starts to fragment it again (block splits) simply
by using it.

Oracle indexes are efficient and well designed and tested. Don’t mess
with them!

As for your last question, I’m not 100% sure what you mean but since
Oracle 10g there is an automatically scheduled job that runs as SYS to
gather statistics for all tables etc. This was slightly broken in 10g
but as far as I’m aware pretty much acceptable in 11g onwards.

HTH


Cheers,
Norm. [TeamT]

Message from: smitty_046

Thanks for the response! I just needed to confirm what he said.


Historical Messages

Author: smitty_046
Date: Thu Jan 05 08:37:54 PST 2012
Thanks for the response! I just needed to confirm what he said.
__

Author: Norman Dunbar
Date: Tue Jan 03 08:47:43 PST 2012
Hi Smitty, > I’m new to Oracle and our Sr. DBA told me there is no need to
defrag an > Oracle 10g/11g database because it uses bitmap technology for
Locally > managed files. I understand that for clustered tables but what about >
non-clustered indexes? He also said that Oracle 11g doesn’t want you to >
defrag, update statistic or integrity check the db. Is that true? From 8i
onwards, any tablespace that was Locall Managed uses bitmaps to determine free
space. No need to defrag. You almost never ever ever ever (!) need to defrag an
Oracle Index. Regardless of what you may find on certain web sites to the
contrary two will be of most help - Ask Tom and Richard Foote’s Blog. Oracle
Indexes are balanced tree indexes (BTree) and not a Binary Tree index. Confusion
often arises on this matter. The first thing that almost always happens when you
rebuild/defrag an index is that Oracle starts to fragment it again (block
splits) simply by using it. Oracle indexes are efficient and well designed and
tested. Don’t mess with them! As for your last question, I’m not 100% sure what
you mean but since Oracle 10g there is an automatically scheduled job that runs
as SYS to gather statistics for all tables etc. This was slightly broken in 10g
but as far as I’m aware pretty much acceptable in 11g onwards. HTH – Cheers,
Norm. [TeamT]
__

Author: smitty_046
Date: Tue Jan 03 08:44:26 PST 2012
I’m new to Oracle and our Sr. DBA told me there is no need to defrag an
Oracle 10g/11g database because it uses bitmap technology for Locally managed
files. I understand that for clustered tables but what about non-clustered
indexes? He also said that Oracle 11g doesn’t want you to defrag, update
statistic or integrity check the db. Is that true?
__


Thanks for the response! I just needed to confirm what he said.