Tutorial by Examples

This creates a table partitioned by hash, in this example on store id. CREATE TABLE orders ( order_nr NUMBER(15), user_id VARCHAR2(2), order_value NUMBER(15), store_id NUMBER(5) ) PARTITION BY HASH(store_id) PARTITIONS 8; You should use a power of 2 for the number of hash ...

This creates a table partitioned by ranges, in this example on order values. CREATE TABLE orders ( order_nr NUMBER(15), user_id VARCHAR2(2), order_value NUMBER(15), store_id NUMBER(5) ) PARTITION BY RANGE(order_value) ( PARTITION p1 VALUES LESS THAN(10), PARTITION ...

Check existing partitions on Schema SELECT * FROM user_tab_partitions;

This creates a table partitioned by lists, in this example on store id. CREATE TABLE orders ( order_nr NUMBER(15), user_id VARCHAR2(2), order_value NUMBER(15), store_id NUMBER(5) ) PARTITION BY LIST(store_id) ( PARTITION p1 VALUES (1,2,3), PARTITION p2 VALUES(4,5,6...

ALTER TABLE table_name DROP PARTITION partition_name;

Select data from a partition SELECT * FROM orders PARTITION(partition_name);

ALTER TABLE table_name TRUNCATE PARTITION partition_name;

ALTER TABLE table_name RENAME PARTITION p3 TO p6;

ALTER TABLE table_name MOVE PARTITION partition_name TABLESPACE tablespace_name;

ALTER TABLE table_name ADD PARTITION new_partition VALUES LESS THAN(400);

Splits some partition into two partitions with another high bound. ALTER TABLE table_name SPLIT PARTITION old_partition AT (new_high_bound) INTO (PARTITION new_partition TABLESPACE new_tablespace, PARTITION old_partition)

Merge two partitions into single one ALTER TABLE table_name MERGE PARTITIONS first_partition, second_partition INTO PARTITION splitted_partition TABLESPACE new_tablespace

Exchange/convert a partition to a non-partitioned table and vice versa. This facilitates a fast "move" of data between the data segments (opposed to doing something like "insert...select" or "create table...as select") as the operation is DDL (the partition exchange ope...