Re: Is this Needed?

There is something else that may benefit, namely tables that are used for queues and that had their high-water mark bumped much too high at a given moment. But copying their contents elsewhere, truncating and re-inserting the rows definitely don't require reorganizing the full database ... I agree with the general feeling: waste of time. Great opportunity to say "told you so".

Stephane Faroult

On Wed, 2005-10-26 at 16:23 +0100, stephen booth wrote:
> On 26/10/05, Freeman, Donald <dofreeman_at_state.pa.us> wrote:
> > I just received a notice from our technical team leader that we were going to schedule a 'refresh' on our transactional database in order to improve performance. They are going to truncate and reimport the production schema. Is this technique useful for a 9i database? I'm thinking that in earlier versions of Oracle it might have been helpful but on the other hand I don't see any discussion of this on any board that I am monitoring. Is this a waste of time?
>
> The only thing I can think of that that could do that might help
> performance (emphasis on might) is if you have a lot of migrated rows
> (rows that started out in one block and latrer moved to another block
> as a result of being updated) or a lot of deletions of indexed rows or
> updates of indexed fields resulting in lots of dead keys. As I recall
> from training, unless it's another myth taught as fact on courses,
> either of these could cause unnecessary physical IOs.
>
> If you are having problems with migrated rows and dead keys then your
> time might be better spent on a a few 'alter table..move'/'alter
> index...rebuild' and trying to minimise reoccurances.
>
> Stephen
>
> --
> It's better to ask a silly question than to make a silly assumption.
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l