Re: Help for newbie - performance
From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/03/19
Message-ID: <5go146$lkt_at_mtinsc04.worldnet.att.net>#1/1
"C_MCKINNON" <C_MCKINNON_at_colloquium.co.uk> wrote:
>I've just taken over responsibility for an Oracle 7 system running on Unix.
>Performance has taken a bit of nose dive in recent months. The amount of
>data has not increased dramatically. I've tried killing off non-essential
>processes on the server, and am now wondering if rebuilding the indexes
>would help - apparrently this was done on the system a couple of years ago
>to good effect. Despite what it says in the Oracle manuals, index
>performance will degrade as the amount of data grows. Specifically, as the
>number of leafs in an index increases and the logical order of leafs
>becomes increasingly out of sync with the physical order.
>
>Is there any way of re-compiling/optimizing the existing indexes without
>dropping & re-creating them?
>
>Cheers,
>
>Colin
Received on Wed Mar 19 1997 - 00:00:00 CET
Date: 1997/03/19
Message-ID: <5go146$lkt_at_mtinsc04.worldnet.att.net>#1/1
If you are not using version 7.3, you are out of luck as far as recreating indexes without dropping them first. V7.3 allows you to "rebuild" an index to change its storage parameters using the
ALTER INDEX <index_name> REBUILD [UNRECOVERABLE]... command.
The index is rebuilt very quickly (more so if you use the UNRECOVERABLE clause) using the data from the existing index.
I hope this helps.
Michael Serbanescu
Senior Consultant, MIACO Corporation
"C_MCKINNON" <C_MCKINNON_at_colloquium.co.uk> wrote:
>I've just taken over responsibility for an Oracle 7 system running on Unix.
>Performance has taken a bit of nose dive in recent months. The amount of
>data has not increased dramatically. I've tried killing off non-essential
>processes on the server, and am now wondering if rebuilding the indexes
>would help - apparrently this was done on the system a couple of years ago
>to good effect. Despite what it says in the Oracle manuals, index
>performance will degrade as the amount of data grows. Specifically, as the
>number of leafs in an index increases and the logical order of leafs
>becomes increasingly out of sync with the physical order.
>
>Is there any way of re-compiling/optimizing the existing indexes without
>dropping & re-creating them?
>
>Cheers,
>
>Colin
Received on Wed Mar 19 1997 - 00:00:00 CET