Since the syntax to create a table partitioning is really hard to keep in mind, we'd better use GUI tools to facilitate us to create. Before doing this, I'd recommend that you should read: how to create a normal table in SQL developer first.
Please note that, table partitioning is an optional feature for enterprise edition. Extra cost is required to have the feature. Please refer to Oracle Database Licensing for more information.
Here we introduce the way to create a new partitioned table by SQL developer.
Let's see the procedure.
New a Table
Right-click on "Tables", it shows a function menu.
We enable "Advanced" option.
Column Definition
Add some columns.
Partition Type and Key
We select "Range" as our partition type.
Select the partition key. Columns which contain datetime factor are good candidates to be a partition key.
Add Partition
Click on + sign to add a new partition segment.
Historical Data (Cold Data)
We add the first partition segment with values less than TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS').
New Data (Hot Data)
The rest of rows go to the second partition segment, so we choose MAXVALUE to accommodate all new data.
Preview DDL
We review DDL for making sure that everything is ready.
The literal DDL is as below.
CREATE TABLE SALES
(
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
PARTITION SALES_CURRENT VALUES LESS THAN (MAXVALUE)
);
Then click OK to confirm.
Table Created
After we have created the new partitioned table, we can try to load some data.
SQL> insert into hr.sales select * from sh.sales;
918843 rows created.
SQL> commit;
Commit complete.
We made it.
Later on, we may also create some partitioned indexes on the partitioned table in SQL developer to get performance boost.