| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Table Statistics Without Analyzing
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
EMP 31067FLIESTO
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...Received on Fri Sep 20 2002 - 14:02:35 CDT
> 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
 |
 |