Oracle generates a monthly partition table through one click script

Posted by satanclaus on Sat, 08 Jan 2022 05:27:51 +0100

preface

As one of the three major components of Oracle, partition table plays a vital role in Oracle database.

What are the advantages of partitioned tables?

  • Common table to partition table: the application does not perceive it, and the DML statement can access the partition table without modification.
  • High availability: the unavailability of some partitions does not affect the use of the entire partition table.
  • Easy to manage: you can perform DDL operations on partitions separately and include them in re indexing or expanding partitions, which does not affect the use of partition tables.
  • Reduce OLTP system resource contention: because DML operates on many segments.
  • Enhance the query performance of data warehouse: partition query can be carried out according to time to accelerate query.

In the process of operation and maintenance development, it is found that some application manufacturers did not consider the problem of data volume at the beginning of table construction, resulting in many large tables without partition tables. During the operation of the system, the data volume of these tables has been increasing. When reaching a certain volume, we need to consider partition table conversion to improve the performance of the database. So, how to operate?

1, Introduction

What methods does Oracle provide for us to convert ordinary tables to partitioned tables?

  • Data pump import
  • Subquery insertion
  • Partition switching
  • Online redefinition
  • ALTER TABLE... MODIFY... Mode (supported after 12.2)

Among the above methods, I often use data pump import, sub query insertion and online redefinition. The common point of these three methods is that the intermediate table or target table of partitioned table structure needs to be created in advance.

2, Script

In the long-time repetitive work, when the "lazy cancer" broke out, I wondered whether I could build the table creation statement of partition table in an automatic way? Then I found the name of big brother Liang Jingbin Script for automatic conversion of normal tables to monthly partitioned tables.

After processing and refining, the above script is modified to suit your own use:

  • Statement for generating CTAS full partition table: ctas_par.prc
  • Directly convert CTAS to partitioned table: par_tab_deal.pkg

Click me to jump to the end of the text You can get the partition table script and the contact information of bloggers

par_ tab_ deal. The usage of PKG is:

--Create log table PART_TAB_LOG
create table PART_TAB_LOG
(
 TAB_NAME     VARCHAR2(200),
 DEAL_MODEL   VARCHAR2(200),
 SQL_TEXT     clob,
 DEAL_TIME    DATE,
 remark       VARCHAR2(4000),
 exec_order1  number,
 exec_order2  number
);

--Perform partition table conversion
BEGIN
  pkg_deal_part_tab.p_main(p_tab            => 't1',
                           p_deal_flag      => 1,
                           p_parallel       => 8,
                           p_part_colum     => 'created_date',
                           p_part_nums      => 24,
                           p_tab_tablespace => 'users',
                           p_idx_tablespace => 'users');
END;

--view log
select DBMS_LOB.SUBSTR(sql_text,1000)||';' from part_tab_log t where tab_name='T1' order by exec_order1,exec_order2;
select sql_text||';' from part_tab_log t where tab_name='T1' order by exec_order1,exec_order2;

ctas_ par. Usage of PRC:

--Just execute the output directly
BEGIN
  ctas_par(p_tab        => 't1',
           p_part_colum => 'created_date',
           p_part_nums  => 24,
           p_tablespace => 'users');
END;

3, Practical application

1. Create test table T1

Since the experiment requires a basic table, create table T1 through the following steps:

--delete t1 surface
DROP TABLE t1 PURGE;
--establish t1 surface
CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);
--Create index
CREATE INDEX t1_created_date_idx ON t1(created_date);
--insert data
INSERT INTO t1
SELECT level,
       'Description for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

2. Create procedure

Execute script to create procedure: 

select  'alter  '||object_type||'   '||owner||'.'||object_name||'   compile;'
from  dba_objects t
where t.object_type='PROCEDURE'
and t.object_name='CTAS_PAR' ;

📢 Note: confirm that the procedure has been successfully created.

3. Execute procedure

Execute the procedure generate CTAS create partition table statement:

  • Table name: T1
  • Partition key: CREATED_DATE
  • Months of partition establishment: 24
  • Table space of partition: USERS
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
BEGIN
  ctas_par(p_tab        => 'T1',
           p_part_colum => 'CREATED_DATE',
           p_part_nums  => 24,
           p_tablespace => 'USERS');
END;

The execution script is as follows:

--Minimum record date of partition column obtained from partition table: 2015-01-01 00:00:00
--Partition table ctas The complete statement created is as follows: 
create table T1
partition BY RANGE(CREATED_DATE)(
partition T1_P201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201507 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201508 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201509 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201510 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201511 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201601 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201602 values less than (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201603 values less than (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201604 values less than (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201605 values less than (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201606 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201607 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201611 values less than (TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201612 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_MAX values less than (maxvalue) tablespace USERS)
             nologging
             parallel 4
             enable row movement
             tablespace USERS
             as select /*+parallel(t,8)*/ * from T1_01 t where 1 = 2;
--Attaching logs and canceling parallelism
alter table T1 logging;
alter table T1 noparallel;

If only the table creation statement of partition table is required, it can be simply spliced here:

create table T1
(
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE
)
partition BY RANGE(CREATED_DATE)(
partition T1_P201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201507 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201508 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201509 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201510 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201511 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201601 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201602 values less than (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201603 values less than (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201604 values less than (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201605 values less than (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201606 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201607 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201611 values less than (TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201612 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_MAX values less than (maxvalue) tablespace USERS)
ENABLE ROW MOVEMENT
TABLESPACE USERS;

4. CTAS create partition table

Before actual execution, rename the original table T1:

alter table lucifer.T1 RENAME TO T1_01;

📢 Note: if you need to create a partition table structure, you do not need to modify the above script; To directly create a partitioned table containing data, you need to change where 1 = 2 to where 1 = 1.

Ensure that the current table t1 has been rename d to T1_01. Execute CTAS to create partition table:

View partition table structure:

📢 Note: since CTAS does not inherit comments and defaults to the default value, it is necessary to manually compare whether they are missing.

COMMENT ON TABLE T1 IS '';
COMMENT ON COLUMN T1.ID IS '';
COMMENT ON COLUMN T1.CREATED_DATE IS '';
COMMENT ON COLUMN T1.DESCRIPTION IS '';

Rename the indexes and constraints of the original table:

--Rename index
ALTER INDEX T1_CREATED_DATE_IDX RENAME TO T1_CREATED_DATE_IDX_01;
ALTER INDEX T1_PK RENAME TO T1_PK_01;
--Rename unique constraint
ALTER TABLE T1_01 RENAME CONSTRAINT T1_PK TO T1_PK_01;

Partition table new local index:

create index T1_CREATED_DATE_IDX on T1 (CREATED_DATE) tablespace users;
alter table T1 add constraint T1_PK primary key (ID) using index  tablespace users;

Query partition table:

Through the following query, it can be found that the data has been assigned to the corresponding partition by month.

SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t1 PARTITION(T1_P201501);
SELECT COUNT(1) FROM t1 PARTITION(T1_P201601);
SELECT COUNT(1) FROM t1 PARTITION(T1_MAX);

So far, the script has been introduced

Hot Topics

©2025 Programmer Think