Skip to Main Content
  • Questions
  • Move subpartition to another partition

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Stefano.

Asked: April 12, 2018 - 7:38 pm UTC

Last updated: April 17, 2018 - 4:48 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi.
Today I've discovered another issue on the DB I've inherited.
I've found this range subpartitions in wrong partitions.

Let me try to explain.

The scenario is:

Partitions with month values subpartitioned by weeks, so I expect to have a hierarchy like this:

P_17_01 January 2017
  SP_17_01_01 1st Jan Week
  SP_17_01_02 2nd Jan Week
  SP_17_01_03 3rd Jan Week
  SP_17_01_04 4th Jan Week

P_17_02 February 2017
  SP_17_02_01 1st Feb Week
  SP_17_02_02 2nd Feb Week
  SP_17_02_03 3rd Feb Week
  SP_17_02_04 4th Feb Week


Looking at the DDL I've found


P_17_01 January 2017
  SP_17_01_01 1st Jan Week

P_17_02 February 2017
  SP_17_01_02 2nd Jan Week
  SP_17_01_03 3rd Jan Week
  SP_17_01_04 4th Jan Week
  SP_17_02_01 1st Feb Week
  SP_17_02_02 2nd Feb Week
  SP_17_02_03 3rd Feb Week
  SP_17_02_04 4th Feb Week


Can't find how to move the January subpartitions from February partition to the correct one without losing data

Thanks

and Connor said...

You should not need to move them, because data cannot be entered into them. For example

SQL> create table t ( d date )
  2  partition by range ( d )
  3  subpartition by range ( d )
  4  (
  5    partition p1 values less than (date '2000-02-01' )
  6     (
  7       subpartition p1s1 values less than (date '2000-01-07' ),
  8       subpartition p1s2 values less than (date '2000-01-14' )
  9     ),
 10    partition p2 values less than (date '2000-03-01' )
 11     (
 12       subpartition p1s3 values less than (date '2000-01-21' ),
 13       subpartition p1s4 values less than (date '2000-01-31' ),
 14       subpartition p2s1 values less than (date '2000-02-07' ),
 15       subpartition p2s2 values less than (date '2000-02-14' ),
 16       subpartition p2s3 values less than (date '2000-02-21' ),
 17       subpartition p2s4 values less than (date '2000-02-29' )
 18     )
 19  )  ;

Table created.

SQL>
SQL> begin
  2   for i in 0 .. 200 loop
  3     begin
  4       insert into t values ( date '2000-01-01'+i ) ;
  5       commit;
  6     exception
  7       when others then null;
  8     end;
  9   end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t order by 1;

D
---------
01-JAN-00
02-JAN-00
03-JAN-00
04-JAN-00
05-JAN-00
06-JAN-00
07-JAN-00
08-JAN-00
09-JAN-00
10-JAN-00
11-JAN-00
12-JAN-00
13-JAN-00
01-FEB-00
02-FEB-00
03-FEB-00
04-FEB-00
05-FEB-00
06-FEB-00
07-FEB-00
08-FEB-00
09-FEB-00
10-FEB-00
11-FEB-00
12-FEB-00
13-FEB-00
14-FEB-00
15-FEB-00
16-FEB-00
17-FEB-00
18-FEB-00
19-FEB-00
20-FEB-00
21-FEB-00
22-FEB-00
23-FEB-00
24-FEB-00
25-FEB-00
26-FEB-00
27-FEB-00
28-FEB-00

41 rows selected.

SQL>



Notice that nothing between 14-Jan and 31-jan made it into the table, because the subpartitions were only considered AFTER the partition level range criteria were applied.

Check that each is empty (they should be), and then just drop them, and add new ones to the existing january partition

SQL> alter table t drop subpartition p1s3;

Table altered.

SQL> alter table t modify partition p1 add subpartition p1s3 values less than (date '2000-01-21' );

Table altered.



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Stefano Vasi, April 15, 2018 - 8:04 am UTC

Thanks Connor.
Looking into subpartitions I can see that are filled with data within their range.

In the DDL there are no ranges defined for the partitions.

So I need to move subpartitions in their own partitions.


Connor McDonald
April 16, 2018 - 1:51 am UTC

I'd like to see some evidence - ie, full DDL and some sample data

Stefano Vasi, April 16, 2018 - 8:15 am UTC

Hi Connor.
Here's the DDL

CREATE TABLE T  ( D DATE )
     
