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...

> 

>

>

> 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:

> 

>

>

> 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...[vbcol=seagreen]

> Richard Foote wrote: 

the[vbcol=seagreen] 

it 

>

> 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