alter index rebuild (NOT ONLINE)
OakRogbak_erPine@yahoo.com Kill the 2 trees in email address to reply
TABLESPACE INDX2;
in an Oracle 8.1.7.0.0 database if that table is be used (possible
DML)? Would an update statement fail or hang until the index is
finished moving and rebuilding?
I don't want to do the ONLINE rebuild until I patch my db due to the
1475310 bug?
Thanks!
Howard J. Rogers
Howard J. Rogers
An index rebuild (even the online kind) takes an exclusive lock on the
table. If DML is already happening on the table, the rebuild command itself
hangs. If the rebuild acquires the lock, then subsequent DML on the table
hangs.
The difference with the online rebuild is that having acquired the lock, it
then releases it, so the length of time the table is locked is kept to a
minimum... though it has to re-acquire the lock to complete the rebuild, so
there's another opportunity for locking and hanging. The problem with the
online rebuild (bugs aside) is that if it can't acquire the exclusive lock
in the first place (because of already-happening DML on the table) it hangs.
And then subsequent DML requests queue up behind the DDL command, appearing
to hang). So even the online rebuild is not terribly "online".
Hence the generic advice: indexes seldom need rebuilding. However you do it,
it's expensive, ruins concurrent access to the table, involves a lot of I/O
and is usually not needed in the first place.
Regards
HJR
OakRogbak_erPine@yahoo.com Kill the 2 trees in email address to reply
going to do it because developers keep building them in tablespaces
other than the index tablespace, so I just wanted to move them.
I have read many places that it is not a good idea to rebuild them,
and I believe that. It is odd though, that Oracle does recommend it:
---
on page 13-36 in my book "Enterprise DBA Part 2:Performance Tuning
Workshop" (That I got from a class at Oracle University), it says as
the first sentence, "You should regularly rebuild your indexes."
In my book "Oracle 9i DBA Handbook" (from Oracle Press), on page 149,
the last paragraph says "To reclaim the unusable space in an index,
you can use the ALTER INDEX REBUILD command. Schedule a batch job to
run periodically to rebuild the indexes on your most active tables."
---
Thanks for the clarification. These index rebuilds are very quick, so
I am not too worried if the application hangs for 2 seconds. I just
don't want things to crash or become corrupt.
Norman Dunbar
2 trees in email address to reply wrote:
<SNIP>
> I have read many places that it is not a good idea to rebuild them,
> and I believe that. It is odd though, that Oracle does recommend it:
> ---
<SNIP>
Oracle also recommend that you separate tables and indexes in different
tablespaces, for performance reasons, because the action of reading from
and index and then from a table is done simultaneously. It isn't.
This is a long running Oracle Myth which Howard has tried on numerous
occasions to put to death. People still believe it. It should be noted
that progress is being made as the 9i course manuals no longer mention
that there is a performance enhancement in separating them.
> In my book "Oracle 9i DBA Handbook" (from Oracle Press), on page 149,
> the last paragraph says "To reclaim the unusable space in an index,
> you can use the ALTER INDEX REBUILD command. Schedule a batch job to
> run periodically to rebuild the indexes on your most active tables."
Another myth - deleted space in an index is never reused. This too is
untrue and is easily demonstable. Howard has done so in this NG - have a
scan of Google Groups if you like for details.
The best book to find good info about indexes is the 'Beginning Oracle
Programming' book by Tom Kyte et al (and Howard J Rogers - who doesn't
like being an 'et al' !). Don't be mislead by the title as it covers the
inner workings of the database in some details.
Cheers,
Norm.
--
Delete the obvious bit from my email address to reply by email.
Yong Huang
message
news:pan.2003.10.31....@RE-MO-VE.BountifulSolutions.co.uk
> On Thu, 30 Oct 2003 09:24:23 -0800, OakRogba...@yahoo.com Kill the
> 2 trees in email address to reply wrote:
>
> <SNIP>
>
> > I have read many places that it is not a good idea to rebuild them,
> > and I believe that. It is odd though, that Oracle does recommend it:
> > ---
> <SNIP>
>
> Oracle also recommend that you separate tables and indexes in different
Hi, Norman,
As usual, sometimes we go too far in correcting myths and forget to
mention those exceptional cases. Let's say the original poster's
database is supposed to be read-only but analysts find incorrect
data (this does happen sometimes). Updates or deletes are run and
the database comes back to read-only. Then it may be worth
rebuilding some huge indexes to save disk space and index scan time.
Index rebuild is obviously needed if you want to move its tablespace
or after you set it unuseable for a big data change. Tom Kyte says
bitmap indexes need more rebuilds. That may be due to Bug 630244 (or
"Feature" 630244). Jonathan Lewis says this problem is even more so in
ASSM tablespaces.
Yong Huang
--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
OakRogbak_erPine@yahoo.com Kill the 2 trees in email address to reply
One-On-One book, then it's a definite must-read.
Thanks,
Roger
Norman Dunbar
> Hi, Norman,
Afternoon Yong,
> As usual, sometimes we go too far in correcting myths and forget to
> mention those exceptional cases.
Don't I know it !
> Let's say the original poster's
> database is supposed to be read-only but analysts find incorrect
> data (this does happen sometimes). Updates or deletes are run and
> the database comes back to read-only. Then it may be worth
> rebuilding some huge indexes to save disk space and index scan time.
True, but I would imagine that any deletions will be reused by insertions
or updates (to the indexed columns anyway) so maybe no rebuild is
required. On the other hand, if there were huge amounts of data deleted
then it is possible that complete index blocks were also emptied, in which
case, they will be added back to the freelist(s) - again, no rebuild
required. As ever, 'it depends' :o)
Tom Kyte claims only to have rebuilt 8 indexes in his career, so that's
really how often indexes *need* rebuilding.
Noons
indexes due to bugs...
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
"Norman Dunbar" <Nor...@RE-MO-VE.BountifulSolutions.co.uk> wrote in message
news:pan.2003.11.01....@RE-MO-VE.BountifulSolutions.co.uk...
Norman Dunbar
> Of course, he's not counting the corrupt
> indexes due to bugs...
Nice one Skippy :o)
Yong Huang
> > Let's say the original poster's
> > database is supposed to be read-only but analysts find incorrect
> > data (this does happen sometimes). Updates or deletes are run and
> > the database comes back to read-only. Then it may be worth
> > rebuilding some huge indexes to save disk space and index scan time.
>
> True, but I would imagine that any deletions will be reused by insertions
> or updates (to the indexed columns anyway) so maybe no rebuild is
> required. On the other hand, if there were huge amounts of data deleted
> then it is possible that complete index blocks were also emptied, in which
> case, they will be added back to the freelist(s) - again, no rebuild
> required. As ever, 'it depends' :o)
>
> Tom Kyte claims only to have rebuilt 8 indexes in his career, so that's
> really how often indexes *need* rebuilding.
Norman,
What you describe is quite correct. Tom Kyte has a very impressive
analogy. An index is like an ordinary person; if you force yourself to
lose fat, you'll get a little fatter back to what you are now.
Similarly, if you force an index to be unnaturally skinny (densely
packed), it'll still become plump as it is now.
The case I described is such that the next data load may come a few
weeks or even months down the road. Before that time, many users need
to query the data. In this case, it makes sense to force the index to
be "unnaturally" skinny by rebuilding or coalescing it, if I can. The
less data blocks for the index, the less disk I/O and more efficient
use of buffer cache. In practice, though, you need try at least once
to see if the index size really comes down.
Given that Tom has been in this business for so long, it's amazing he
still remembers the exact number of times he rebuilt indexes...
Yong
Norman Dunbar
Morning Yong,
> What you describe is quite correct. Tom Kyte has a very impressive
> analogy. An index is like an ordinary person; if you force yourself to
> lose fat, you'll get a little fatter back to what you are now.
> Similarly, if you force an index to be unnaturally skinny (densely
> packed), it'll still become plump as it is now.
Not me, I never go an a diet - my wife on the other hand ......
> The case I described is such that the next data load may come a few
> weeks or even months down the road. Before that time, many users need
> to query the data.
This is true, however, are the users complaining about very slow response
times - if not, then do you *really* need to be rebuilding indexes ? If
you do, then when do you have to do it - while your users are querying the
data or outside normal hours.
In the first case, *you* are causing the users response probles as querys
hang waiting for access to the table because your rebuild has taken out an
exclusive lock on it.
If it is outside normal hours, then you are missing out on life because
you are sitting in an office waiting for a rebuild to finish. And you
can't fire off a rebuild script and go home because it might fail and then
where will you be tomorrow when the users do start to complain?
> In this case, it makes sense to force the index to
> be "unnaturally" skinny by rebuilding or coalescing it, if I can. The
> less data blocks for the index, the less disk I/O and more efficient
> use of buffer cache. In practice, though, you need try at least once
> to see if the index size really comes down.
Not unless you have a performance problem.
> Given that Tom has been in this business for so long, it's amazing he
> still remembers the exact number of times he rebuilt indexes...
Well. I can see how he would remember if he has only had to do it
infrequently. I've only ever *had* to rebuild an index twice in 7 years
myself. It was based on a sequence number and had a lot of deletions - but
not enough to free up entire blocks for re-use. I've been asked on
numerous occasions by developers to rebuild indexes to help with
performance - it didn't, the problem was in the SQL being used :o)
I suspect we won't agree on this one, but I say 'never rebuild indexes
unless you really have to'.
Cheers,
Norm.
>
> Yong
Jonathan Lewis
If no-one's mentioned it in this thread so far,
here's my comment:
http://www.dbazine.com/jlewis14.shtml
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Norman Dunbar" <Nor...@RE-MO-VE.BountifulSolutions.co.uk> wrote in
message
news:pan.2003.11.03....@RE-MO-VE.BountifulSolutions.co.uk.
..
Daniel Morgan
Management Secrects". I wonder if the book contains a single page with a single sentence
paragraph ... "Build them correctly and then leave them alone." Probably not. ;-)
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damo...@x.washington.edu (replace 'x' with a 'u' to reply)