Oracle Table and Index Partitions

In this article we will look at:

  • How to create table partitions
  • Query to check table partitions
  • Query data from each partition
  • Creating Index on partition table and
  • Partitioning an existing non-partition table

Crate Partition Table

You can use below query to create sample table with time range partition. Please note that each partition is placed under different tablespace inside the database.

CREATE TABLE time_range_sales
   ( prod_id        NUMBER(6) primary key
   , cust_id        NUMBER
   , time_id        DATE
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
PARTITION BY RANGE (time_id)
 (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')) tablespace users,
  PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) tablespace examples,
  PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) tablespace xyz,
  PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) tablespace abc
 ); 

Check Table Partitions

You can query user_tab_partitions to get details about the table, partition name, number of rows in each partition and more.

COLUMN high_value FORMAT A20
SELECT table_name,
       partition_name,
       high_value,
       num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

Query Table Partition

You can query individual table partition to get records only from the specific partition

select * from TIME_RANGE_SALES partition (SALES_1998);
select * from TIME_RANGE_SALES partition (SALES_1999);
select * from TIME_RANGE_SALES partition (SALES_2000);
select * from TIME_RANGE_SALES partition (SALES_2001);

You can always query a partitioned table like a normal table too. In this case, the output will be from all the partitions inside the table

select * from TIME_RANGE_SALES;

Create Index on Partition Table

You can create two types of indexes on a partition table:

  • Local Index and
  • Global Index

Create Local Index

You will be using LOCAL keyword in CREATE INDEX statement to create a local index

CREATE INDEX time_range_sales_indx ON TIME_RANGE_SALES(time_id) LOCAL
 (PARTITION year_1 TABLESPACE users,
  PARTITION year_2 TABLESPACE users,
  PARTITION year_3 TABLESPACE users,
  PARTITION year_4 TABLESPACE users);

Create Global Index

To create a global index, you will be using GLOBAL keyword with CREATE INDEX statement

CREATE INDEX glob_time_range_sales ON TIME_RANGE_SALES(time_id)
GLOBAL PARTITION BY RANGE (time_id)
 (PARTITION glob_y1 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
  PARTITION glob_y2 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
  PARTITION glob_y3 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
  PARTITION glob_y4 VALUES LESS THAN (MAXVALUE));

Check Partition Indexes

select INDEX_NAME, PARTITION_NAME from user_ind_partitions;

Partitioning Existing Non-Partition Table

There are two ways to achieve it:

  1. Take table export, drop it, create new partition table and then import
  2. Use split and exchange partition

Method 1: export & import

First check if a table is partitioned or not using below query

SQL> select TABLE_NAME, CLUSTER_NAME, PARTITIONED from user_tables
where table_name='SALES_NEW';

SQL> select * from sales_new;

PROD_ID    CUST_ID TIME_ID   QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ------------- -----------
       116      11393 05-JUN-99             1         100
        40     100530 30-NOV-98             1         200
       118        133 06-JUN-01             2         300
        36       4523 27-JAN-99             1         500
       125       9417 04-FEB-98             1         600
        30        170 23-FEB-01             1         700
        24      11899 26-JAN-99             1         800
        45       9491 28-AUG-98             1         100
       133       9450 01-DEC-00             1         400
        35       2606 17-FEB-00             1         900

Export the table using Oracle exp utility

# exp file=sales_new.exp tables=sales_new

[[email protected] ~]$ ls -lrt
total 20
drwxr-xr-x 3 oracle oinstall  4096 Mar 10 13:21 oradiag_oracle
-rw-r--r-- 1 oracle oinstall 16384 Mar 11 03:20 sales_new.exp

Rename The Original table

SQL> rename sales_new to sales_new_bkp;

Create New Partition Table

CREATE TABLE sales_test
   ( prod_id        NUMBER(6) primary key
   , cust_id        NUMBER
   , time_id        DATE
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
PARTITION BY RANGE (time_id)
 (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
  PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
  PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
  PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
 ); 

Import Data Into New Partition Table

imp file=sales_new.exp tables=sales_new ignore=y

Drop The Old Table

DROP TABLE SALES_NEW_BKP;

Query Partitions From the New Partition Table

select * from SALES_NEW partition (SALES_1998);
select * from SALES_NEW partition (SALES_1999);
select * from SALES_NEW partition (SALES_2000);
select * from SALES_NEW partition (SALES_2001);

SELECT * FROM SALES_NEW;

Method 2: split & exchange partition

Create a test table for this activity and make sure its not a partition table

CREATE TABLE my_table (
  id           NUMBER,
  description  VARCHAR2(50)
);

INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;

Now create another single full partition table with only one partition to contain whole table

CREATE TABLE my_table_2 (
  id           NUMBER,
  description  VARCHAR2(50)
)
PARTITION BY RANGE (id)
(PARTITION my_table_part VALUES LESS THAN (MAXVALUE));

Switch original table segment with partition table segment

ALTER TABLE my_table_2
  EXCHANGE PARTITION my_table_part
  WITH TABLE my_table
  WITHOUT VALIDATION;

Drop original table and rename partition table

DROP TABLE my_table;
RENAME my_table_2 TO my_table;

Finally, we can split the new partition table into Multiple partitions

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
 INTO (PARTITION my_table_part_1,
       PARTITION my_table_part_2);

Check partition details via below query

SELECT table_name,
       partition_name,
       high_value,
       num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

Was this article helpful?

Related Articles

Leave a Comment