Re: Table Statistics Without Analyzing

Jonathan,

this is version 7.3.4.4.0 on AIX.

Initially I was thinking as you are .. CTAS knows how many rows it just inserted. The thing that threw me was that the "new" table, that was created by CTAS and has not yet been analyzed, is the only one that does not have the correct value for num_rows.

Thanks.
Roy

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ambqvp$d32$1$830fa17d_at_news.demon.co.uk>...
> Which version of Oracle ?
>
> I haven't seen this mentioned - but all that takes is
> failing to read one line in document.
>
> Implementation would in theory be easy -
> CTAS "knows" how many rows it has created,
> and how many blocks used, so it is a
> small extra price to write the stats in to tab$.
>
> I'll have to try it.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______September 24/26, November 12/14
>
> ____USA__________November 7/9 (MI), 19/21 (TX)
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
>
>
>
> Roy wrote in message <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:24:42 CDT