Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, KISHORE.

Asked: July 06, 2016 - 5:08 pm UTC

Last updated: July 11, 2016 - 6:00 am UTC

Version: 11gR2

Viewed 1000+ times

You Asked

Hello Sir
I have a table partitioned on RANGE (daily) (interval partitioning) - there are about 300 partitions .
Now I want to add a sub-partition to each of these based on a value in a different column.
Should I come up with a script with 300 ALTER statements ? if yes, after the sub-partitions are created , should I manually INSERT rows into the sub-partitions ? and what about the future sub-partitions ? should I create a template for the future ones ?

Thanks
Kishore

and Connor said...

When a table is composite partitioned (ie, is partitioned and each partition has subpartitions), the partitions are "logical". They dont have any rows so to speak - they simply are a logical grouping of the subpartitions which *do* have rows (ie, are physical segments on the database).

So converting a partitioned table to a subpartitioned table will mean physically moving the rows.

The easiest way to do this would be with DBMS_REDEFINITION. An example is below, but refer to the documentation for complete usage


SQL> create table t ( x date , y int, z int )
  2  partition by range ( x )
  3  interval ( numtodsinterval(1,'DAY'))
  4  (
  5  partition p1 values less than ( date '2016-01-01' )
  6  );

Table created.

SQL>
SQL> insert into t
  2  select date '2016-01-01' + trunc(rownum/50),
  3         rownum,
  4         rownum
  5  from dual
  6  connect by level <= 4000;

4000 rows created.

SQL>
SQL> alter table T add primary key ( y ) ;

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_Stats('','t')

PL/SQL procedure successfully completed.

SQL>
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      0
SYS_P15705                             49
SYS_P15706                             50
SYS_P15707                             50
SYS_P15708                             50
SYS_P15709                             50
SYS_P15710                             50
SYS_P15711                             50
SYS_P15712                             50
SYS_P15713                             50
SYS_P15714                             50
SYS_P15715                             50
SYS_P15716                             50
SYS_P15717                             50
SYS_P15718                             50
SYS_P15719                             50
SYS_P15720                             50
SYS_P15721                             50
SYS_P15722                             50
SYS_P15723                             50
SYS_P15724                             50
SYS_P15725                             50
SYS_P15726                             50
SYS_P15727                             50
SYS_P15728                             50
SYS_P15729                             50
SYS_P15730                             50
SYS_P15731                             50
SYS_P15732                             50
SYS_P15733                             50
SYS_P15734                             50
SYS_P15735                             50
SYS_P15736                             50
SYS_P15737                             50
SYS_P15738                             50
SYS_P15739                             50
SYS_P15740                             50
SYS_P15741                             50
SYS_P15742                             50
SYS_P15743                             50
SYS_P15744                             50
SYS_P15745                             50
SYS_P15746                             50
SYS_P15747                             50
SYS_P15748                             50
SYS_P15749                             50
SYS_P15750                             50
SYS_P15751                             50
SYS_P15752                             50
SYS_P15753                             50
SYS_P15754                             50
SYS_P15755                             50
SYS_P15756                             50
SYS_P15757                             50
SYS_P15758                             50
SYS_P15759                             50
SYS_P15760                             50
SYS_P15761                             50
SYS_P15762                             50
SYS_P15763                             50
SYS_P15764                             50
SYS_P15765                             50
SYS_P15766                             50
SYS_P15767                             50
SYS_P15768                             50
SYS_P15769                             50
SYS_P15770                             50
SYS_P15771                             50
SYS_P15772                             50
SYS_P15773                             50
SYS_P15774                             50
SYS_P15775                             50
SYS_P15776                             50
SYS_P15777                             50
SYS_P15778                             50
SYS_P15779                             50
SYS_P15780                             50
SYS_P15781                             50
SYS_P15782                             50
SYS_P15783                             50
SYS_P15784                             50
SYS_P15785                              1

82 rows selected.

