SEO

Are there supplied scripts, packages, or procedures that will find and
rebuild existing index's? What I am really looking for is a script to
be run to rebuild fragmented index's as needed.

"DiggidyMack69" <DiggidyMack69@hotmail.com> wrote in message
news:c86ce4f.0404300648.63277bc8@posting.google.co m...
> Are there supplied scripts, packages, or procedures that will find and
> rebuild existing index's? What I am really looking for is a script to
> be run to rebuild fragmented index's as needed.

Go to the download section at www.actoug.org.au, make what you can of the
PowerPoint presentation, wait for the accompanying whitepaper/book, read it
carefully too and hopefully you'll get the picture.

Cheers

Richard

DiggidyMack69 wrote:
> Are there supplied scripts, packages, or procedures that will find and
> rebuild existing index's? What I am really looking for is a script to
> be run to rebuild fragmented index's as needed.

Trust me, that's the last thing you want.

Leave indexes alone. They cope quite nicely on their own.

Regards
HJR

> Are there supplied scripts, packages, or procedures that will find and
> rebuild existing index's? What I am really looking for is a script to
> be run to rebuild fragmented index's as needed.

You sound so much like an Oracle 7 DBA! First take a look at
www.jlcomp.demon.co.uk/faq/table_frag.html, then take a look at the
referenced Steve Adam's paper if you want more details. The only time
that you should rebuild an index is if you notice that a specific SQL
statement is significantly slowed down by index fragmentation. This
probably won't ever be an issue if you use locally-managed tablespaces
(preferably with "automatic" extent management, as opposed to uniform
extent management).

HTH

Daniel

DiggidyMack69@hotmail.com (DiggidyMack69) wrote in message news:<c86ce4f.0404300648.63277bc8@posting.google.c om>...
> Are there supplied scripts, packages, or procedures that will find and
> rebuild existing index's? What I am really looking for is a script to
> be run to rebuild fragmented index's as needed.

And of course, if you don't believe these guys now, look at
http://metalink.oracle.com/metalink/..._id= 182699.1

Here's a bunch of handy scripts, but be sure you understand why you
run any you happen to run, some are elderly and have lost correctness:

http://metalink.oracle.com/metalink/..._id= 131704.1

Don't forget to post version info, it can make a difference.
http://members.cox.net/oracleunix/readme-cdos.htm

jg
--
@home.com
http://www.myrtlebeachonline.com/mld...cs/8369624.htm

Richard Foote wrote:
> "DiggidyMack69" <DiggidyMack69@hotmail.com> wrote in message
> news:c86ce4f.0404300648.63277bc8@posting.google.co m...
>
>>Are there supplied scripts, packages, or procedures that will find and
>>rebuild existing index's? What I am really looking for is a script to
>>be run to rebuild fragmented index's as needed.
>
>
> Go to the download section at www.actoug.org.au, make what you can of the
> PowerPoint presentation, wait for the accompanying whitepaper/book, read it
> carefully too and hopefully you'll get the picture.

Is there an emoticon for a big, BIG, BIIIIIIGGGG beaming smiley face.

After having read the first slides containing the delicious quotes from
Herr Burleson und seiner freunde, I had to go and bake a doughnut.

After that I had indigestion with your 50-50 leaf block claim. Jonathan
claims it's 50-50 *on average*, but not by volume, I think. Interesting.

However: I just wanna know why that Sharman guy gets all the invites and
I don't :-((

Regards
HJR

Howard J. Rogers wrote:
> Richard Foote wrote:
>
>> "DiggidyMack69" <DiggidyMack69@hotmail.com> wrote in message
>> news:c86ce4f.0404300648.63277bc8@posting.google.co m...
>>
>>> Are there supplied scripts, packages, or procedures that will find and
>>> rebuild existing index's? What I am really looking for is a script to
>>> be run to rebuild fragmented index's as needed.
>>
>>
>>
>> Go to the download section at www.actoug.org.au, make what you can of the
>> PowerPoint presentation, wait for the accompanying whitepaper/book,
>> read it
>> carefully too and hopefully you'll get the picture.
>
>
> Is there an emoticon for a big, BIG, BIIIIIIGGGG beaming smiley face.
>
> After having read the first slides containing the delicious quotes from
> Herr Burleson und seiner freunde, I had to go and bake a doughnut.
>
> After that I had indigestion with your 50-50 leaf block claim. Jonathan
> claims it's 50-50 *on average*, but not by volume, I think. Interesting.
>
> However: I just wanna know why that Sharman guy gets all the invites and
> I don't :-((
>
> Regards
> HJR


Actually, upon reflection, I think you've been a tad unfair with those
"experts" quotes in your presentation: where are the Oracle Corporation
quotes that suggest you should rebuild when del_lf_rows/lf_rows exceeds
15/20/25/30 percent?? Where is the recognition of the fact that Oracle's
OCP training courses have been pushing regular index rebuilds for yonks??

But really, it is quite an eye-opener to realise just how much rubbish
the ultimate self-proclaimed experts can spew forth... and be believed
for so long.

