Re: Table Statistics Without Analyzing

Niall Litchfield wrote:
>
> on 9i a CTAS does NOT end up with a value in num_rows
>
> SQL> create table nl_test
> 2 as
> 3 select * from dba_objects;
>
> Table created.
>
> SQL> select table_name,num_rows
> 2 from user_tabes;
> from user_tabes
> *
> ERROR at line 2:
> ORA-00942: table or view does not exist
>
> SQL> ed
> Wrote file afiedt.buf
>
> 1 select table_name,num_rows
> 2* from user_tables
> SQL> /
>
> TABLE_NAME NUM_ROWS
> ------------------------------ ----------
> A
> AIRPORT
> B
> EMP 31067
> FLIESTO
> NL_TEST
> ORDERS
> T1
> T2
> T3
>
> 10 rows selected.
>
> and neither does a rename.
>
> I'm with richard unless you can give version and test case (or someone else
> can do similar).
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
> "Roy" <rspeaker_at_yahoo.com> wrote in message
> news:498db9a0.0209200728.21aca75b_at_posting.google.com...
> > 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

I'm dying to know secret object "user_tabes" is :-)

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."