Re: num of blocks containing table data

The ROWID is composed of a file number and a block number(note, the format of the ROWID changed in Oracle 8). Count distinct will return the number of blocks that contain at least one row. Row chaining could add to the number of blocks that contain data, but a properly maintained table shouldn't have a lot of this.

Jurij Modic wrote:

> On Fri, 12 Jun 1998 10:50:26 GMT, "Jonathan Lewis"
> <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> >
> >Why do you want this figure though, since it doesn't appear to be a
> >meaningful number. For instance, if you want the number which is:
> >'the minimum number of blocks which I could recreate the table in'
> >then look at:
> > (average row size + row overhead) x row count.
>
> To me it doesn't appear to be totaly meaningless number. For example,
> if you want to monitor how table's blocks are filled during the life
> of application to verify the correctness of the PCTFREE/PCTUSED
> settings, you could compare the numbers from your formula, from HWM
> and from the occupied block count to decide if it is rational to
> rebuild your table (perhaps with different storage parameters).
>
> Of course, I can estimate (probably with acceptable accuracy) the
> number of occupied blocks, but I was simply surprised to find out that
> there seems to be no method to exactly determine this number in all
> circumstances. At all the various statistics that Oracle is offering
> it realy surprises me that I can not determine such a basic data.
>
> But to be honest, it is realy only my curiosity.
>
> >Jonathan Lewis
>
> Regards,
> ============================================================
> Jurij Modic Republic of Slovenia
> jurij.modic_at_mf.sigov.mail.si Ministry of Finance
> ============================================================
> The above opinions are mine and do not represent any official
> standpoints of my employer
Received on Mon Jun 29 1998 - 18:04:33 CDT