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.expRename 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=yDrop 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
