Ah, but according to your mentors, only one "proof" case is necessary to infer how something works . . .takes one example and comes up with generic recommendations, based on one isolated case.
And please don't insinuate that you are a competant DBA.Please don't mistake exposure for quality.
It depends on the system!under normal situation, we have very rare chance to have massive delete, don't we?
Sure, Metalink has errors, but it's still the "official" rules for what is allowed and what is not allowed. I've seen shops get de-supported for not following the advice on MetaLink. (e.g. advice not to use some 3rd party tools, etc.)."It's written in metalink... So i have no fault"
But don't take my word for it, let's see whatDon,
Jonathan says about you:
"Sybrand, Your manners and your lack of care leave
much to be desired . . .
You quoted the first line, and commented "utter
rubbish" - presumably having failed to bother to go
on to the later lines of text. . .
You side-stepped the point, changed the question, and
redefined the term DDL to mean 'the components',
pointed out that you can save the DDL in a script,
and said you didn't see the problem.
"I am allowed to give you the wrong answer because I
don't see the problem and I'm not paying attention"
is NOT a valid comment. . .
Next time you decide to be offensive, please wait 24
before hitting the <Send> key.
How can I tell if my Db indexes require rebuilding.. when should I rebuild.. ?HoLy_PiLgRiM
MetaLink and the docs are the "official" word fromPoint though is Don, metalink documents can be erroneous and are being corrected all the time ...
Oracle, right? When I quote my notes, people
chastise me for NOT citing the docs! I can't win!
However, deletes are a different story. Physically,When a leaf page contains nothing but deleted entries, the leaf block is placed on the freelist and can be subsequently recycled. Therefore, if the insert rate approximates the delete rate and deletes "empty" leaf blocks, the index remains both physically and logically balanced ...
Oracle indexes are always balanced because empty
blocks stay inside the tree structure after a massive
delete. Logically, Oracle indexes are not
self-balancing because Oracle does not remove the
dead blocks as they become empty.
This type of “sparse” index is typical of an index onIt's actually very untypical because you don't appear to understand how deleted entries are cleaned out and blocks recycled by Oracle ...
highly-active tables with large-scale inserts,
deletes and updates. We may have thousands of empty
or near-empty index blocks, and several Oracle
execution plans will run longer on this type of
Excellent question. The benefits of large blocksizesIs that the same thread where you thought the example was based on the guys actual experience but he was simply quoting from our Russian mate on the Ask Tom website that had absolutely no technical merit and that you eventually agreed by stating "Yeah, I redacted that one. " in this thread
are demonstrated on this OTN thread where we see a
demo showing 3x faster performance using a larger
Also, Robin Schumacher has proved that indexes buildYou always, always always always, come back to poor Robin's example. The fact it's published in one of your books does rather explain a lot ...
differently in different blocksizes:
“As you can see, the amount of logical reads has been
reduced in half simply by using the new 16K
tablespace and accompanying 16K data cache.
Clearly, the benefits of properly using the new data
caches and multi-block tablespace feature of Oracle9i
and above are worth your investigation and trials in
your own database.“