Re: Table Statistics Without Analyzing

Hi Richard,

I created the "new" table and checked these numbers immediately after, so I know the table hadn't been analyzed yet.

Thanks for the thoughts though!

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<c5ei9.35676$g9.101778_at_newsfeeds.bigpond.com>...
> Hi Roy,
>
> CTAS does not generate statistics for a table automatically (that I know
> of ) ?
>
> Therefore someone must have generated these statistics (perhaps globally via
> dbms_stats.gather_schema_stats ?).
>
> Sorry I can't help :(
>
> Richard
> "Roy" <rspeaker_at_yahoo.com> wrote in message
> news:498db9a0.0209181911.3147979_at_posting.google.com...
> > Hi All --
> >
> > consider the following scenario (row counts are approximate)
> >
> > table_a has 24,500,000 rows
> > recent analyze (compute) of table has num_rows=24,500,000 in
> > user_tables
> >
> > rename table_a to table_a_old
> >
> > create table_a as select * from table_a_old
> >
> > select count(*) from table_a returns 24,500,000
> > select count(*) from table_a_old returns 24,500,000
> >
> > select num_rows from user_tables
> > where table_name = 'table_a_old' returns 24,500,000
> >
> > select num_rows from user_tables
> > where table_name = 'table_a' returns 24,685,000
> >
> > close, but not exact
> >
> > I have not analyzed the "new" table_a, so I'm assuming that the
> > algorithm for "create table as select" does an analyze, similar to
> > import. Is this correct? If so, it must be doing an estimate? If so,
> > what %? If it doesn't do an analyze, how did user_tables get updated
> > with num_rows?
> >
> > Thanks!
> > Roy
Received on Fri Sep 20 2002 - 10:28:51 CDT