Re: Where are these table stats coming from?

I remember on 8.1.x using "comp stats" on an index rebuild also did
the table (unless that table was partitioned...)

In 9.2 (on this solaris system) I got the following

SQL> create table T ( x number );

Table created.

SQL> create index TX on T ( x );

Index created.

SQL> insert into T select rownum from all_objects
  2  where rownum < 10;

9 rows created.

SQL> alter index TX rebuild compute statistics;

Index altered.

SQL> @ind
Enter value for table_name: T

TABLE_NAME            INDEX_NAME             DISTINCT_KEYS   NUM_ROWS
--------------------- --------------------   ------------- ----------
T                     TX                                 9          9

SQL> @tab
Enter value for table_name: T

TABLE_NAME          NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
----------------- ---------- ---------- ------------ -----------
T                          9          1            0         100

SQL> @tabcol
Enter value for table_name: t

COLUMN_NAME         NUM_DISTINCT    DENSITY AVG_COL_LEN  NUM_NULLS
------------------- ------------ ---------- ----------- ----------
X                              9 .111111111           2          0

(on an unrelated note - how do we post in monospaced font ... does <pre> wo=
rk ?)

hth
connor=20

On 6/9/05, Barbara Baker <barb.baker@xxxxxxxxx> wrote:
> Ok, I'll start running some tests.
> (I was originally wondering if it was doing a full compute on the
> table for each index rebuild.  This would account for why it's taking
> so much time to rebuild the indexes.  I'm now less inclined to think
> that is what's happening.  I think it's just updating a couple of
> fields in the user_tables view.)
>=20
> thanks so much for your response!
>=20
>=20
> On 6/8/05, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> > Barbara,
> >=3D20
> > AVG_ROW_LEN =3D3D 100 is the default. BLOCKS is gotten out of the segme=
nt
> > header for the table and this is accurate, and I suppose gathering inde=
x
> > statistics requires the number of rows in the table - it is after all
> > one of the index statistics - so it seems that "compute statistics" on
> > index builds and rebuilds updates the table stats while it's at it. In
> > that it behaves consistent with gathering histograms using dbms_stats.
> > It too update the num_rows table statistics (and perhaps blocks, I
> > haven't checked that).
> > What's left to verify is if it will override existing table statistics.
> >=3D20
> > Barbara Baker wrote:
> > > Wolfgang:  Interesting!  I should have looked
> > > Yes, both are filled in. But the value for avg row length is WAY off.
> > > Should be  about 900.
> > >
> > > NUM_ROWS                      : 16233354
> > > BLOCKS                        : 3250702
> > > EMPTY_BLOCKS                  : 0
> > > AVG_SPACE                     : 0
> > > CHAIN_CNT                     : 0
> > > AVG_ROW_LEN                   : 100
> > --
> > Regards
> >=3D20
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > www.centrexcc.com
> >
> --
> http://www.freelists.org/webpage/oracle-l
>=20


--=20
Connor McDonald
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
email: connor_mcdonald@xxxxxxxxx
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"
--
http://www.freelists.org/webpage/oracle-l

Other related posts: