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
)
PARTITION BY RANGE( DATA_ )
(
PARTITION OP_P_200103 VALUES LESS THAN(TO_DATE('01-04-2001
00:00:00','DD-MM-YYYY HH24:MI:SS'))
TABLESPACE UC_OP_TS_200103
PCTFREE 5 PCTUSED 60
STORAGE ( INITIAL 1024M NEXT 1024M MAXEXTENTS UNLIMITED PCTINCREASE 0)
);
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
)
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_IMMAGINE_ID ON OPERAZIONE
(
IMMAGINE_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_DIPENDENZA ON OPERAZIONE
(
DATA_ ASC,
DIPENDENZA ASC,
PERSONALE_MATRICOLA 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_IMPORTO ON OPERAZIONE
(
DATA_ ASC,
DIPENDENZA ASC,
IMPORTO 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_CONTO ON OPERAZIONE
(
CONTO_NUMERO ASC,
CONTO_DIPENDENZA ASC,
CONTO_CATEGORIA 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_RIFERIMENTO ON OPERAZIONE
(
DATA_ ASC,
NUM_RIFERIMENTO ASC
)
LOCAL
(
PARTITION OP_P_200103 TABLESPACE UC_OP_TSIX_200103
PCTFREE 5 PCTUSED 60
STORAGE ( INITIAL 1024M NEXT 1024M MAXEXTENTS UNLIMITED PCTINCREASE 0)
);
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Berindei Alex
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