PARTITION BY RANGE ( D ) 
SUBPARTITION BY RANGE ( D ) 
(
  PARTITION "PART_2018_01"  VALUES LESS THAN (TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
   (
    SUBPARTITION "SUBPART_2018_01_07"  VALUES LESS THAN (TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
   ),
  PARTITION "PART_2018_02"  VALUES LESS THAN (TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))        
  ( 
    SUBPARTITION "SUBPART_2018_01_14"  VALUES LESS THAN (TO_DATE(' 2018-01-14 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
    SUBPARTITION "SUBPART_2018_01_21"  VALUES LESS THAN (TO_DATE(' 2018-01-21 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
    SUBPARTITION "SUBPART_2018_01_MX"  VALUES LESS THAN (TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
    SUBPARTITION "SUBPART_2018_02_07"  VALUES LESS THAN (TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  )
);


Feed the table with:

begin
   for i in 0 .. 200 loop
     begin
       insert into t values ( date '2018-01-01'+i ) ;
       commit;
     exception
       when others then null;
     end;
   end loop;
 end;
 /


Results are:

SELECT count(*), min(d), max(d) FROM t SUBPARTITION(SUBPART_2018_01_07)
UNION
SELECT count(*), min(d), max(d) FROM t SUBPARTITION(SUBPART_2018_01_14)
UNION
SELECT count(*), min(d), max(d) FROM t SUBPARTITION(SUBPART_2018_01_21)
UNION
SELECT count(*), min(d), max(d) FROM t SUBPARTITION(SUBPART_2018_01_MX)
UNION
SELECT count(*), min(d), max(d) FROM t SUBPARTITION(SUBPART_2018_02_07);

COUNT(*) MIN(D)         MAX(D)
 7 01-GEN-18 07-GEN-18
 7 08-GEN-18 14-GEN-18
 7 15-GEN-18 21-GEN-18
 7 01-FEB-18 07-FEB-18
10 22-GEN-18 31-GEN-18



Thanks
Connor McDonald
April 17, 2018 - 4:48 am UTC

Wow...what an interesting naming convention :-) "2018_01" only going up to Jan 8th.

Anyway, I think you'll need to run split commands and then drop the subpartitions that no longer apply, eg


SQL> CREATE TABLE T  ( D DATE )
  2  PARTITION BY RANGE ( D )
  3  SUBPARTITION BY RANGE ( D )
  4  (
  5    PARTITION "PART_2018_01"  VALUES LESS THAN (TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  6     (
  7      SUBPARTITION "SUBPART_2018_01_07"  VALUES LESS THAN (TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
  8     ),
  9    PARTITION "PART_2018_02"  VALUES LESS THAN (TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
 10    (
 11      SUBPARTITION "SUBPART_2018_01_14"  VALUES LESS THAN (TO_DATE(' 2018-01-14 23:59:59', 'SYYYY-MM-DD HH24:MI:SS')),
 12      SUBPARTITION "SUBPART_2018_01_21"  VALUES LESS THAN (TO_DATE(' 2018-01-21 23:59:59', 'SYYYY-MM-DD HH24:MI:SS')),
 13      SUBPARTITION "SUBPART_2018_01_MX"  VALUES LESS THAN (TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS')),
 14      SUBPARTITION "SUBPART_2018_02_07"  VALUES LESS THAN (TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
 15    )
 16  );

Table created.

SQL>
SQL> begin
  2     for i in 0 .. 200 loop
  3       begin
  4         insert into t values ( date '2018-01-01'+i ) ;
  5         commit;
  6       exception
  7         when others then null;
  8       end;
  9     end loop;
 10   end;
 11   /

PL/SQL procedure successfully completed.

SQL>
SQL> alter table t split partition "PART_2018_02" at (TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS'))
  2    into ( partition PART_2018_01S, partition PART_2018_02S );

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T', granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL>
SQL> col high_value format a50
SQL> set lines 200
SQL> select partition_name, high_value, subpartition_count, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 HIGH_VALUE                                         SUBPARTITION_COUNT   NUM_ROWS
------------------------------ -------------------------------------------------- ------------------ ----------
PART_2018_01                   TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:                  1       7
                               MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_01S                  TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:                  4      24
                               MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_02S                  TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:                  4       7
                               MI:SS', 'NLS_CALENDAR=GREGORIA


3 rows selected.

SQL>
SQL> col high_value format a50
SQL> set lines 200
SQL> select partition_name, subpartition_name, high_value, num_rows
  2  from user_tab_subpartitions
  3  where table_name = 'T'
  4  order by partition_name, subpartition_position;

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE                                   NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------- ----------
PART_2018_01                   SUBPART_2018_01_07             TO_DATE(' 2018-01-08 00:00:00', 'SYYYY-MM-DD HH24:          7
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_01S                  SYS_SUBP157                    TO_DATE(' 2018-01-14 23:59:59', 'SYYYY-MM-DD HH24:          7
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_01S                  SYS_SUBP158                    TO_DATE(' 2018-01-21 23:59:59', 'SYYYY-MM-DD HH24:          7
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_01S                  SYS_SUBP159                    TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:         10
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_01S                  SYS_SUBP160                    TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:          0
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_02S                  SYS_SUBP161                    TO_DATE(' 2018-01-14 23:59:59', 'SYYYY-MM-DD HH24:          0
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_02S                  SYS_SUBP162                    TO_DATE(' 2018-01-21 23:59:59', 'SYYYY-MM-DD HH24:          0
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_02S                  SYS_SUBP163                    TO_DATE(' 2018-01-31 23:59:59', 'SYYYY-MM-DD HH24:          0
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA

PART_2018_02S                  SYS_SUBP164                    TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:          7
                                                              MI:SS', 'NLS_CALENDAR=GREGORIA


9 rows selected.

SQL>
SQL>
SQL>
SQL>


evil alignment

Racer I., April 16, 2018 - 10:24 am UTC

Hi Stefano,

From your DDL it looks as if the intention was to have partitions not per calendar month(1. -> 31.) but shifted :
PART_01 : up to 7.1.
PART_02 : 8.1. -> 7.2.
...

If so the data looks good as it is?

If not then the partitions should read :

PART_01 VALUES LESS THAN (TO_DATE(' 2018-02-01 0...
PART_02 VALUES LESS THAN (TO_DATE(' 2018-03-01 0...

regards,

Stefano Vasi, April 16, 2018 - 12:37 pm UTC

Customer says that partitioning is by month.
Each month divided in weekly subpartitions.
Subpartitions have been respected.
The only concern is that:

- January partition has only one of its subpartitions.
- February partition has three January subpartitions and one of its subpartitions.

This happens only for Jan to Mar partitions.
Apr to Dec are ok.

I think there has been a mistake while subpartitioning the table.

I only need, and can't find how, to move Jan subpartitions into Jan partition and so on.

If it's possible, of course.

Thanks

chaotic alignment

Racer I., April 17, 2018 - 7:52 am UTC

Hi,

Continuing from Connor :

alter table t split partition "PART_2018_02" at (TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
into ( partition PART_2018_01S, partition PART_2018_02S );   

alter table t merge partitions "PART_2018_01", "PART_2018_01S" into partition "PART_2018_01S";

alter table t rename partition "PART_2018_01S" to "PART_2018_01"

alter table t rename partition "PART_2018_02S" to "PART_2018_02"

declare
  cursor CurPart IS 
    select partition_name, high_value, subpartition_count, num_rows
       from user_tab_partitions
       where table_name = 'T'
       order by partition_position;
  cursor CurSubPart(pPartName VARCHAR2) IS    
   select partition_name, subpartition_name, high_value, num_rows
    from user_tab_subpartitions
    where table_name = 'T'
    and   partition_name = pPartName
    order by partition_name, subpartition_position;
begin
  dbms_output.enable;
  for RecPart in CurPart LOOP
    dbms_output.put_line(RecPart.partition_name || ' up to ' || TO_CHAR(RecPart.high_Value) ||
      ' sub : ' || RecPart.subpartition_count || ' rows : ' || RecPart.num_rows); 
    for RecSubPart in CurSubPart(RecPart.partition_name) LOOP
      dbms_output.put_line('-' || RecSubPart.subpartition_name || ' up to ' || TO_CHAR(RecSubPart.high_Value) ||
        ' rows : ' || RecSubPart.num_rows); 
    END LOOP;
  END LOOP;
end;
{Code}

{Code}
PART_2018_01 up to TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') sub : 4 rows : 31
-SYS_SUBP1258103 up to TO_DATE(' 2018-01-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 14
-SYS_SUBP1258104 up to TO_DATE(' 2018-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 7
-SYS_SUBP1258105 up to TO_DATE(' 2018-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 10
-SYS_SUBP1258106 up to TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 0
PART_2018_02 up to TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') sub : 4 rows : 7
-SYS_SUBP1258107 up to TO_DATE(' 2018-01-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 0
-SYS_SUBP1258108 up to TO_DATE(' 2018-01-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 0
-SYS_SUBP1258109 up to TO_DATE(' 2018-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 0
-SYS_SUBP1258110 up to TO_DATE(' 2018-02-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') rows : 7


That is the split results in too many partitions and they loose their original names. The merge has alos merged the subpartition (7+14) into one
So you need to identify and rename/split/drop (the superfluous ones) them.

repeat for february and march.

Test bei selecting both using the FROM t part (x) subpart(y)-syntax and WHERE date between to make sure data is where it should be (EXCHANGE PARTITION has WITHOUT VALIDATION...).

regards,

PS : I believe the high_value comes from the "VALUES LESS THAN"-clause so it is exclusive.

Stefano Vasi, April 17, 2018 - 10:34 am UTC

Just tried and it works!

Thanks

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.