Re: how to deal with this situation? truncate table + statistics

Hello,

Although the table is empty with:

  dbms_stats.set_table_stats( user, 'TABLE', numrows => 50000, numblks => 10000 );
  dbms_stats.set_column_stats( user, 'TABLE', 'COLUMN', distcnt => 15000 );

you can "simulate" the number of rows and blocks you wish.

Bye

--
Enrique

On 9/13/05, The Human Fly <sjaffarhussain_at_gmail.com> wrote:


> Hello List,

> 

> Well, I have a little bit tricky or I can say confused situation. We

> have database for shares tradining. Beginning of every day, a couple

> of tables will be truncted  and we analyze the schema.

> The situation is, when we truncate and analzye those tables,

> statitiscs are updated as rum_rows=0, and blocks =0. The entire day,

> about 100,000 records comes in each of those truncated  tables. But,

> for the optimizer,  the data dictionary statistics provides nothing,

> when there are records coming every second, we dont re-analyze again,

> i.e. old statistics available to the optimizer.

> How can we deal with this situation? Is it better to collect the

> statistics before table get truncated, so that statistics available

> for those tables to help otpimizer? If so, lets say, if our num_rows

> says 100,000 and truncated the table, when records start coming in a

> table,  optimizer assumed that this table has 100,000 records, may be

> the empty table starts filling from 10 records and keep growing.?

> What is the best way to deal this?

> 

> --

> Best Regards,

> Syed Jaffar Hussain

> OCP 8i & 9i DBA,

> Banque Saudi Fransi,

> Saudi Arabia

> ----------------------------------------------------------------------------------------

> "It is your atittude, not your aptitude that determins your altitude."

> --

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

> 



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