Oracle Partitioning

RANGE PARTITION

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-07-01','YYYY-MM-DD')),
PARTITION SALES_DEFT VALUES LESS THAN (MAXVALUE)
);

LIST PARTITION

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY LIST (PROD_ID)
(
PARTITION P1 VALUES (13, 14, 15, 16, 17, 18, 19, 20, 21),
PARTITION P2 VALUES (33, 34, 35, 36, 37, 38, 39, 40, 41),
PARTITION P3 VALUES (117,118, 119, 120, 121, 122, 123, 124),
PARTITION P4 VALUES (134, 135, 136, 137, 138, 139, 140),
PARTITION PD VALUES (DEFAULT)
);

HASH PARTITION

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY HASH (PROD_ID)
(
PARTITION P1 TABLESPACE USERS,
PARTITION P2 TABLESPACE USERS,
PARTITION P3 TABLESPACE USERS,
PARTITION P4 TABLESPACE USERS
);

OR

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY HASH (PROD_ID)
PARTITIONS 4 STORE IN (USERS, USERS, USERS, USERS);

COMPOSITE RANGE HASH PARTITIONING

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY RANGE (TIME_ID)
SUBPARTITION BY HASH (PROD_ID)
SUBPARTITION TEMPLATE
(
SUBPARTITION P1 TABLESPACE USERS,
SUBPARTITION P2 TABLESPACE USERS,
SUBPARTITION P3 TABLESPACE USERS,
SUBPARTITION P4 TABLESPACE USERS
)
(
PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-07-01','YYYY-MM-DD')),
PARTITION SALES_DEFT VALUES LESS THAN (MAXVALUE)
);

COMPOSITE RANGE LIST PARTITIONING

CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY RANGE (TIME_ID)
SUBPARTITION BY LIST (PROD_ID)
SUBPARTITION TEMPLATE
(
SUBPARTITION P1 VALUES (13, 14, 15, 16, 17, 18, 19, 20, 21),
SUBPARTITION P2 VALUES (33, 34, 35, 36, 37, 38, 39, 40, 41),
SUBPARTITION P3 VALUES (117,118, 119, 120, 121, 122, 123, 124),
SUBPARTITION P4 VALUES (134, 135, 136, 137, 138, 139, 140),
SUBPARTITION PD VALUES (DEFAULT)
)
(
PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-07-01','YYYY-MM-DD')),
PARTITION SALES_DEFT VALUES LESS THAN (MAXVALUE)
);

INDEX ORGANIZED TABLES PARTITIONING

CREATE TABLE SCOTT.SALESIOT
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2),
CONSTRAINT PK_CONS_SALESIOT PRIMARY
KEY(PROD_ID,CUST_ID,CHANNEL_ID,PROMO_ID,TIME_ID)
)
ORGANIZATION INDEX
PARTITION BY LIST (PROD_ID)
(
PARTITION P1 VALUES (13, 14, 15, 16, 17, 18, 19, 20, 21),
PARTITION P2 VALUES (33, 34, 35, 36, 37, 38, 39, 40, 41),
PARTITION P3 VALUES (117,118, 119, 120, 121, 122, 123, 124),
PARTITION P4 VALUES (134, 135, 136, 137, 138, 139, 140),
PARTITION PD VALUES (DEFAULT)
);
LOCAL INDEXES
CREATE INDEX SALESIDX_LOCAL ON SALESTAB(TIME_ID)
LOCAL
(
PARTITION SALES_1998_IDX,
PARTITION SALES_1999_IDX,
PARTITION SALES_2000_IDX,
PARTITION SALES_DEFT_IDX
);

GLOBAL PARTITIONED INDEXES

CREATE INDEX SALESIDX_GLOBAL ON SALESTAB(TIME_ID)
GLOBAL PARTITION BY RANGE(TIME_ID)
(
PARTITION SALES_LT_2000 VALUES LESS THAN (TO_DATE('2001-07-01','YYYY-MM-DD')),
PARTITION SALES_DEFAULT VALUES LESS THAN (MAXVALUE)
);

GLOBAL NON-PARTITIONED INDEXES

CREATE INDEX SALESIDX_GLOBAL_NP ON SALESTAB(TIME_ID);

CREATE INDEX SALESIDX_PROMO ON SALESTAB(PROMO_ID);

INSERT INTO SALESTAB SELECT * FROM SALES;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','SALESTAB');

SELECT TABLE_NAME,PARTITION_NAME,NUM_ROWS FROM
USER_IND_PARTITIONS WHERE TABLE_NAME='SALESTAB';

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS
FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='SALESIOT';

