RE: analyze after truncate

I once took over an old database which hadn't been analyzed in nearly a year, so I started analyzing it every two weeks. For the most part things stayed the same or got faster; however, one procedure would sometimes, only sometimes, take a lot longer. Come to find out there was a large 'work' table to hold data for reports. Sometimes this table would have a few hundred thousand records in it and other times it would be empty. If I happen to kick off the analyze when the table was empty, the CBO would not use indexes even though the table was now populated with a lot of records. The Rule hint helped but still not perfect as this table had many indexes and the data was used different ways. In our case, having the table analyzed after populating the table as part of the procedure worked best.

Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: sat0789_at_fastmail.fm [SMTP:sat0789_at_fastmail.fm]
>
> Hello All,
> We have a huge table both is terms of number of columns and rows. It is
> list partitioned. it has 7 mill rows and its avg row length is 600
> bytes. One of the developers has come up with a method of truncating
> and reloading the table on a daily basis thorough sqlloader. everyday
> there are about 50,000 rows insert and 100,000 rows update. My question
> is do i need to reanalyze this table on a daily basis. Even though we
> truncate the table the statistics still stays on with the table and
> since the table goes thorugh only minimal change, is it advisable to
> analyze it on a daily basis or can i analyze it on a weekly basis.
>
> Please advice..
>
> Thanks,
>
> Sat.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).