Oracle Table Partitioning / Local scope vs Global scope

Partitioning in Database is very important for system performances for critical applications. This improves overall Database and Application level maintainability and manageability. In Database partitioning, Database administrators can define smaller pieces of Database objects under different partition types. In application level, programmers can focus on specific Database objects rather than the entire Database object. This approach helps to make high performance and terabyte level applications. 

Figure 1 - Non partitioned table vs partitioned table

Global scope partitioning

Main disadvantage of this type of partitioning is, once we remove old/unnecessary partitions then we have to rebuild entire Database. So this required complete system downtime

Local scope partitioning

Main advantage of this approach is, once we remove old/unnecessary partition then we don't need to rebuild entire Database objects and no system downtime is required. In this approach we have to manually create relevant partitions.  

Ex : Local scope partitioning ( Manual approach )

Note : This table  has primary key called 'LOG_ID' and lets create partitions based on date time. Data of previous month will be stored in relevant partition.

CREATE TABLE "TBL_MESSAGE" (
"LOG_ID" VARCHAR2(100 BYTE), 
"ARRIVAL_DATETIME" TIMESTAMP (6), 
"PAYMENT_DATE" TIMESTAMP (6), 
"TRANSACTION_REFERRENCE" VARCHAR2(100 BYTE), 
"PAYMENT_AMOUNT" VARCHAR2(100 BYTE), 
"TX_STATUS_DESCRIPTION" VARCHAR2(2000 BYTE), 
PRIMARY KEY ("LOG_ID")
  )
   PARTITION BY RANGE (ARRIVAL_DATETIME)(
PARTITION message_01122020 VALUES LESS THAN (TO_DATE('01/12/2020', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01012021 VALUES LESS THAN (TO_DATE('01/01/2021', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01022021 VALUES LESS THAN (TO_DATE('01/02/2021', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01032021 VALUES LESS THAN (TO_DATE('01/03/2021', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01042021 VALUES LESS THAN (TO_DATE('01/04/2021', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01052021 VALUES LESS THAN (TO_DATE('01/05/2021', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION message_01062021 VALUES LESS THAN (TO_DATE('01/06/2021', 'DD/MM/YYYY')) TABLESPACE users);

   

Note: We have to create partition indexing on specific table column. We have to decided this column which is based on most frequent database queries.

CREATE INDEX index_tbl_message ON TBL_MESSAGE (ARRIVAL_DATETIME) LOCAL (

PARTITION message_01122020 TABLESPACE users,

PARTITION message_01012021 TABLESPACE users,

PARTITION message_01022021 TABLESPACE users,

PARTITION message_01032021 TABLESPACE users,

PARTITION message_01042021 TABLESPACE users,

PARTITION message_01052021 TABLESPACE users,

PARTITION message_01062021 TABLESPACE users);

Ex : Local scope partitioning ( Automatic approach )

Note : In this approach partitions will be created automatically in each month

CREATE TABLE "TBL_MESSAGE_AUTO" (

"LOG_ID" VARCHAR2(100 BYTE), 

"ARRIVAL_DATETIME" TIMESTAMP (6), 

"PAYMENT_DATE" TIMESTAMP (6), 

"TRANSACTION_REFERRENCE" VARCHAR2(100 BYTE), 

"PAYMENT_AMOUNT" VARCHAR2(100 BYTE), 

"TX_STATUS_DESCRIPTION" VARCHAR2(2000 BYTE), 

PRIMARY KEY ("LOG_ID")

  )

PARTITION BY RANGE (ARRIVAL_DATETIME)

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

( 

   PARTITION message_01122020 VALUES LESS THAN (TO_DATE('01-12-2020', 'DD-MM-YYYY')),

   PARTITION message_01012021 VALUES LESS THAN (TO_DATE('01-01-2021', 'DD-MM-YYYY'))

); 

CREATE INDEX index_tbl_message_auto ON TBL_MESSAGE_AUTO (ARRIVAL_DATETIME) LOCAL (

PARTITION message_01122020 TABLESPACE users,

PARTITION message_01012021 TABLESPACE users);