Skip to Main Content
  • Questions

  • Upper bound length constraint for list partitioned tables (ORA-14116)

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Philip.

Asked: September 05, 2017 - 3:32 pm UTC

Last updated: September 06, 2017 - 2:43 pm UTC

Version: 12cR1

Viewed 1000+ times

You Asked

Dear Tom,

We do have some table where partitions will be created frequently:
CREATE TABLE partitioned_table 
(
    someUsefulPartitionIndicator NUMBER (*,0),
    someData varchar2(4000)
)
partition by list (someUsefulPartitionIndicator)
(partition p1 values (1));

declare
    i number;
begin
    FOR i IN 2..10000
    LOOP
        execute immediate 'alter table partitioned_table add partition p'||i||' values ('||i||')';    
    END LOOP;
end;



Now - we do have a housekeeping job that merges partitions into more useful partitions according to the way we will access our data:
declare
    i number;
begin
    FOR i IN 2..5000
    LOOP
        execute immediate 'alter table partitioned_table merge partitions p2,p'||2*i||' into partition p2';
    END LOOP;
END;


Unfortunately, that job fails:
ORA-14116: partition bound of partition "P2" is too long
ORA-06512: at line 6


Investigating partition 2 reviels:

select high_value from user_tab_partitions
where partition_name = 'P2'
and table_name = 'PARTITIONED_TABLE'

'1550, 1548, ..., 2'


Well, that String has length 4096.

Is there any way of circumnavigating this? unfortunately, partition by range does not work for us since then we lose the control on what partitions actually to merge (only adjacent can be merged).

Thank you for your Help,
Philip

and Chris said...

I feel like you're approaching this backwards. Instead of placing all values in their own partition, then combining those that have too few values or whatever, do the opposite.

Stick everything in one partition. Then split out from that as needed. You can do this with the default partition. For example:

create table partitioned_table (
  someusefulpartitionindicator number (*,0),
  somedata varchar2(4000)
)
partition by list (someusefulpartitionindicator)
(partition pdef values (default));

alter table partitioned_table split partition pdef into (
  partition p1 values (1),
  partition p2 values (2),
  partition pdef
);

select partition_name, high_value 
from   user_tab_partitions
where  table_name = 'PARTITIONED_TABLE';

PARTITION_NAME  HIGH_VALUE  
P1              1           
P2              2           
PDEF            default


This stops you hitting the 4KB limit for partition keys. And (potentially) the limit of 1024K - 1 partitions per table.

Rating

  (3 ratings)

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

Comments

Still the problem of the length for the upper bound persists

Philip P�tsch, September 06, 2017 - 10:33 am UTC

Dear Tom,

thanks a lot for your answer. I very much like the approach of a default partition, however, that does not really tackle the root cause of my problem. Now let's go as suggested:

create table partitioned_table (
  someUsefulPartitionIndicator number (*,0),
  somedata varchar2(4000)
)
partition by list (someusefulpartitionindicator)
(partition pdef values (default));