And (here's the sad part) still are.

OK... small bet. I bet that within one month, someone will post here
about the best way of determining which indexes need to be rebuilt.

Regards
HJR

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
news:409334fa$0$25007$afc38c87@news.optusnet.com.a u...
> Richard Foote wrote:
> >
> > Go to the download section at www.actoug.org.au, make what you can of
the
> > PowerPoint presentation, wait for the accompanying whitepaper/book, read
it
> > carefully too and hopefully you'll get the picture.
>
> Is there an emoticon for a big, BIG, BIIIIIIGGGG beaming smiley face.

Hi Howard,

I'm not sure. What about x 100 !!

>
> After having read the first slides containing the delicious quotes from
> Herr Burleson und seiner freunde, I had to go and bake a doughnut.

The difficult part was selecting which quotes to use, there are so many. I
didn't want to single out any one person and I wanted to highlight how so
many have got it so wrong. So I focused on those "experts" that seem to
publish a lot of "stuff" ...

>
> After that I had indigestion with your 50-50 leaf block claim. Jonathan
> claims it's 50-50 *on average*, but not by volume, I think. Interesting.

No, it's definitely by volume. I showed this in a thread here a while back
where after a leaf block split, the number of index entries in each block
varied but the amount of used space sat at the 50% mark in each block.

Look here:
http://groups.google.com/groups?q=g:...r=&ie=UTF-8&oe
=UTF-8&selm=ant%259.39604%24jM5.100537%40newsfeeds.bigp ond.com. where I
demonstrate my "volume" claim The white paper also has an example.

It's the same thread whereby Don Burleson showed his total ignorance of how
indexes work and kinda opened my eyes that he wasn't quite what he claimed.
Interestingly, almost the entire history of Don Burleson posts have been
removed from the google archives, there's hardly a one left. As an example,
all the posts in this thread where he made a fool of himself are no longer
there, although thankfully for prosperity sake, his "words of wisdom" can
still be found in among the posts of others. It's all very interesting don't
you think, although it's probably a good move from Don's point of view to
reduce the embarrassment of some of the stuff his written.

>
> However: I just wanna know why that Sharman guy gets all the invites and
> I don't :-((
>

A lot of it has to do with the fact he's a local lad who lives in the
neighbouring suburb from me !! However, our next User Group event is on 23
June so if your interested in conducting a presentation in front of 60 odd
folks and would like a lovely free lunch to boot, please let me know and
I'll grab you a spot no worries at all !!

Cheers

Richard

Note in-line.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar

"Howard J. Rogers" <hjr@dizwell.com> wrote in message
news:409334fa$0$25007$afc38c87@news.optusnet.com.a u...
>
> After that I had indigestion with your 50-50 leaf block claim. Jonathan
> claims it's 50-50 *on average*, but not by volume, I think. Interesting.
>

I'm not sure I've ever been precise in my wording about how the 50/50
works - by default the tests I've done to date have always had uniform
sized keys, so 50/50 by key count would tend to be 50/50 by volume,
so I've never gone beyond saying just "roughly 50/50".

This, of course, demonstrates, that for every test result and repeatable
test case, there is always an enhancement to the test and refinement of
understanding. (Richard's "50% by volume" is such an obvious detail
to check - after it's been pointed out !! - it's also an extremely practical
point to be aware of, some systems will have keys with lots of columns
with lots of nulls, leading to all sorts of odd variation in key length).

In fact, Steve Adams described a further detail to the split process when
he was doing the Miracle Masterclass in Denmark in 2003, the preliminary
target is a 'fair' split - which Richard now tells us is 50/50 by volume -
but
Oracle will then allow a little side-stepping on the split to mimimise the
size
of the necessary entries in the branch blocks.

"Richard Foote" <richard.foote@tbigpond.nospam.com> wrote in message
news:yUIkc.6619$TT.2282@news-server.bigpond.net.au...


> many have got it so wrong. So I focused on those "experts" that seem to
> publish a lot of "stuff" ...


you mean the quantity of books published is not in direct
proportion to expertise? Now now, next you're gonna tell us that
BCHR is not the most performance gauge...



> No, it's definitely by volume. I showed this in a thread here a while back
> where after a leaf block split, the number of index entries in each block
> varied but the amount of used space sat at the 50% mark in each block.


Great prezzie, Richard. Excellent info. I do particularly like the point
you raise about clustering the table data, not the index! I've been advising
people to load their data in physical sequence of range scan keys for years
and have had tremendous performance improvements from just physically
sorting data in tables where range scans are involved. Now I know precisely
the reason why.

After reading it, I've got a question for you if I may:

Let's assume a situation where we have a root block, three branch blocks
and a bucketload of leaf blocks, like in your slide #22.
Assume as well that this index was created on a sequence-generated
column (regular but not necessarily consecutive increase). As such in the
pictorial representation the index leaf blocks would be organised left to
right in increase value of the key.

Now let's look at the physical (in the disk) distribution of this thing.
Presumably the root block and the first branch and a few leaf blocks
would be on consecutive blocks on the disk partition - assuming a raw
disk, an "intelligent" file system might do other things...
Followed by another branch block and more leaf blocks,then another branch
block and more leaf blocks. Correct assumption?

If so, then let's assume that later interspersed insertions of new keys
cause the third block from the left to split. Like you say in your slides,
no problemo: the new block is allocated from index freelist, the initial
block gets "emptied" 50% into the new one and we do not get an increase in
index level. Fine and dandy.

But now consider the physical distribution. Isn't that new block coming
from the freelist? Which might be pointing to a first free block on "the
other side of the disk", so to speak. This block will now LOGICALLY be
between the original third and fourth leaf blocks, but physically very
remote from these two original blocks.

You know where I'm getting at now, don't you? If someone is now doing an
index range scan - which for the sake of example will involve the third,
new fourth and old fourth leaf blocks using the block-to-block link
pointers, wouldn't it involve a heap of I/O wait while the arm got
repositioned to get the new fourth block from "the other side of the disk"?

Wouldn't that be a case for an index rebuild, based purely on physical
distribution rather than hazy "broken b-tree" concepts? What would be
a way of finding out if this was indeed the case, apart from the obvious
system I/O wait queue increasing in this device?

Thanks in advance for any feedback.
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam