Re: Table Statistics Without Analyzing

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 Thu Sep 19 2002 - 01:36:26 CDT