In the meantime, a lot of data as been gathered in my default partitoin and I would like to split one off. I determined which someUsefulPartitionIndicator's I would like to have in that partition. So I try: 

 
alter table partitioned_table split partition pdef into (
  partition p1 values (1552, 1550, 1548, 1546, 1544, 1542, 1540, 1538, 1536, 1534, 1532, 1530, 1528, 1526, 1524, 1522, 1520, 1518, 1516, 1514, 1512, 1510, 1508, 1506, 1504, 1502, 1500, 1498, 1496, 1494, 1492, 1490, 1488, 1486, 1484, 1482, 1480, 1478, 1476, 1474, 1472, 1470, 1468, 1466, 1464, 1462, 1460, 1458, 1456, 1454, 1452, 1450, 1448, 1446, 1444, 1442, 1440, 1438, 1436, 1434, 1432, 1430, 1428, 1426, 1424, 1422, 1420, 1418, 1416, 1414, 1412, 1410, 1408, 1406, 1404, 1402, 1400, 1398, 1396, 1394, 1392, 1390, 1388, 1386, 1384, 1382, 1380, 1378, 1376, 1374, 1372, 1370, 1368, 1366, 1364, 1362, 1360, 1358, 1356, 1354, 1352, 1350, 1348, 1346, 1344, 1342, 1340, 1338, 1336, 1334, 1332, 1330, 1328, 1326, 1324, 1322, 1320, 1318, 1316, 1314, 1312, 1310, 1308, 1306, 1304, 1302, 1300, 1298, 1296, 1294, 1292, 1290, 1288, 1286, 1284, 1282, 1280, 1278, 1276, 1274, 1272, 1270, 1268, 1266, 1264, 1262, 1260, 1258, 1256, 1254, 1252, 1250, 1248, 1246, 1244, 1242, 1240, 1238, 1236, 1234, 1232, 1230, 1228, 1226, 1224, 1222, 1220, 1218, 1216, 1214, 1212, 1210, 1208, 1206, 1204, 1202, 1200, 1198, 1196, 1194, 1192, 1190, 1188, 1186, 1184, 1182, 1180, 1178, 1176, 1174, 1172, 1170, 1168, 1166, 1164, 1162, 1160, 1158, 1156, 1154, 1152, 1150, 1148, 1146, 1144, 1142, 1140, 1138, 1136, 1134, 1132, 1130, 1128, 1126, 1124, 1122, 1120, 1118, 1116, 1114, 1112, 1110, 1108, 1106, 1104, 1102, 1100, 1098, 1096, 1094, 1092, 1090, 1088, 1086, 1084, 1082, 1080, 1078, 1076, 1074, 1072, 1070, 1068, 1066, 1064, 1062, 1060, 1058, 1056, 1054, 1052, 1050, 1048, 1046, 1044, 1042, 1040, 1038, 1036, 1034, 1032, 1030, 1028, 1026, 1024, 1022, 1020, 1018, 1016, 1014, 1012, 1010, 1008, 1006, 1004, 1002, 1000, 998, 996, 994, 992, 990, 988, 986, 984, 982, 980, 978, 976, 974, 972, 970, 968, 966, 964, 962, 960, 958, 956, 954, 952, 950, 948, 946, 944, 942, 940, 938, 936, 934, 932, 930, 928, 926, 924, 922, 920, 918, 916, 914, 912, 910, 908, 906, 904, 902, 900, 898, 896, 894, 892, 890, 888, 886, 884, 882, 880, 878, 876, 874, 
872, 870, 868, 866, 864, 862, 860, 858, 856, 854, 852, 850, 848, 846, 844, 842, 840, 838, 836, 834, 832, 830, 828, 826, 824, 822, 820, 818, 816, 814, 812, 810, 808, 806, 804, 802, 800, 798, 796, 794, 792, 790, 788, 786, 784, 782, 780, 778, 776, 774, 772, 770, 768, 766, 764, 762, 760, 758, 756, 754, 752, 750, 748, 746, 744, 742, 740, 738, 736, 734, 732, 730, 728, 726, 724, 722, 720, 718, 716, 714, 712, 710, 708, 706, 704, 702, 700, 698, 696, 694, 692, 690, 688, 686, 684, 682, 680, 678, 676, 674, 672, 670, 668, 666, 664, 662, 660, 658, 656, 654, 652, 650, 648, 646, 644, 642, 640, 638, 636, 634, 632, 630, 628, 626, 624, 622, 620, 618, 616, 614, 612, 610, 608, 606, 604, 602, 600, 598, 596, 594, 592, 590, 588, 586, 584, 582, 580, 578, 576, 574, 572, 570, 568, 566, 564, 562, 560, 558, 556, 554, 552, 550, 548, 546, 544, 542, 540, 538, 536, 534, 532, 530, 528, 526, 524, 522, 520, 518, 516, 514, 512, 510, 508, 506, 504, 502, 500, 498, 496, 494, 492, 490, 488, 486, 484, 482, 480, 478, 476, 474, 472, 470, 468, 466, 464, 462, 460, 458, 456, 454, 452, 450, 448, 446, 444, 442, 440, 438, 436, 434, 432, 430, 428, 426, 424, 422, 420, 418, 416, 414, 412, 410, 408, 406, 404, 402, 400, 398, 396, 394, 392, 390, 388, 386, 384, 382, 380, 378, 376, 374, 372, 370, 368, 366, 364, 362, 360, 358, 356, 354, 352, 350, 348, 346, 344, 342, 340, 338, 336, 334, 332, 330, 328, 326, 324, 322, 320, 318, 316, 314, 312, 310, 308, 306, 304, 302, 300, 298, 296, 294, 292, 290, 288, 286, 284, 282, 280, 278, 276, 274, 272, 270, 268, 266, 264, 262, 260, 258, 256, 254, 252, 250, 248, 246, 244, 242, 240, 238, 236, 234, 232, 230, 228, 226, 224, 222, 220, 218, 216, 214, 212, 210, 208, 206, 204, 202, 200, 198, 196, 194, 192, 190, 188, 186, 184, 182, 180, 178, 176, 174, 172, 170, 168, 166, 164, 162, 160, 158, 156, 154, 152, 150, 148, 146, 144, 142, 140, 138, 136, 134, 132, 130, 128, 126, 124, 122, 120, 118, 116, 114, 112, 110, 108, 106, 104, 102, 100, 98, 96, 94, 92, 90, 88, 86, 84, 82, 80, 78, 76, 74, 72, 70, 68, 
66, 64, 62, 60, 58, 56, 54, 52, 50, 48, 46, 44, 42, 40, 38, 36, 34, 32, 30, 28, 26, 24, 22, 20, 18, 16, 14, 12, 10, 8, 6, 4, 2),
  partition pdef
);

