Indexes and tablespaces

From: Berindei Alex <Alex.Berindei_at_elsag.it>
Date: Tue, 20 Mar 2001 16:01:13 -0800
Message-ID: <F001.002D2372.20010320133025@fatcity.com>

 

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: 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-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).

Received on Tue Mar 20 2001 - 18:01:13 CST