How To Rename Interval Partition with More Meaningful Name

3 min read

Mar 17, 2021 12:00:00 AM

When using interval partitioning, the database automatically creates partitions for a specified interval.

Unfortunately, the database creates partitions with non-meaningful names, e.g. SYS_P35714.

It would be better if the partition was named using some sort of format, e.g. PYYYYMM – P201212. This could help to simplify some queries.

Here’s an example of how to simplify a query:

-- Query using system generated partition.
SQL> select TRANS_DATE from OWNER.TRANSACTION PARTITION (SYS_P35714) where ROWNUM=1;

TRANS_DATE
---------------
26-DEC-12

SQL> 

-- Query using named partition.
select TRANS_DATE from OWNER.TRANSACTION PARTITION (P201212) where ROWNUM=1;

Fortunately, there’s an option to rename interval partitions. I’ll demonstrate how you can do this.

Below is the PL/SQL block to rename interval partitions.

For testing purposes and validation, the script doesn’t actually rename the partition but rather generates SQL to rename the partition.

set echo off
set lines 300 pages 200 serveroutput on size unlimited trimsp on tab off feedb off verify off echo on
DECLARE
  l_sql VARCHAR2 (4000);
  l_fmt VARCHAR2 (30);
  l_par VARCHAR2 (30);
BEGIN
  FOR x IN (
    SELECT a.table_name, NULL index_name, a.partition_name,
           a.partition_position, a.high_value, b.interval
    FROM   dba_tab_partitions a, dba_part_tables b
    WHERE  a.table_name = b.table_name
    AND    a.interval = 'YES'
    AND    REGEXP_LIKE(b.interval,'^[0-9]|DAY|MONTH|YEAR','i')
    AND    a.table_owner = UPPER('&&owner')
    AND    a.table_name = UPPER('&&table')
    AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
    UNION ALL
    SELECT d.table_name, c.index_owner||'.'||c.index_name index_name,
           c.partition_name, c.partition_position, c.high_value, d.interval
    FROM   dba_ind_partitions c, dba_part_indexes d
    WHERE  c.index_name = d.index_name
    AND    c.interval = 'YES'
    AND    REGEXP_LIKE(d.interval,'^[0-9]|DAY|MONTH|YEAR','i')
    AND    d.table_name = UPPER('&&table')
    AND    partition_name LIKE 'SYS\_P%' ESCAPE '\'
    ORDER BY 1,2 NULLS FIRST,4 ASC
  )
  LOOP
    IF INSTR(x.interval,'YEAR') > 0 THEN
      l_fmt := q'#,'"P"yyyy'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSIF INSTR(x.interval,'MONTH') > 0 THEN
      l_fmt := q'#,'"P"yyyymm'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSIF INSTR(x.interval,'DAY') > 0 THEN
      l_fmt := q'#,'"P"yyyymmdd'#';
      l_sql := 'SELECT TO_CHAR('|| x.high_value||'-'||x.interval||l_fmt||') from dual';
    ELSE
      l_fmt := 'P'||LPAD(ROUND(TO_NUMBER(x.high_value)/TO_NUMBER(x.interval)),3,0);
      l_sql := 'SELECT '''||l_fmt||''' from dual';
    END IF;
    EXECUTE IMMEDIATE l_sql INTO l_par;
    IF x.index_name IS NULL THEN
      l_sql := 'ALTER TABLE '|| x.table_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
    ELSE
      l_sql := 'ALTER INDEX '|| x.index_name|| ' RENAME PARTITION '|| x.partition_name|| ' TO '|| l_par;
    END IF;
    EXECUTE IMMEDIATE l_sql;
--  dbms_output.put_line(l_sql);
  END LOOP;
END;
/

NOTE: EXECUTE IMMEDIATE l_sql INTO l_par is required vs assigning value to l_par.

The rationale is the high_value contains the string “TO_DATE(‘ 2021-05-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)” and the TO_DATE needs to be evaluated and the only way to do it is using EXECUTE IMMEDIATE.

Hopefully, this is useful information for creating standards and simplifying processes.

Here’s an example of interval partitioning for the table TRANSACTION:

TABLE_NAME           PARTITION_NAME        PART_POS INT COMPRESS COMPRESS_FOR HIGH_VALUE                                               NUMTOYMINTERVAL
-------------------- --------------------- -------- --- -------- ------------ -------------------------------------------------------- ------------------------------
TRANSACTION          SYS_P35714                  13 YES ENABLED  ADVANCED     TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P35713                  14 YES ENABLED  ADVANCED     TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P35712                  15 YES ENABLED  ADVANCED     TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P36731                  16 YES ENABLED  ADVANCED     TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P39163                  17 YES ENABLED  ADVANCED     TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
TRANSACTION          SYS_P41802                  18 YES ENABLED  ADVANCED     TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')

When running the PL/SQL, this is the output stating what the partitions will be renamed to:

ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P35714 TO P201212;
ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P35713 TO P201301;
ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P35712 TO P201302;
ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P36731 TO P201303;
ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P39163 TO P201304;
ALTER TABLE OWNER.TRANSACTION RENAME PARTITION SYS_P41802 TO P201305;

If you have any questions or thoughts about the above, please feel free to leave them in the comments!