alter table partitioned_table split partition pdef into (
  partition p1 values (1550, 1548, 1546, 1544, 1542, 1540, 1538, 1536, 1534, 1532, 1530, 1528, 1526, 1524, 1522, 1520, 1518, 1516, 1514, 1512, 1510, 1508, 1506, 1504, 1502, 1500, 1498, 1496, 1494, 1492, 1490, 1488, 1486, 1484, 1482, 1480, 1478, 1476, 1474, 1472, 1470, 1468, 1466, 1464, 1462, 1460, 1458, 1456, 1454, 1452, 1450, 1448, 1446, 1444, 1442, 1440, 1438, 1436, 1434, 1432, 1430, 1428, 1426, 1424, 1422, 1420, 1418, 1416, 1414, 1412, 1410, 1408, 1406, 1404, 1402, 1400, 1398, 1396, 1394, 1392, 1390, 1388, 1386, 1384, 1382, 1380, 1378, 1376, 1374, 1372, 1370, 1368, 1366, 1364, 1362, 1360, 1358, 1356, 1354, 1352, 1350, 1348, 1346, 1344, 1342, 1340, 1338, 1336, 1334, 1332, 1330, 1328, 1326, 1324, 1322, 1320, 1318, 1316, 1314, 1312, 1310, 1308, 1306, 1304, 1302, 1300, 1298, 1296, 1294, 1292, 1290, 1288, 1286, 1284, 1282, 1280, 1278, 1276, 1274, 1272, 1270, 1268, 1266, 1264, 1262, 1260, 1258, 1256, 1254, 1252, 1250, 1248, 1246, 1244, 1242, 1240, 1238, 1236, 1234, 1232, 1230, 1228, 1226, 1224, 1222, 1220, 1218, 1216, 1214, 1212, 1210, 1208, 1206, 1204, 1202, 1200, 1198, 1196, 1194, 1192, 1190, 1188, 1186, 1184, 1182, 1180, 1178, 1176, 1174, 1172, 1170, 1168, 1166, 1164, 1162, 1160, 1158, 1156, 1154, 1152, 1150, 1148, 1146, 1144, 1142, 1140, 1138, 1136, 1134, 1132, 1130, 1128, 1126, 1124, 1122, 1120, 1118, 1116, 1114, 1112, 1110, 1108, 1106, 1104, 1102, 1100, 1098, 1096, 1094, 1092, 1090, 1088, 1086, 1084, 1082, 1080, 1078, 1076, 1074, 1072, 1070, 1068, 1066, 1064, 1062, 1060, 1058, 1056, 1054, 1052, 1050, 1048, 1046, 1044, 1042, 1040, 1038, 1036, 1034, 1032, 1030, 1028, 1026, 1024, 1022, 1020, 1018, 1016, 1014, 1012, 1010, 1008, 1006, 1004, 1002, 1000, 998, 996, 994, 992, 990, 988, 986, 984, 982, 980, 978, 976, 974, 972, 970, 968, 966, 964, 962, 960, 958, 956, 954, 952, 950, 948, 946, 944, 942, 940, 938, 936, 934, 932, 930, 928, 926, 924, 922, 920, 918, 916, 914, 912, 910, 908, 906, 904, 902, 900, 898, 896, 894, 892, 890, 888, 886, 884, 882, 880, 878, 876, 874, 872, 
870, 868, 866, 864, 862, 860, 858, 856, 854, 852, 850, 848, 846, 844, 842, 840, 838, 836, 834, 832, 830, 828, 826, 824, 822, 820, 818, 816, 814, 812, 810, 808, 806, 804, 802, 800, 798, 796, 794, 792, 790, 788, 786, 784, 782, 780, 778, 776, 774, 772, 770, 768, 766, 764, 762, 760, 758, 756, 754, 752, 750, 748, 746, 744, 742, 740, 738, 736, 734, 732, 730, 728, 726, 724, 722, 720, 718, 716, 714, 712, 710, 708, 706, 704, 702, 700, 698, 696, 694, 692, 690, 688, 686, 684, 682, 680, 678, 676, 674, 672, 670, 668, 666, 664, 662, 660, 658, 656, 654, 652, 650, 648, 646, 644, 642, 640, 638, 636, 634, 632, 630, 628, 626, 624, 622, 620, 618, 616, 614, 612, 610, 608, 606, 604, 602, 600, 598, 596, 594, 592, 590, 588, 586, 584, 582, 580, 578, 576, 574, 572, 570, 568, 566, 564, 562, 560, 558, 556, 554, 552, 550, 548, 546, 544, 542, 540, 538, 536, 534, 532, 530, 528, 526, 524, 522, 520, 518, 516, 514, 512, 510, 508, 506, 504, 502, 500, 498, 496, 494, 492, 490, 488, 486, 484, 482, 480, 478, 476, 474, 472, 470, 468, 466, 464, 462, 460, 458, 456, 454, 452, 450, 448, 446, 444, 442, 440, 438, 436, 434, 432, 430, 428, 426, 424, 422, 420, 418, 416, 414, 412, 410, 408, 406, 404, 402, 400, 398, 396, 394, 392, 390, 388, 386, 384, 382, 380, 378, 376, 374, 372, 370, 368, 366, 364, 362, 360, 358, 356, 354, 352, 350, 348, 346, 344, 342, 340, 338, 336, 334, 332, 330, 328, 326, 324, 322, 320, 318, 316, 314, 312, 310, 308, 306, 304, 302, 300, 298, 296, 294, 292, 290, 288, 286, 284, 282, 280, 278, 276, 274, 272, 270, 268, 266, 264, 262, 260, 258, 256, 254, 252, 250, 248, 246, 244, 242, 240, 238, 236, 234, 232, 230, 228, 226, 224, 222, 220, 218, 216, 214, 212, 210, 208, 206, 204, 202, 200, 198, 196, 194, 192, 190, 188, 186, 184, 182, 180, 178, 176, 174, 172, 170, 168, 166, 164, 162, 160, 158, 156, 154, 152, 150, 148, 146, 144, 142, 140, 138, 136, 134, 132, 130, 128, 126, 124, 122, 120, 118, 116, 114, 112, 110, 108, 106, 104, 102, 100, 98, 96, 94, 92, 90, 88, 86, 84, 82, 80, 78, 76, 74, 72, 70, 68, 66, 
64, 62, 60, 58, 56, 54, 52, 50, 48, 46, 44, 42, 40, 38, 36, 34, 32, 30, 28, 26, 24, 22, 20, 18, 16, 14, 12, 10, 8, 6, 4, 2),
  partition pdef
);


