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.
SQL Developer - Create a New Table
We enable "Advanced" option.
SQL Developer - Create Table Builder - Click Advanced
Column Definition
Add some columns.
SQL Developer - Create Table Builder - Column
Partition Type and Key
We select "Range" as our partition type.
SQL Developer - Create Table Builder - Partition by Range
Select the partition key. Columns which contain datetime factor are good candidates to be a partition key.
SQL Developer - Create Table Builder - Select Partition Key
Add Partition
Click on + sign to add a new partition segment.
SQL Developer - Create Table Builder - Add a Partition
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').
SQL Developer - Create Table Builder - First Partition
New Data (Hot Data)
The rest of rows go to the second partition segment, so we choose MAXVALUE to accommodate all new data.
SQL Developer - Create Table Builder - MAXVALUE Partition
Preview DDL
We review DDL for making sure that everything is ready.
SQL Developer - Create Table Builder - DDL
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.