Partitioning Table in Oracle

 
We can partition an existing table online , this allows DML operations to continue during the conversion.

•  Identify the min and max values for the partition_range_value

select min(trans_Date),max(trans_date) from Attendance;

• Partition the table using below SQL,

ALTER TABLE Attendance MODIFY
  PARTITION BY RANGE (trans_date) (
    PARTITION t1_part_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY')),
    PARTITION t1_part_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),
    PARTITION t1_part_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),
    PARTITION t1_part_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')),
    PARTITION t1_part_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
    PARTITION t1_part_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),
    PARTITION t1_part_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),
    PARTITION t1_part_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')),
    PARTITION t1_part_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY')),
    PARTITION t1_part_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY'))
  ) ONLINE;

• Adding Partition to existing partitioned table

ALTER TABLE Attendance
ADD PARTITION t1_part_2024 
VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'));

• Gather fresh statistics on the table

EXEC DBMS_STATS.gather_table_stats(NULL, 'Attendance');

• Check table partitions 

  SELECT table_name, partition_name, num_rows FROM user_tab_partitions where table_name like 'Attendance' ORDER BY 1,2;
        
        Table_name    Partition_name    Num_Rows
        ==================================
    Attendance     T1_PART_2014	0
    Attendance     T1_PART_2015	9
    Attendance     T1_PART_2016	453 
    Attendance     T1_PART_2017	670
    Attendance     T1_PART_2018	900
    Attendance     T1_PART_2019	711146
    Attendance     T1_PART_2020	300087
    Attendance     T1_PART_2021	671263

      

• Check indexes on partitioned table

SELECT index_name, partitioned, status,table_name  
FROM   user_indexes where table_name like 'Attendance'  ORDER BY 1;

	index_name         partitioned     status            table_name
	==============================================
	BDG_INDX2_P1	YES	        N/A	            Attendance
	BDG_TEST_P2	    YES	            N/A	            Attendance

The status N/A in user_indexes only tells you it is not a 'normal' index
You need to look at user_ind_partitions view to see if your partitioned index is ok or not. And then look for status usable and unusable.

• Check index partitions.

SELECT index_name, partition_name, status FROM   user_ind_partitions ORDER BY 1,2;
	
        index_name 	        partition_name         status
	=======================================
	BDG_INDX2_P1	T1_PART_2014	        USABLE
	BDG_INDX2_P1	T1_PART_2015	        USABLE
	BDG_INDX2_P1	T1_PART_2016	        USABLE
	BDG_INDX2_P1	T1_PART_2017	        USABLE

• Rebuild Partitioned Indexes

 Identify the indexes and the index partitions to be rebuild

SELECT index_name, partition_name, status FROM   user_ind_partitions ORDER BY 1,2;
	
        index_name 	        partition_name         status
	=======================================
	BDG_INDX2_P1	T1_PART_2014	        USABLE
	BDG_INDX2_P1	T1_PART_2015	        USABLE
	BDG_INDX2_P1	T1_PART_2016	        USABLE
	BDG_INDX2_P1	T1_PART_2017	        USABLE

      

Specify the partition name in the index rebuild statement:

alter index BDG_INDX2_P1 rebuild partition T1_PART_2015;

OR Build all at once

select 'alter index ' || index_name || ' rebuild partition ' || partition_name || ';' 
from dba_ind_partitions 
where index_name = 'BDG_TEST_P2';

• Query data from specific partition

select * from Attendance partition(T1_PART_2020);

• Try updating existing record using the partition_range_value

        I will try to update 2015 records + 3 years

update Attendance set trans_date=trans_date+1000 where year ='2015';
    
        ORA-14402: updating partition key column would cause a partition change

FIX : -   SQL > alter table Attendance enable row movement;
             SQL > update Attendance set trans_date=trans_date+1000 where year ='2015';

Gather statistics after update on the Partitioned table
   
    SQL >   SELECT table_name, partition_name, num_rows FROM user_tab_partitions where table_name like 'Attendance' ORDER BY 1,2;   
     Table_name    Partition_name    Num_Rows
        ==================================
    Attendance     T1_PART_2014	0
    Attendance     T1_PART_2015	0
    Attendance     T1_PART_2016	0 
    Attendance     T1_PART_2017	0
    Attendance     T1_PART_2018	221
    Attendance     T1_PART_2019	729746
    Attendance     T1_PART_2020	335287
    Attendance     T1_PART_2021	741263