Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.
Steps:
- Create a non partitionned table
- Insert rows in the table
- Convert a non patitionned table to partitionned table online.
Step 01: Create a non partitionned table
Exemple:
create table months_tn
(
month_date date not null,
month_name varchar2(20)
);
Step 02: Insert rows in the table
insert into months_tn values (to_date('03/01/2025','DD/MM/YYYY'),'jun01');
insert into months_tn values (to_date('25/01/2025','DD/MM/YYYY'),'jun02');
insert into months_tn values (to_date('05/02/2025','DD/MM/YYYY'),'fev01');
insert into months_tn values (to_date('26/02/2025','DD/MM/YYYY'),'fev02');
insert into months_tn values (to_date('06/03/2025','DD/MM/YYYY'),'mars01');
insert into months_tn values (to_date('20/03/2025','DD/MM/YYYY'),'mars02');
insert into months_tn values (to_date('04/04/2025','DD/MM/YYYY'),'april01');
insert into months_tn values (to_date('19/04/2025','DD/MM/YYYY'),'april02');
Step 03: Convert a non patitionned table to partitionned table online
Range partitioning is the most common type of partitioning and is often used with dates, Each partition has a VALUES LESS THAN clause, that specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this literal are added to the next higher partition, A MAXVALUE literal can be defined for the highest partition.
alter table months_tn modify partition by range (month_date)
(
partition month_01 values less than (to_date('01/02/2025','DD/MM/YYYY')),
partition month_02 values less than (to_date('01/03/2025','DD/MM/YYYY')),
partition month_03 values less than (to_date('01/04/2025','DD/MM/YYYY')),
partition month_04 values less than (to_date('01/05/2025','DD/MM/YYYY'))
);
Check range paritions
SQL> SELECT table_name, partition_name FROM user_tab_partitions where lower(table_name)='months_tn';
TABLE_NAME PARTITION_NAME
-------------------- --------------------
MONTHS_TN MONTH_01
MONTHS_TN MONTH_02
MONTHS_TN MONTH_03
MONTHS_TN MONTH_04
SQL>
Enjoy!
Google+
Facebook
