top of page

Oracle Table and Index Partitions

Optimize performance with Oracle table and index partitioning.

Partitioning allows tables and indexes to be divided into small parts for easy management inside database. You can create small portions of very large tables inside database which makes data access more simple and manageable.


Crate Table Partitions


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
 ); 

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;

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 Partitioned Index


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

  • Local Index and

  • Global 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);  

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

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


Oracle 12cR2 allows you to perform an online conversion of non-partitioned table to a partitioned table without any impact to the end users access to the table data. The previous Oracle releases didn't have the option of creating partitions on an existing table and involved several steps to achieve this.

We will be looking at all the methods of creating partitions for an existing table starting with the latest one.


Online Partition Existing Table (12cR2 onwards)


Oracle 12cR2 requires only a single command to covert non-partitioned table into a partitioned table. Assume that you would like to create year wise range partition on a column which contains date and time

ALTER TABLE hr.tab1 MODIFY
PARTITION BY RANGE (transaction_date) (
PARTITION TRX_2018 VALUES LESS THAN (TIMESTAMP' 2018-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2019 VALUES LESS THAN (TIMESTAMP' 2019-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2020 VALUES LESS THAN (TIMESTAMP' 2020-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2021 VALUES LESS THAN (TIMESTAMP' 2021-12-31 23:59:59.000000000+00:00') tablespace TBS_01
) ONLINE;

Notice the ONLINE clause at the end of the query. Let's gather statistics

EXEC DBMS_STATS.gather_table_stats('HR', 'TAB1');

Check table partitions

SELECT table_name, partition_name, num_rows
FROM dba_tab_partitions WHERE table_name='TAB1'
ORDER BY 1,2;
The index on transaction_date column will be automatically converted to locally partitioned index

Check index partitions are automatically created

SELECT index_name, table_name, partitioned, status
FROM dba_indexes where table_name='TAB1' ORDER BY 1;

SELECT index_name, partition_name, status, NUM_ROWS
FROM dba_ind_partitions WHERE idex_name='&index_name' ORDER BY 1,2;

Export & Import Partition Table


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

SQL> select TABLE_NAME, CLUSTER_NAME, PARTITIONED from dba_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

[oracle@ahmed ~]$ 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;

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   dba_tab_partitions
ORDER BY table_name, partition_name;


Add Partition to Existing Partition Table


To add a new partition to an existing partitioned table, use below command

ALTER TABLE SALES_NEW 
ADD PARTITION sales_2002
values less than (TO_DATE('01-JAN-2003','DD-MON-YYYY'))
tablespace TBS_01;

Run below command to check high value of each partition

SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;

Further Read


Become a top notch dba with DBA Genesis
Start your DBA career today
bottom of page