about pct_free avg_space

Hi,
Oracle 9.2.0.6 on AIX 5.3

I have a problem that i don't understand

I have a table with these values :
PCTFREE 10
PCTUSED 40

And after alter table move and analyse compute stats

Name : PROCP
blocks : 11832
num_rows : 50008
avg_row_len : 998
avg_space : 3826
size (blocks*8) : 92.44Mo
size (num_rows*avg_row_len) : 47.6Mo

So why the blocks are not more used, they are ~50% Full ?

I have created a copy of this table with pctfree 0, here it seems correct to
me, the block are full
Name : PROCP_pctfree0
blocks : 6545
num_rows : 50008
avg_row_len : 998
avg_space : 410
size (blocks*8) : 51.13Mo
size (num_rows*avg_row_len) : 47.6Mo

and another one with pctfree 30
Name : PROCP_pctfree30
blocks : 12379
num_rows : 50008
avg_row_len : 998
avg_space : 4012
size (blocks*8) : 96.71Mo
size (num_rows*avg_row_len) : 47.6Mo


Any idea ?


ps: The segment management is manual, i have tested with auto and it's the
same values

thanks


.

Relevant Pages

  • Re: NOT NULL constrinat lost in VIEW after cast
    ... Puget Sound Oracle Users Groupwww.psoug.org ... PCTFREE 10 ... INITRANS 1 ... MAXTRANS 255 ...
    (comp.databases.oracle.misc)
  • Re: move table for chianed rows?
    ... for resolve chained rows on tables, it is enough move table in this ... You said " As it copies the rows it will repack the blocks up to> pctfree potentially resulting in the use of less space. ... There is a chapter in the Concepts manual that explains how Oracle ... But on a rebuild if the rows from this block ...
    (comp.databases.oracle.server)
  • Re: Ad-hoc/Dynamic SQL Performance
    ... PCTFREE 0; ... PRIMARY KEY (field1) ... The less free space there is within blocks, the more likely it is that a record insert will cause a block split, no? ... Oracle Ace Director & Instructor ...
    (comp.databases.oracle.server)
  • Re: How to delete STATSPACK data efficient to release back space?
    ... probably the statpack table is using the default table PCTFREE ... (Hi Connor, I have your book here in my office, Oracle Insight-> ... Tales of the Oak Table) ...
    (comp.databases.oracle.server)
  • Re: How to delete STATSPACK data efficient to release back space?
    ... probably the statpack table is using the default table PCTFREE ... (Hi Connor, I have your book here in my office, Oracle Insight-> ... Tales of the Oak Table) ...
    (comp.databases.oracle.server)