RE: how to deal with this situation? truncate table + statistics
- From: "Hallas, John, Tech Dev" <John.Hallas@xxxxxxxxxxxxxxxxx>
- To: <sjaffarhussain@xxxxxxxxx>, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Sep 2005 11:11:24 +0100
Syed,
I believe that if a table is not analyzed the optimiser defaults to
assume 100 rows. That is not what you want.
From what you are saying bar the first few minutes of trading there will
always be rows in your table and it will almost certainly more than 100.
What I suggest is that you go for the median (or average) point and
analyze your table at lunch-time one day when there should be about 50K
rows in leave the stats there as you truncate the table each morning.
You really need to experiment with different execution plans to see what
impact the various permutations of statistics have on your sql.
I don't know what version you are on but does 10G have anything that
allows a table to be analyzed without taking a lock out?
J
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of The Human Fly
Sent: 13 September 2005 10:31
To: Oracle-L Freelists
Subject: how to deal with this situation? truncate table + statistics
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."
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
