| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Fragmentation
David Sisk wrote:
>
> Hi:
>
> If you've *standardized* your extent sizes, you'll get lots of space reuse
> on tables, so table fragmentation shouldn't be much of a problem. Space
> from deleted rows in indexes isn't reused until 8.1, so you will get index
> fragmentation. Luckily, you can easily fix this by running "ALTER INDEX
> indexname REBUILD tablespacename" on each affected index. Be sure to
> specify the tablespace name or it will rebuild on the default user's
> tablespace.
>
> Good luck!
>
> --
> David C. Sisk
> The Unofficial ORACLE on NT site
> http://www.ipass.net/~davesisk/oont.htm
>
> newsreader wrote in message ...
> >We are unsing Oracle 7.3.x and we are concerned about Fagmentation and
> >Performance.
> >
> >
> >Our Database will contain aproximitely 2 Million Sets of Data. Every week
> we
> >get additional 200.000 Data-Sets. We want to delete about 200.000 old Sets
> >every week too. Is there going to be an issue with Database Fragmentation
> >and Performance and how can we handle this.
> >
> >
> >
> >Any suggestions are greatly appreciated.
> >
> >
> >Wolfgang Niefert
> >
> >
Index space CAN be re-used (even way back in Oracle 7) as long as the entire block becomes free and is not in the same transaction...
For example, create a table with an increasing sequential number column have this column indexed...A commonly held belief is that this index will grow out of control even if we delete old rows...
But try the following:
loop
insert a 1000 new rows
commit
delete the oldest 1000 rows
commit
end loop
and you'll find that the old index blocks will be re-used...
leave the commits out and the index will grow...
CHeers
--
"Some days you're the pigeon, and some days you're the statue." Received on Mon Jul 19 1999 - 09:13:38 CDT
 |
 |