Skip to content
Home » Oracle » SQL Developer Create Table Partition

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.

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.

  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.

Later on, we may also create some partitioned indexes on the partitioned table in SQL developer to get performance boost.

Leave a Reply

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