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.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]
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.
