In the Table Partition ESAP webinar yesterday (February 26, 2014) it was mentioned that OpenEdge will prevent the UNKNOWN value "?" from being saved in a partition aligned field. Partition aligned fields are considered the columns that define the partitioning scheme for a specific table. For example, Invoice-date, Order-date or Sales-rep as described in the ESAP workflow examples. This restriction may affect migration of existing data to a partitioned environment and may have implications to field assignment at runtime.
Some mechanisms to help deal with this follow:
Suprizingly, I received no push back on this restriction. However, before enforcing it in the product I'd to hear from you:
I look forward to hearing your comments on this.
No, not simply (or I wouldn't have asked!)
The UNKNONW value sorts high. Since range partitions are considered "<=" this would mean that an UNKNOWN range partition would be inclusive of all values larger than the largest defined partition. While I can see some application for this, I would thnk that what is desired is to have an explicit partition for UNKNOWN range values and not a partition for all values "<=" UNKWNON value which is everything down to the previously defined partition. Also, supporting the UNKNOWN value for range partitions complicates creation of new range partitions since adding a new partition after your largest defined range and before the "UNKNOWN" range partition will require a split operation to complete. Something that would be avoided otherwise.
For list partitions it is straight forward enough but becomes cumbersome when defining sub-partitioins since each sub-partition column would require another definition to include the UNKNOWN value for equality matches causing the number of physical partitions to grow exponentially based on the number of columns involved in the sub-partition definition.
Making sure partition aligned fields don't contain unknown values might be the 'easy' part. Not that I'm saying that it's no work at all, of course.
The bulk of the work will rather be in the rest of the application where these fields are used. Especially when used in calculations or reporting, changes will be required. If a field on a screen now suddenly contains a date whereas it was blank in the past, that will be a change to the user experience. If you still want to show an empty field on the screen but behind the scenes fill it with a date, that would be even more work. And it would have to work the other way around too. If there's a date in a field which is supposed to represent the unkown value, it needs to be blanked out before being put on the screen. The same goes for reports. Columns which had no date, now suddenly will contain data and the interpretation of the report will change because of it.
My 2 cents,
I understand your concerns, Wouter, but I am wondering how likely it is that one is going to want to partition on a field that contains an unknown value. Something like OrderDate, for example, one would not expect to ever be unknown or the record wouldn't exist.
Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice http://www.cintegrity.com
From my experience I think it would be quite likely.
Any business process that might involve a "closure state" -- like an invoice or payment date, for example -- could be a key for archival purposes. If the relative fiscal information is based on that sort of closure data, one might prefer to archive by fiscal year. Those dates would certainly be unknown at record creation and could conceivably be left open for months in exceptional cases.
Not that there wouldn't be ways to work around the issue, but at this early a phase it seems like it would be wise to deal with the issue.
From my experience working on Horizontal partitioning for a number of years in a non-progress environment we do not use UNKNOWN/NULL values on any partitions fields.
In my opinion Partition fields, like primary keys should not contain UNKNOWN/NULL values. If your fields contain NULL/UNKNOWN then those fields are not the correct candidates for either.
I agree. However, in the 4GL, there is a small complication to consider. The 4GL runtime delays CREATES as long as it can but key component validation requires a rowid, which in turn triggers row creation. If you have not set values for all the partition related fields in a single ASSIGN statement, then a row can get created without all the values set and then updated later when the remaining values are known.
This is a well known concern but it is different from dealing with mandatory fields today?