Skip to Main Content
  • Questions

  • Splitting a partition of a range partitioned table into multiple partitions based on num_rows column.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Gaurav.

Asked: November 28, 2015 - 3:54 am UTC

Last updated: November 28, 2015 - 11:18 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

I have a 220 GB (range-partitioned)table which is split into 65 partitions. The first 59 partitions contain around 2 crores of records each but after that each partition contains over 4 crores records. The 65th partition contains around 12 crore records. So i decided to merge the last 6 partitions(p59,p62,p63,p64,p65,p60) into one partition(p60). And then split the resulting partion p60 serially into several partitions containing 1.5 crore records each. Also i would like to tell 60th partition has a (maxvalue) clause associated with it.

Merging of all the partitions into p60 is successfully done, which now contains around 43 crore records. Now i want to know how would it be possible to split p60 partition into 29 different partitions, each containing around 1.5 crores records.

Regards,
Gaurav Pant

and Connor said...

You can split the partitions with 'alter table split partition' but that means repeating that task 30times to do all your splits. You may be better off

a) creating a table with the 30 partitions you need
b) loading that from the current table
c) using 'exchange partition'

That way you are only scanning/moving that big chunk of data once.

In terms of working out partition ranges, you can use NTILE for that, eg

SQL> create table T
  2  as select rownum  + trunc(dbms_Random.value(0,10000)) x
  3  from
  4   ( select 1 from dual connect by level <= 1000 ),
  5   ( select 1 from dual connect by level <= 1000 )
  6  /

Table created.

SQL> select min(x), max(x) from t;

    MIN(X)     MAX(X)
---------- ----------
       110    1009842

SQL> select nt, min(x), max(x)
  2  from (
  3  select ntile(30) over ( order by x ) nt, x from t
  4  )
  5  group by nt
  6  order by 1;

        NT     MIN(X)     MAX(X)
---------- ---------- ----------
         1        110      38287
         2      38287      71607
         3      71609     104981
         4     104982     138386
         5     138386     171655
         6     171656     205026
         7     205026     238330
         8     238331     271687
         9     271688     305043
        10     305043     338286
        11     338287     371692
        12     371692     405003
        13     405004     438293
        14     438294     471708
        15     471711     505030
        16     505031     538290
        17     538290     571719
        18     571719     605020
        19     605021     638344
        20     638345     671678
        21     671679     705100
        22     705102     738352
        23     738352     771675
        24     771678     804972
        25     804972     838313
        26     838315     871695
        27     871697     905005
        28     905005     938360
        29     938362     971692
        30     971693    1009842

30 rows selected.

SQL>



Hope this helps

Rating

  (1 rating)

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

Comments

Gaurav Pant, November 28, 2015 - 5:32 am UTC

Hi Tom,

Thanks for the update.

I am sharing some of the details/output of the work i did on this table.

As per the discussions with my supervisor, i am told to follow range partitioning and hence not allowed to follow any other other partitioning method. So please tell me how to proceed with splitting these partitions based on the HIGH_VALUE and NUM_ROWS column.

SQL> select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,NUM_ROWS from dba_tab_partitions where TABLE_NAME='p';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p01 '0030000002002225670' 1 12423636
p02 '0030000002004778809' 2 12870968
p03 '0030000002007418007' 3 13531600
p04 '0030000002009888422' 4 13221436
p05 '0030000002013294051' 5 13416920
p06 '0030000002017359374' 6 13022572
p07 '0030000002019719140' 7 13906988
p08 '0030000002022165592' 8 13829624
p09 '0030000002023532213' 9 13918500
p10 '0030000002024913904' 10 13905772
p11 '0030000002026795912' 11 13789632

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p12 '0030000002029244015' 12 14120344
p13 '0030000002031022280' 13 14213292
p14 '0030000002033783644' 14 13897496
p15 '0030000002037498109' 15 14526408
p16 '0030000002040469842' 16 14498600
p17 '0030000002043440206' 17 14943852
p18 '0030000002046509969' 18 14555160
p19 '0030000002048829689' 19 15357908
p20 '0030000002051171267' 20 14756832
p21 '0030000002056039743' 21 16232704
p22 '0030000002058709364' 22 15828960

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p23 '0030000002062718009' 23 15826892
p24 '0030000002067704091' 24 15915284
p25 '0030000002071410402' 25 17051456
p26 '0030000002075228888' 26 15827224
p27 '0030000002079472636' 27 16304824
p28 '0030000002083176709' 28 15702588
p29 '0030000002087417615' 29 16800480
p30 '0030000002092398620' 30 17314556
p31 '0030000002096010494' 31 16741748
p32 '0030000002100033408' 32 16984380
p33 '0030000002104172595' 33 19092412

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p34 '0030000002112005886' 34 16800120
p35 '0030000002122082460' 35 20053680
p36 '0030000002129710599' 36 26885196
p37 '0030000002137239162' 37 26916968
p38 '0030000002145928657' 38 31663920
p39 '0030000002151651295' 39 33616456
p40 '0030000002158228899' 40 33301456
p41 '0030000002164800117' 41 32228936
p42 '0030000002172538740' 42 32084564
p43 '0030000002180485694' 43 33954672
p44 '0030000002186631586' 44 34087276

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p45 '0030000002193844571' 45 32760604
p46 '0030000002201374344' 46 34546412
p47 '0030000002209324383' 47 34286256
p48 '0030000002217059901' 48 34688928
p49 '0030000002225113085' 49 33900652
p50 '0030000002232210178' 50 33650636
p51 '0030000002242175427' 51 33300348
p52 '0030000002252244798' 52 33482084
p61 '0030000004999999999' 53 24617756
p53 '0030000005000208067' 54 4019528
p54 '0030000005000941148' 55 14431172

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p55 '0030000005001766183' 56 16599828
p56 '0030000005002429485' 57 19147276
p57 '0030000005003582374' 58 23802008
p58 '0030000005005095893' 59 38098352
p59 '0030000005007012733' 60 38820728
p62 '0030000005009176535' 61 47061000
p63 '0030000005011612665' 62 46126836
p64 '0030000005014771611' 63 56814852
p65 '0030000005900453259' 64 127331632
p60 MAXVALUE 65 72529136

