Re: Opinions for this storage parameters.

2 block and 1 block extents seem very dubious.

I don;t myself these days use extents less than 64K. I use locally managed tablespaces. If I end up with s**tloads of extents, I move the table up to the next tablespace which has 256K extents. And from there to 1Mb extents and so on.

In short, read lots about sizing of tables and indexes.... and then forget it all. You don't need to worry about sizes in intricate detail unless you are desperately short of disk space (in which case, use Access not Oracle) or if you are using dictionary-managed tablespaces.

Regards
HJR "Fernando Ruza" <fruza_at_yahoo.com> wrote in message news:3d8dfca3.0207020210.6962a4be_at_posting.google.com...
> Hi,
>
> I'm creating all the objects for one application in an Oracle
> database. After reading a lot about sizing tables and indexes and at
> the end following the rules commented in the book "Oracle8i DBA
> Handbook" I have decided the following storage parameters for an
> static table.
>
> I just would like to know your opinions about the analysis to obtain
> the datas and if these parameters are ok for this kind of table.
>
>
> -- Table CLIENTES
> -----------------
> -- Database Block Size: 8192 bytes (8kb)
> -- PCTFREE: 5 % => 8102 * 0.05 = 405 bytes
> -- PCTUSED: PCTUSED + PCTFREE = 85 => PCTUSED = 80
> -- Average Row Length: 100 bytes (Get it with command ANALYZE)
> -- Number of expected rows: 50 - 100
> -- Block space available: 8192 - 90 (Fixed block header) = 8102 - 405
> = 7697 bytes
> -- Rows per Block: 7697 / 100 (bytes per row) = 76,97 ~ 77 rows.
> -- Number of Blocks: 100 (rows expected) / 77 (rows per Block) = 1,29
> ~ 2 Blocks
>
> -- Table CLIENTES
> CREATE TABLE CLIENTES
> (CODCLIENTE NUMBER NOT NULL,
> NOMBRECLIENTE VARCHAR2(30),
> RAZONSOCIAL VARCHAR2(50),
> CONTACTO VARCHAR2(50),
> DIRECCION VARCHAR2(50),
> CODIGOPOSTAL VARCHAR2(5),
> LOCALIDAD VARCHAR2(50),
> PROVINCIA VARCHAR2(50),
> PAIS VARCHAR2(50),
> CIF VARCHAR2(15),
> TFNO1 VARCHAR2(15),
> TFNO2 VARCHAR2(15),
> FAX VARCHAR2(15),
> FECHAALTA DATE,
> FECHABAJA DATE,
> OBSERVACIONES VARCHAR2(200)
> )
> TABLESPACE DATA
> STORAGE ( INITIAL 16384
> NEXT 16384
> MINEXTENTS 1
> MAXEXTENTS 2
> PCTINCREASE 1
> )
> PCTFREE 5
> PCTUSED 80;
>
> -- Primary Key of CLIENTES
> --------------------------
> -- Database Block Size: 8192 bytes (8kb)
> -- PCTFREE: 5 % => 8192 * 0.05 = 405 bytes
> -- Average index lenght: 26 bytes (Get it with SELECT
> AVG(NVL(VSIZE(Col1),0)) Avg_Row_Length FROM table_name;)
> -- Space used per row = Avg_Row_Length + Number of columns + Number
> of long columns + 8 header bytes.
> -- Number of expected rows: 50 - 100
> -- Block space available: 8192 - 161 (Fixed index block header) = 8031
> - 405 = 7626 bytes
> -- Rows per Block: 7626 / 26 (bytes per row) = 293,30 ~ 293 rows.
> -- Number of Block: 100 (rows expected) / 293 (rows per Block) = 1
>
> -- Creamos Primary Key
> ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES
> PRIMARY KEY (CODCLIENTE)
> USING INDEX
> TABLESPACE INDEXES
> STORAGE ( INITIAL 8192
> NEXT 8192
> MINEXTENTS 1
> MAXEXTENTS 1
> PCTINCREASE 1
> )
> PCTFREE 5;
>
>
> Thanks in advance for any reply,
>
> Fernando.
Received on Tue Jul 02 2002 - 12:15:27 CEST