drop partitions without invalidating global indexes
alter table t drop partition part1 update <global> indexes;
|
Create the new empty partitioned table
CREATE TABLE tablePartitioned
PARTITION BY RANGE (your range)
(PARTITION partition_migrate VALUES LESS THAN (all keys of old table),
PARTITION P_new VALUES LESS THAN (a new dummy partition),
PARTITION p_max VALUES LESS THAN (MAXVALUE))
AS SELECT * FROM oldTable WHERE 1 = 2;
Exchange partition and finish
ALTER TABLE tablePartitioned_P EXCHANGE PARTITION partition_migrate WITH TABLE oldTable EXCLUDING INDEXES WITHOUT VALIDATION;
DROP TABLE oldTable;
RENAME tablePartitioned TO oldTable;
#recreate indexes
|
Split partitionsALTER TABLE CLDB.ico_numeri_telefono_t SPLIT PARTITION CAMPANIA AT
('087') INTO ( PARTITION CAMPANIA_01 TABLESPACE ico_5_tbs , PARTITION CAMPANIA_02 TABLESPACE ico_6_tbs);
alter index CLDB.ICO_NUMERI_TELEFONO_PK rebuild partition EMILIA_TOSCANA_01;
begin
dbms_stats.gather_table_stats(ownname=> 'CLDB', tabname=> 'ico_numeri_telefono_t', partname=> 'EMILIA_TOSCANA_01' , estimate_percent
=> 10, cascade=>true );
end;
/ |
Range partitioning This is a very easy way to partition a table, its a logical way to partition Obviously you can set your own different tablespace for each partition CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) PARTITION BY RANGE (sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE users1, PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE users2, PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE users3, PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE users4); |
List partitioning
Unlike range, here you manually specify a list of values matching a specific criteria
CREATE TABLE q1_sales_by_region
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA') tablespace users1,
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM') tablespace users2,
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ') tablespace users3,
PARTITION q1_southeast VALUES ('FL', 'GA') tablespace users4,
PARTITION q1_northcentral VALUES ('SD', 'WI') tablespace users5,
PARTITION q1_southcentral VALUES ('OK', 'TX')) tablespace users6; |
Hash partitioning Provide a uniform distribution of rows based on a hash key, this is not a logical way to partition CREATE TABLE sales( invoice_no NUMBER,sale_year INT NOT NULL,sale_month INT NOT NULL,sale_day INT NOT NULL ) PARTITION BY HASH (invoice_no) PARTITIONS 4 STORE IN (users1, users2, users3, users4); |
Composite Range-Hash partitioning
Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating range-hash partitions, you specify the following:
Partitioning method: range
Partitioning column(s)
Partition descriptions identifying partition bounds
Subpartitioning method: hash
Subpartitioning column(s)
Number of subpartitions for each partition or descriptions of subpartitions
The following statement creates a range-hash partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).
CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE)); |