Re: Where are these table stats coming from?

From: Connor McDonald <mcdonald.connor_at_gmail.com>
Date: Thu, 9 Jun 2005 08:46:32 +0800
Message-ID: <5e304862050608174635ed84c3@mail.gmail.com>

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_at_gmail.com> 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_at_centrexcc.com> 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_at_yahoo.com
web: http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 08 2005 - 20:51:32 CDT