SQL>
SQL> create table new_t ( x date , y int, z int )
  2  partition by range ( x ) interval ( numtodsinterval(1,'DAY'))
  3  subpartition by hash (y)
  4  subpartition template
  5  ( subpartition a, subpartition b, subpartition c, subpartition d )
  6  (
  7  partition p1 values less than ( date '2016-01-01' )
  8  );

Table created.

SQL>
SQL> alter table new_T add primary key ( y ) ;

Table altered.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T', 'NEW_T');

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T', 'NEW_T');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_Stats('','t',granularity=>'ALL')

PL/SQL procedure successfully completed.

SQL> select partition_name, subpartition_name, num_rows
  2  from user_tab_subpartitions
  3  where table_name = 'T';

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
P1                             P1_A                                    0
P1                             P1_B                                    0
P1                             P1_C                                    0
P1                             P1_D                                    0
SYS_P15790                     SYS_SUBP15786                           8
SYS_P15790                     SYS_SUBP15787                          13
SYS_P15790                     SYS_SUBP15788                          13
SYS_P15790                     SYS_SUBP15789                          15
SYS_P15795                     SYS_SUBP15791                          12
SYS_P15795                     SYS_SUBP15792                          15
SYS_P15795                     SYS_SUBP15793                          11
SYS_P15795                     SYS_SUBP15794                          12
SYS_P15800                     SYS_SUBP15796                           9
SYS_P15800                     SYS_SUBP15797                          10
SYS_P15800                     SYS_SUBP15798                          21
SYS_P15800                     SYS_SUBP15799                          10
SYS_P15805                     SYS_SUBP15801                          14
SYS_P15805                     SYS_SUBP15802                          20
SYS_P15805                     SYS_SUBP15803                           8
SYS_P15805                     SYS_SUBP15804                           8
SYS_P15810                     SYS_SUBP15806                          10
SYS_P15810                     SYS_SUBP15807                          15
SYS_P15810                     SYS_SUBP15808                          15
SYS_P15810                     SYS_SUBP15809                          10
SYS_P15815                     SYS_SUBP15811                          13
SYS_P15815                     SYS_SUBP15812                          12
SYS_P15815                     SYS_SUBP15813                          10
SYS_P15815                     SYS_SUBP15814                          15
SYS_P15820                     SYS_SUBP15816                          12
SYS_P15820                     SYS_SUBP15817                           7
SYS_P15820                     SYS_SUBP15818                          17
SYS_P15820                     SYS_SUBP15819                          14
SYS_P15825                     SYS_SUBP15821                          11
SYS_P15825                     SYS_SUBP15822                          16
SYS_P15825                     SYS_SUBP15823                          13
SYS_P15825                     SYS_SUBP15824                          10
SYS_P15830                     SYS_SUBP15826                          14
SYS_P15830                     SYS_SUBP15827                          14
SYS_P15830                     SYS_SUBP15828                          11
SYS_P15830                     SYS_SUBP15829                          11
SYS_P15835                     SYS_SUBP15831                          11
SYS_P15835                     SYS_SUBP15832                          12
SYS_P15835                     SYS_SUBP15833                          13
SYS_P15835                     SYS_SUBP15834                          14
SYS_P15840                     SYS_SUBP15836                          13
SYS_P15840                     SYS_SUBP15837                           9
SYS_P15840                     SYS_SUBP15838                          16
SYS_P15840                     SYS_SUBP15839                          12
SYS_P15845                     SYS_SUBP15841                          16
SYS_P15845                     SYS_SUBP15842                           7
SYS_P15845                     SYS_SUBP15843                          13
SYS_P15845                     SYS_SUBP15844                          14
SYS_P15850                     SYS_SUBP15846                          10
SYS_P15850                     SYS_SUBP15847                          13
SYS_P15850                     SYS_SUBP15848                          11
SYS_P15850                     SYS_SUBP15849                          16
SYS_P15855                     SYS_SUBP15851                           6
SYS_P15855                     SYS_SUBP15852                          15
SYS_P15855                     SYS_SUBP15853                          13
SYS_P15855                     SYS_SUBP15854                          16
SYS_P15860                     SYS_SUBP15856                          18
SYS_P15860                     SYS_SUBP15857                           8
SYS_P15860                     SYS_SUBP15858                          12
SYS_P15860                     SYS_SUBP15859                          12
SYS_P15865                     SYS_SUBP15861                           7
SYS_P15865                     SYS_SUBP15862                          12
SYS_P15865                     SYS_SUBP15863                          13
SYS_P15865                     SYS_SUBP15864                          18
SYS_P15870                     SYS_SUBP15866                          14
SYS_P15870                     SYS_SUBP15867                          10
SYS_P15870                     SYS_SUBP15868                          16
SYS_P15870                     SYS_SUBP15869                          10
SYS_P15875                     SYS_SUBP15871                          15
SYS_P15875                     SYS_SUBP15872                          11
SYS_P15875                     SYS_SUBP15873                          11
SYS_P15875                     SYS_SUBP15874                          13
SYS_P15880                     SYS_SUBP15876                           7
SYS_P15880                     SYS_SUBP15877                          14
SYS_P15880                     SYS_SUBP15878                          11
SYS_P15880                     SYS_SUBP15879                          18
SYS_P15885                     SYS_SUBP15881                          13
SYS_P15885                     SYS_SUBP15882                          11
SYS_P15885                     SYS_SUBP15883                          13
SYS_P15885                     SYS_SUBP15884                          13
SYS_P15890                     SYS_SUBP15886                          12
SYS_P15890                     SYS_SUBP15887                          11
SYS_P15890                     SYS_SUBP15888                          13
SYS_P15890                     SYS_SUBP15889                          14
SYS_P15895                     SYS_SUBP15891                           9
SYS_P15895                     SYS_SUBP15892                          12
SYS_P15895                     SYS_SUBP15893                          16
SYS_P15895                     SYS_SUBP15894                          13
SYS_P15900                     SYS_SUBP15896                          18
SYS_P15900                     SYS_SUBP15897                           8
SYS_P15900                     SYS_SUBP15898                           9
SYS_P15900                     SYS_SUBP15899                          15

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
SYS_P15905                     SYS_SUBP15901                          18
SYS_P15905                     SYS_SUBP15902                          11
SYS_P15905                     SYS_SUBP15903                          10
SYS_P15905                     SYS_SUBP15904                          11
SYS_P15910                     SYS_SUBP15906                          11
SYS_P15910                     SYS_SUBP15907                          14
SYS_P15910                     SYS_SUBP15908                          14
SYS_P15910                     SYS_SUBP15909                          11
SYS_P15915                     SYS_SUBP15911                           9
SYS_P15915                     SYS_SUBP15912                          11
SYS_P15915                     SYS_SUBP15913                          16
SYS_P15915                     SYS_SUBP15914                          14
SYS_P15920                     SYS_SUBP15916                          15
SYS_P15920                     SYS_SUBP15917                          12
SYS_P15920                     SYS_SUBP15918                           9
SYS_P15920                     SYS_SUBP15919                          14
SYS_P15925                     SYS_SUBP15921                          18
SYS_P15925                     SYS_SUBP15922                           8
SYS_P15925                     SYS_SUBP15923                          11
SYS_P15925                     SYS_SUBP15924                          13
SYS_P15930                     SYS_SUBP15926                          12
SYS_P15930                     SYS_SUBP15927                          16
SYS_P15930                     SYS_SUBP15928                           9
SYS_P15930                     SYS_SUBP15929                          13
SYS_P15935                     SYS_SUBP15931                          11
SYS_P15935                     SYS_SUBP15932                          17
SYS_P15935                     SYS_SUBP15933                          14
SYS_P15935                     SYS_SUBP15934                           8
SYS_P15940                     SYS_SUBP15936                          18
SYS_P15940                     SYS_SUBP15937                          12
SYS_P15940                     SYS_SUBP15938                           8
SYS_P15940                     SYS_SUBP15939                          12
SYS_P15945                     SYS_SUBP15941                          10
SYS_P15945                     SYS_SUBP15942                          16
SYS_P15945                     SYS_SUBP15943                          16
SYS_P15945                     SYS_SUBP15944                           8
SYS_P15950                     SYS_SUBP15946                          16
SYS_P15950                     SYS_SUBP15947                          12
SYS_P15950                     SYS_SUBP15948                          12
SYS_P15950                     SYS_SUBP15949                          10
SYS_P15955                     SYS_SUBP15951                           9
SYS_P15955                     SYS_SUBP15952                          18
SYS_P15955                     SYS_SUBP15953                          14
SYS_P15955                     SYS_SUBP15954                           9
SYS_P15960                     SYS_SUBP15956                          11
SYS_P15960                     SYS_SUBP15957                          18
SYS_P15960                     SYS_SUBP15958                          12
SYS_P15960                     SYS_SUBP15959                           9
SYS_P15965                     SYS_SUBP15961                          11
SYS_P15965                     SYS_SUBP15962                          11
SYS_P15965                     SYS_SUBP15963                          14
SYS_P15965                     SYS_SUBP15964                          14
SYS_P15970                     SYS_SUBP15966                          11
SYS_P15970                     SYS_SUBP15967                          16
SYS_P15970                     SYS_SUBP15968                          15
SYS_P15970                     SYS_SUBP15969                           8
SYS_P15975                     SYS_SUBP15971                          17
SYS_P15975                     SYS_SUBP15972                          12
SYS_P15975                     SYS_SUBP15973                          15
SYS_P15975                     SYS_SUBP15974                           6
SYS_P15980                     SYS_SUBP15976                          11
SYS_P15980                     SYS_SUBP15977                          14
SYS_P15980                     SYS_SUBP15978                          13
SYS_P15980                     SYS_SUBP15979                          12
SYS_P15985                     SYS_SUBP15981                          11
SYS_P15985                     SYS_SUBP15982                          11
SYS_P15985                     SYS_SUBP15983                          11
SYS_P15985                     SYS_SUBP15984                          17
SYS_P15990                     SYS_SUBP15986                           9
SYS_P15990                     SYS_SUBP15987                          10
SYS_P15990                     SYS_SUBP15988                          20
SYS_P15990                     SYS_SUBP15989                          11
SYS_P15995                     SYS_SUBP15991                           6
SYS_P15995                     SYS_SUBP15992                          13
SYS_P15995                     SYS_SUBP15993                          13
SYS_P15995                     SYS_SUBP15994                          18
SYS_P16000                     SYS_SUBP15996                          11
SYS_P16000                     SYS_SUBP15997                          19
SYS_P16000                     SYS_SUBP15998                          10
SYS_P16000                     SYS_SUBP15999                          10
SYS_P16005                     SYS_SUBP16001                          15
SYS_P16005                     SYS_SUBP16002                          13
SYS_P16005                     SYS_SUBP16003                          10
SYS_P16005                     SYS_SUBP16004                          12
SYS_P16010                     SYS_SUBP16006                          10
SYS_P16010                     SYS_SUBP16007                          11
SYS_P16010                     SYS_SUBP16008                          14
SYS_P16010                     SYS_SUBP16009                          15
SYS_P16015                     SYS_SUBP16011                          11
SYS_P16015                     SYS_SUBP16012                          13
SYS_P16015                     SYS_SUBP16013                          13
SYS_P16015                     SYS_SUBP16014                          13
SYS_P16020                     SYS_SUBP16016                          21
SYS_P16020                     SYS_SUBP16017                           5
SYS_P16020                     SYS_SUBP16018                          14
SYS_P16020                     SYS_SUBP16019                          10

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
SYS_P16025                     SYS_SUBP16021                          11
SYS_P16025                     SYS_SUBP16022                          12
SYS_P16025                     SYS_SUBP16023                          10
SYS_P16025                     SYS_SUBP16024                          17
SYS_P16030                     SYS_SUBP16026                          15
SYS_P16030                     SYS_SUBP16027                          12
SYS_P16030                     SYS_SUBP16028                           9
SYS_P16030                     SYS_SUBP16029                          14
SYS_P16035                     SYS_SUBP16031                          13
SYS_P16035                     SYS_SUBP16032                           8
SYS_P16035                     SYS_SUBP16033                          11
SYS_P16035                     SYS_SUBP16034                          18
SYS_P16040                     SYS_SUBP16036                          12
SYS_P16040                     SYS_SUBP16037                          11
SYS_P16040                     SYS_SUBP16038                          11
SYS_P16040                     SYS_SUBP16039                          16
SYS_P16045                     SYS_SUBP16041                          11
SYS_P16045                     SYS_SUBP16042                          11
SYS_P16045                     SYS_SUBP16043                          17
SYS_P16045                     SYS_SUBP16044                          11
SYS_P16050                     SYS_SUBP16046                          10
SYS_P16050                     SYS_SUBP16047                           9
SYS_P16050                     SYS_SUBP16048                          17
SYS_P16050                     SYS_SUBP16049                          14
SYS_P16055                     SYS_SUBP16051                          15
SYS_P16055                     SYS_SUBP16052                           8
SYS_P16055                     SYS_SUBP16053                          19
SYS_P16055                     SYS_SUBP16054                           8
SYS_P16060                     SYS_SUBP16056                          20
SYS_P16060                     SYS_SUBP16057                          11
SYS_P16060                     SYS_SUBP16058                          10
SYS_P16060                     SYS_SUBP16059                           9
SYS_P16065                     SYS_SUBP16061                          10
SYS_P16065                     SYS_SUBP16062                          14
SYS_P16065                     SYS_SUBP16063                          14
SYS_P16065                     SYS_SUBP16064                          12
SYS_P16070                     SYS_SUBP16066                          12
SYS_P16070                     SYS_SUBP16067                          12
SYS_P16070                     SYS_SUBP16068                          14
SYS_P16070                     SYS_SUBP16069                          12
SYS_P16075                     SYS_SUBP16071                          11
SYS_P16075                     SYS_SUBP16072                          16
SYS_P16075                     SYS_SUBP16073                          14
SYS_P16075                     SYS_SUBP16074                           9
SYS_P16080                     SYS_SUBP16076                          12
SYS_P16080                     SYS_SUBP16077                          15
SYS_P16080                     SYS_SUBP16078                          10
SYS_P16080                     SYS_SUBP16079                          13
SYS_P16085                     SYS_SUBP16081                          10
SYS_P16085                     SYS_SUBP16082                          13
SYS_P16085                     SYS_SUBP16083                          15
SYS_P16085                     SYS_SUBP16084                          12
SYS_P16090                     SYS_SUBP16086                          21
SYS_P16090                     SYS_SUBP16087                           9
SYS_P16090                     SYS_SUBP16088                           8
SYS_P16090                     SYS_SUBP16089                          12
SYS_P16095                     SYS_SUBP16091                          11
SYS_P16095                     SYS_SUBP16092                          16
SYS_P16095                     SYS_SUBP16093                          12
SYS_P16095                     SYS_SUBP16094                          11
SYS_P16100                     SYS_SUBP16096                          15
SYS_P16100                     SYS_SUBP16097                           8
SYS_P16100                     SYS_SUBP16098                          15
SYS_P16100                     SYS_SUBP16099                          12
SYS_P16105                     SYS_SUBP16101                          13
SYS_P16105                     SYS_SUBP16102                          12
SYS_P16105                     SYS_SUBP16103                          11
SYS_P16105                     SYS_SUBP16104                          14
SYS_P16110                     SYS_SUBP16106                          15
SYS_P16110                     SYS_SUBP16107                          13
SYS_P16110                     SYS_SUBP16108                          10
SYS_P16110                     SYS_SUBP16109                          12
SYS_P16115                     SYS_SUBP16111                          14
SYS_P16115                     SYS_SUBP16112                          11
SYS_P16115                     SYS_SUBP16113                          15
SYS_P16115                     SYS_SUBP16114                          10
SYS_P16120                     SYS_SUBP16116                          15
SYS_P16120                     SYS_SUBP16117                          12
SYS_P16120                     SYS_SUBP16118                          14
SYS_P16120                     SYS_SUBP16119                           9
SYS_P16125                     SYS_SUBP16121                          11
SYS_P16125                     SYS_SUBP16122                          11
SYS_P16125                     SYS_SUBP16123                          15
SYS_P16125                     SYS_SUBP16124                          13
SYS_P16130                     SYS_SUBP16126                          12
SYS_P16130                     SYS_SUBP16127                          18
SYS_P16130                     SYS_SUBP16128                          10
SYS_P16130                     SYS_SUBP16129                          10
SYS_P16135                     SYS_SUBP16131                          14
SYS_P16135                     SYS_SUBP16132                          16
SYS_P16135                     SYS_SUBP16133                           8
SYS_P16135                     SYS_SUBP16134                          12
SYS_P16140                     SYS_SUBP16136                          11
SYS_P16140                     SYS_SUBP16137                          13
SYS_P16140                     SYS_SUBP16138                          13
SYS_P16140                     SYS_SUBP16139                          13

