Re: Collecting statistics for large established database

"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news:135bad08-1a1b-4e45-bc04-bf5a2446a9ca_at_q40g2000prh.googlegroups.com...
> Let's say we have a relatively large database (2 TB or more). It is
> being used by an application that went live more than 3 years ago, so
> historic (transactional) tables contain more than 1000 days worth of
> data. There has been no changes to the table structure, i.e. all
> tables are the same as 3 years ago. The tables are not partitioned.
> This is classic OLTP application where most tables get populated by
> online users (call centre staff). One year we may have 1000 people in
> call centres, another year we may have 2000, but we are not going to
> have one million. So transactional tables experience linear growth.
>
> Question: why do we need to collect table/index statistics on a
> regular basis (i.e. daily or weekly?) At the end of the day the
> optimizer does not care about number of records in a table, only
> relative values are important. Let's say we have table A with
> 1,000,000 rows and table B with 1,000 rows. One year later table A
> may have 1,500,000 rows and table B may have 2,000 rows. It shouln't
> have any impact on the plan. Yes it is possible that as a result of
> table growth a different plan may become slightly more efficient, but
> in real-life scenario plan stability is more important.
>

<snip>

Statistics collect a lot more than just the number of rows in a table... But if you don't like them, you could always use the RULE-based optimizer :-)

Matthias Received on Wed Aug 19 2009 - 14:37:59 CDT