But I do get ORA-14116: partition bound of partition "P1" is too long

So - I still have the problem that I can't put everything into that partition that I would like to add.
Currently, we try to face that situation by adding as much "someUsefulPartitionIndicator's" as we can, and then create a new partition. I am just wondering if there's some mechanism, or planned feature, that will allow to store longer lists of values for that should go into one list-partition. I mean, this situation becomes even worse with the multi-column list partition feature in 12cR2 and if you use varchar columns in here.

Best regards,
Philip
Chris Saxon
September 06, 2017 - 2:41 pm UTC

There's a hard of 4KB limit for partition keys. So you're going to need to rethink your approach. Starting with answering the question of why you're partitioning in the first place!

If partitioning really is useful, you may get somewhere following the method the poster below proposes of partitioning by a virtual column which maps values to a smaller set. e.g.:

create table t (
  x int , 
  vc as (mod(x, 10)) -- replace with your mapping function, collapsing many vals to 1
) partition by list (vc) (
  partition p0 values (0),
  partition p1 values (3),
  partition pdef values (default)
);

Extra work?

Raj J, September 06, 2017 - 12:39 pm UTC

OP,

Based on example provided (assuming it mimics real life), you seem to partition where key values are even numbers between 2 and 1550 (both inclusive). You could create a virtual column that returns if Partition key value is even or odd and partition on that virtual column thus saving a lot of trouble for yourself.

This may seem like a very specific solution but it will likely work for your specific example.
Chris Saxon
September 06, 2017 - 2:43 pm UTC

Yep, that looks like the way to go. Or rethink whether (list) partitions are worth using!

Philip P�tsch, September 06, 2017 - 1:20 pm UTC

This does not mimic real life. In real life, we do have a separate process that determines which keys should be included into one partition. There is no logic within this. The example was just created to show:
a) we add many keys to one partition
b) we don't add adjacent numbers to one partition - they are "randomly" distributed.

Regards,
Philip
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

Design

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