65 rows selected.




After merging the required partitions,the output is as follows:


select PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,NUM_ROWS from dba_tab_partitions where TABLE_NAME='IADVGG';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p01 '0030000002002225670' 1 12423636
p02 '0030000002004778809' 2 12870968
p03 '0030000002007418007' 3 13531600
p04 '0030000002009888422' 4 13221436
p05 '0030000002013294051' 5 13416920
p06 '0030000002017359374' 6 13022572
p07 '0030000002019719140' 7 13906988
p08 '0030000002022165592' 8 13829624
p09 '0030000002023532213' 9 13918500
p10 '0030000002024913904' 10 13905772
p11 '0030000002026795912' 11 13789632

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p12 '0030000002029244015' 12 14120344
p13 '0030000002031022280' 13 14213292
p14 '0030000002033783644' 14 13897496
p15 '0030000002037498109' 15 14526408
p16 '0030000002040469842' 16 14498600
p17 '0030000002043440206' 17 14943852
p18 '0030000002046509969' 18 14555160
p19 '0030000002048829689' 19 15357908
p20 '0030000002051171267' 20 14756832
p21 '0030000002056039743' 21 16232704
p22 '0030000002058709364' 22 15828960

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p23 '0030000002062718009' 23 15826892
p24 '0030000002067704091' 24 15915284
p25 '0030000002071410402' 25 17051456
p26 '0030000002075228888' 26 15827224
p27 '0030000002079472636' 27 16304824
p28 '0030000002083176709' 28 15702588
p29 '0030000002087417615' 29 16800480
p30 '0030000002092398620' 30 17314556
p31 '0030000002096010494' 31 16741748
p32 '0030000002100033408' 32 16984380
p33 '0030000002104172595' 33 19092412

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p34 '0030000002112005886' 34 16800120
p35 '0030000002122082460' 35 20053680
p36 '0030000002129710599' 36 26885196
p37 '0030000002137239162' 37 26916968
p38 '0030000002145928657' 38 31663920
p39 '0030000002151651295' 39 33616456
p40 '0030000002158228899' 40 33301456
p41 '0030000002164800117' 41 32228936
p42 '0030000002172538740' 42 32084564
p43 '0030000002180485694' 43 33954672
p44 '0030000002186631586' 44 34087276

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p45 '0030000002193844571' 45 32760604
p46 '0030000002201374344' 46 34546412
p47 '0030000002209324383' 47 34286256
p48 '0030000002217059901' 48 34688928
p49 '0030000002225113085' 49 33900652
p50 '0030000002232210178' 50 33650636
p51 '0030000002242175427' 51 33300348
p52 '0030000002252244798' 52 33482084
p61 '0030000004999999999' 53 24617756
p53 '0030000005000208067' 54 4019528
p54 '0030000005000941148' 55 14431172

PARTITION_NAME HIGH_VALUE PARTITION_POSITION NUM_ROWS
--------------- ------------------------ ------------------ ----------
p55 '0030000005001766183' 56 16599828
p56 '0030000005002429485' 57 19147276
p57 '0030000005003582374' 58 23802008
p60 MAXVALUE 59
426782536



Please suggest me how to split these these partitions(one by one into 29 partitions) as per the attached output.


Regards,
Gaurav Pant

Connor McDonald
November 28, 2015 - 11:18 am UTC

Ok, to split that last partition, you need some values at where to split, so you run:

SQL> select nt, min(x), max(x)
2 from (
3 select ntile(30) over ( order by c ) nt, c from IADVGG partition ( p60 )
4 )
5 group by nt
6 order by 1;

where 'c' is your partitioning column.

That gives you 30 values, evenly distributed.

Then its just

alter table IADVGG split partition p60 at ( <value30> )
into ( partition p_x, partition p_y);

and then repeat for value29, 28 etc etc




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.