Skip to content

SQL Developer Create Table Partition

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.

Here we introduce the way to create a new partitioned table by SQL developer.

Let's see the procedure.

  1. New a Table
  2. Column Definition
  3. Partition Type and Key
  4. Add Partition
  5. Preview DDL
  6. Table Created

New a Table

Right-click on "Tables", it shows a function menu.

SQL Developer - Create a New Table

SQL Developer - Create a New Table

We enable "Advanced" option.

SQL Developer - Create Table Builder - Click Advanced

SQL Developer - Create Table Builder - Click Advanced

Column Definition

Add some columns.

SQL Developer - Create Table Builder - Column

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

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

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

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

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

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *