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 - 05:10:14 CDT
