Re: Analysing Tables and Indexes

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 15 Mar 2001 20:18:11 +0100
Message-ID: <tb257jrjl04q37@beta-news.demon.nl>

""Anurag Minocha"" <aminocha_at_herold.com> wrote in message news:EBBA6D3A81229C42981E3AEA79BF5A4E11123D_at_jshemail.herold.com...
> Hi,
> How frequentlly should one analyse the tables and indexes of a database in
> which where all tables are cleared (all rows deleted ) and populated
again.
>
> The following steps are taken
>
> - Indexes Dropped
> -All records from all tables deleted.
> -Indexes recreated.
>
> Since the indexes are being recreated do they need to be analysed so that
> CBO takes the best path.
>
> Please Help.
>
> Thanks
> Anurag
>
>
> --
> Posted from sjc3-1.relay.mail.uu.net [199.171.54.122]
> via Mailgate.ORG Server - http://www.Mailgate.ORG

First of all, if you are deleting *all* records, you should TRUNCATE that table, instead of delete. Otherwise the HighWaterMark will not be reset and performance will suffer severely.
And yes, you should analyze your *schema* (as opposed to your indexes only) by issuing
begin
dbms_utility.analyze_schema('<SCHEMANAME>','COMPUTE'); end;

and as your database seems to be volatile, you should repeat this action frequently, say once a week.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Mar 15 2001 - 13:18:11 CST