PARTITIONING

Ejemplo de una tabla Particionada por rangos de fechas y su respectivo índice.


CREATE TABLE OWNER.TABLE_NAME (
campo_1 number(10),
campo_2 varchar2(100),
campo_3 date)
PARTITION BY RANGE (campo_3) (
PARTITION P200808 VALUES LESS THAN (TO_DATE('01-09-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200809 VALUES LESS THAN (TO_DATE('01-10-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200810 VALUES LESS THAN (TO_DATE('01-11-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200811 VALUES LESS THAN (TO_DATE('01-12-2008','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200812 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200901 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200902 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY'))
TABLESPACE DATA_P,
PARTITION P200903 VALUES LESS THAN (TO_DATE('01-04-2009','DD-MM-YYYY'))
TABLESPACE DATA_P);


El índice para la tabla con el mísmo rango de particiones.


CREATE INDEX OWNER.INDEX_NAME ON OWNER:TABLE_NAME
(campo_2)
TABLESPACE EXCH_INDX_P
LOGGING
LOCAL (
PARTITION P200808
TABLESPACE INDX_P,
PARTITION P200809
TABLESPACE INDX_P,
PARTITION P200810
TABLESPACE INDX_P,
PARTITION P200811
TABLESPACE INDX_P,
PARTITION P200812
TABLESPACE INDX_P,
PARTITION P200901
TABLESPACE INDX_P,
PARTITION P200902
TABLESPACE INDX_P,
PARTITION P200903
TABLESPACE INDX_P,
)
NOPARALLEL;