SELECT INDEX_NAME,PARTITION_NAME,NUM_ROWS,STATUS FROM USER_IND_PARTITIONS WHERE
INDEX_NAME='SALESIDX_LOCAL';


CREATE TABLE SCOTT.SALESTAB
( PROD_ID NUMBER ,
CUST_ID NUMBER ,
TIME_ID DATE ,
CHANNEL_ID CHAR(1) ,
PROMO_ID NUMBER ,
QUANTITY_SOLD NUMBER(10,2),
AMOUNT_SOLD NUMBER(10,2)
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')),
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-07-01','YYYY-MM-DD')),
PARTITION SALES_DEFT VALUES LESS THAN (MAXVALUE)
);

SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES WHERE
STATUS='UNUSABLE';

SQL> ALTER TABLE SALESTAB TRUNCATE PARTITION SALES_1998;
Table truncated.

SQL> ALTER TABLE SALESTAB DROP PARTITION SALES_1999;
Table altered.

SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES
WHERE STATUS<>'VALID';

INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
SALESIDX_PROMO NORMAL UNUSABLE
PK_CONS_SALESTAB NORMAL UNUSABLE

SQL> ALTER INDEX SALESIDX_PROMO REBUILD;
Index altered.

SQL> ALTER INDEX PK_CONS_SALESTAB REBUILD;
Index altered.

SQL> SELECT INDEX_NAME,PARTITION_NAME,NUM_ROWS,STATUS FROM
USER_IND_PARTITIONS WHERE INDEX_NAME='SALESIDX_LOCAL';

INDEX_NAME PARTITION_NAME NUM_ROWS STATUS
------------------------------ ------------------------------ ----------
--------
SALESIDX_LOCAL SALES_1998_IDX 178834 USABLE
SALESIDX_LOCAL SALES_1999_IDX 247945 USABLE
SALESIDX_LOCAL SALES_2000_IDX 356546 USABLE
SALESIDX_LOCAL SALES_DEFT_IDX 135518 USABLE

SQL> ALTER TABLE SALESTAB TRUNCATE PARTITION SALES_2000 UPDATE
GLOBAL INDEXES;
Table truncated.

SQL> ALTER TABLE SALESTAB DROP PARTITION SALES_2000 UPDATE
GLOBAL INDEXES;
Table altered.

SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM USER_INDEXES
WHERE STATUS='UNUSABLE';
no rows selected

ADD PARTITION

SQL> ALTER TABLE SCOTT.SALESTAB ADD PARTITION SALES_DEFT
VALUES LESS THAN (MAXVALUE);
Table altered.


EXCHANGE

SQL> CREATE TABLE SALESTAB_SALES_1998 AS SELECT * FROM
SALESTAB WHERE 1=2;
Table created.

SQL> ALTER TABLE SALESTAB EXCHANGE PARTITION SALES_1998 WITH TABLE
SALESTAB_SALES_1998;
Table altered.

MERGE

ALTER TABLE SALESTAB MERGE PARTITIONS SALES_1998, SALES_1999
INTO PARTITION SALES_1999;

SQL> alter index SALESIDX_PROMO rebuild;
Index altered.

SQL> ALTER INDEX SALESIDX_LOCAL REBUILD PARTITION SALES_1999_IDX;
Index altered.

SPLIT

SQL> ALTER TABLE SALESTAB SPLIT PARTITION SALES_1999 AT (TO_DATE('1999-01-01','YYYY-MM-DD'))
INTO (PARTITION SALES_1998 ,PARTITION SALES_1999);
Table altered.

ALTER TABLE SALESTAB SPLIT PARTITION SALES_DEFT AT (TO_DATE('2001-01-01','YYYY-MM-DD'))
INTO (PARTITION SALES_2001 ,PARTITION SALES_DEFT);

MOVE

SQL> ALTER TABLE SALESTAB MOVE PARTITION SALES_1998 TABLESPACE
TESTDATA3;
Table altered.

SQL> alter index SALESIDX_LOCAL rebuild partition SALES_1998;
Index altered.

SQL> alter index SALESIDX_PROMO rebuild;
Index altered.


REDEFINE PARTITION ONLINE

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE (
UNAME => 'SCOTT',
ORIG_TABLE => 'SALESTAB',
INT_TABLE => 'SALESTAB_NEW',
PART_NAME => 'SALES_2000'
);
END;
/

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE (
UNAME => 'SCOTT',
ORIG_TABLE => 'SALESTAB',
INT_TABLE => 'SALESTAB_NEW',
PART_NAME => 'SALES_2000'
);
END;
/

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE (
UNAME => 'SCOTT',
ORIG_TABLE => 'SALESTAB',
INT_TABLE => 'SALESTAB_NEW',
PART_NAME => 'SALES_2000'
);
END;
/