PARTITION_NAME                 SUBPARTITION_NAME                NUM_ROWS
------------------------------ ------------------------------ ----------
SYS_P16145                     SYS_SUBP16141                          11
SYS_P16145                     SYS_SUBP16142                           7
SYS_P16145                     SYS_SUBP16143                          13
SYS_P16145                     SYS_SUBP16144                          19
SYS_P16150                     SYS_SUBP16146                          11
SYS_P16150                     SYS_SUBP16147                          11
SYS_P16150                     SYS_SUBP16148                          12
SYS_P16150                     SYS_SUBP16149                          16
SYS_P16155                     SYS_SUBP16151                          14
SYS_P16155                     SYS_SUBP16152                          14
SYS_P16155                     SYS_SUBP16153                          13
SYS_P16155                     SYS_SUBP16154                           9
SYS_P16160                     SYS_SUBP16156                          12
SYS_P16160                     SYS_SUBP16157                          10
SYS_P16160                     SYS_SUBP16158                          15
SYS_P16160                     SYS_SUBP16159                          13
SYS_P16165                     SYS_SUBP16161                           9
SYS_P16165                     SYS_SUBP16162                          16
SYS_P16165                     SYS_SUBP16163                          13
SYS_P16165                     SYS_SUBP16164                          12
SYS_P16170                     SYS_SUBP16166                          15
SYS_P16170                     SYS_SUBP16167                          13
SYS_P16170                     SYS_SUBP16168                          10
SYS_P16170                     SYS_SUBP16169                          12
SYS_P16175                     SYS_SUBP16171                          14
SYS_P16175                     SYS_SUBP16172                          13
SYS_P16175                     SYS_SUBP16173                          10
SYS_P16175                     SYS_SUBP16174                          13
SYS_P16180                     SYS_SUBP16176                           9
SYS_P16180                     SYS_SUBP16177                          15
SYS_P16180                     SYS_SUBP16178                          13
SYS_P16180                     SYS_SUBP16179                          13
SYS_P16185                     SYS_SUBP16181                           9
SYS_P16185                     SYS_SUBP16182                          16
SYS_P16185                     SYS_SUBP16183                          17
SYS_P16185                     SYS_SUBP16184                           8
SYS_P16190                     SYS_SUBP16186                           0
SYS_P16190                     SYS_SUBP16187                           1
SYS_P16190                     SYS_SUBP16188                           0
SYS_P16190                     SYS_SUBP16189                           0

328 rows selected.

SQL>



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

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.