Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Indexes and tablespaces
Hello,
I have a partitioned historical table in tablespace TS1 with 8 local
indexes.
I'm not sure how is better to:
- put the eight indexes in separate tablespaces (with small extents) or
- create just one big tablespace (with larger extents) for all indexes.
Other concerns include:
- a formula to calculate extents for indexes, tablespaces and data files
associated.
- is there a tool to help?
Thanks, alex.
Below the definitions for table and indexes (don't laugh too much!), using just one big tablespace UC_OP_TSIX_200103.
CREATE TABLESPACE UC_OP_TS_200103
DATAFILE
'D:\ORANT\DATABASE\UC_OP_TS_200103_1.ORA' SIZE 512M AUTOEXTEND ON NEXT
512M
, 'D:\ORANT\DATABASE\UC_OP_TS_200103_2.ORA' SIZE 512M AUTOEXTEND ON NEXT
512M
SIZE 1024M AUTOEXTEND ON NEXT 1024M;
CREATE TABLESPACE UC_OP_TSIX_200103
DATAFILE 'D:\ORANT\DATABASE\UC_OP_TSIX_200103_1.ORA' SIZE 512M AUTOEXTEND
ON NEXT 512M
, DATAFILE 'D:\ORANT\DATABASE\UC_OP_TSIX_200103_2.ORA' SIZE 512M AUTOEXTEND
ON NEXT 512M
, DATAFILE 'D:\ORANT\DATABASE\UC_OP_TSIX_200103_3.ORA' SIZE 512M AUTOEXTEND
ON NEXT 512M
SIZE 512M AUTOEXTEND ON NEXT 512M;
CREATE TABLE OPERAZIONE (
ID NUMBER(13) NOT NULL, ABI VARCHAR2(5) NULL, CAB VARCHAR2(5) NULL, PERSONALE_MATRICOLA VARCHAR2(10) NOT NULL, DIPENDENZA NUMBER(5) NOT NULL, TERMINALE NUMBER(3) NOT NULL, NUMERO NUMBER(7) NOT NULL, DATA_CONTABILE DATE NOT NULL, DATA_ DATE NOT NULL, DATA_DISPONIBILITA DATE NULL, DATA_VALUTA DATE NULL, COD_TRANSAZIONE VARCHAR2(8) NULL, DIVISA_UIC NUMBER(3) NOT NULL, IMPORTO NUMBER(18,3) NOT NULL, TIPO VARCHAR2(1) NOT NULL, NUM_RIFERIMENTO NUMBER(10) NULL, COD_CAUSALE NUMBER(3) NULL, CONTO_DIPENDENZA NUMBER(5) NULL, CONTO_CATEGORIA NUMBER(3) NULL, CONTO_NUMERO NUMBER(7) NULL, DESCRIZIONE VARCHAR2(50) NULL, DATA_IMPORTAZIONE DATE NOT NULL, BUSTA_ID NUMBER(11) NULL, IMMAGINE_ID NUMBER(11) NULL)
CREATE INDEX OPERAZIONE_IX_ID ON OPERAZIONE
(
ID ASC
)
LOCAL
(
PARTITION OP_P_200103 TABLESPACE UC_OP_TSIX_200103
PCTFREE 5 PCTUSED 60
STORAGE ( INITIAL 1024M NEXT 1024M MAXEXTENTS UNLIMITED PCTINCREASE 0)
);
CREATE INDEX OPERAZIONE_IX_BUSTA_ID ON OPERAZIONE
(
BUSTA_ID ASC)
CREATE INDEX OPERAZIONE_IX_IMMAGINE_ID ON OPERAZIONE
(
IMMAGINE_ID ASC)
CREATE INDEX OPERAZIONE_IX_DIPENDENZA ON OPERAZIONE
(
DATA_ ASC, DIPENDENZA ASC, PERSONALE_MATRICOLA ASC)
CREATE INDEX OPERAZIONE_IX_IMPORTO ON OPERAZIONE
(
DATA_ ASC, DIPENDENZA ASC, IMPORTO ASC)
CREATE INDEX OPERAZIONE_IX_CONTO ON OPERAZIONE
(
CONTO_NUMERO ASC, CONTO_DIPENDENZA ASC, CONTO_CATEGORIA ASC)
CREATE INDEX OPERAZIONE_IX_RIFERIMENTO ON OPERAZIONE
(
DATA_ ASC, NUM_RIFERIMENTO ASC)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Berindei Alex INET: Alex.Berindei_at_elsag.it Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Mar 20 2001 - 18:01:13 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).