Table Partitioning and the UNKNOWN value

Could you not simply put unknown values into their own partition ?

Julian

On 27 February 2014 18:31, Richard Banville
wrote:
> Table Partitioning and the UNKNOWN value
> Thread created by Richard Banville
>
> 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:
>
> Use a non-UNKNOWN default/initial value for partition aligned fields and
> ensure the value has a partition definition associated with it.
> Make partition aligned fields mandatory to avoid runtime errors during data
> entry.
> To make migration of existing data smoother, ensure existing partition
> aligned field values do not contain the UNKNWON value. If any do, update
> them to a non-UNKNOWN default value so their storage location can be
> identified.
>
> Suprizingly, I received no push back on this restriction. However, before
> enforcing it in the product I'd to hear from you:
>
> Will this restriction deter you in any way from adopting OpenEdge's Table
> Partitioning?
> Do the options for handling this mentioned above meet your needs?
> How much work is it for you (gross idea) to ensure the unknown value is not
> assigned in partition aligned fields?
> Was your expectation to partition data on the UNKNWON value?
> if so, what does that mean in your deployment (how would you use it)?
> Other commnets on managing UNKNOWN values with table partitioning?
>
> I look forward to hearing your comments on this.
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

"The bitterness of poor quality remains long after the sweetness of
low price is forgotten"

Follow dot.r on http://twitter.com/DotRlimited

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.

Hi Richard,

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,

Wouter

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.

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?