OE 11.7 on AIX 6.1
Q1: Who is using partitioning in prod? If you prefer, please reach out to me in private.
Q2: What are the programming gotchas? I know
1. RECIDs not usable
2. Modifying the partiition-aligned keys could cause a record move/ROWID change
3. Attempting to create a record outside a partition will fail (nowhere to put it)
4. Partition field must be set at record creation
Any others?
Paul
RECID's are not usable at all. RECID(myPartitionedTable) fails at runtime.
Paul
[View:/cfs-file/__key/communityserver-discussions-components-files/18/TP_5F00_Application_5F00_And_5F00_Design.pdf:320:240]
[mention:7dc6ee8d33b54aa696ccb0bdd581e3b1:e9ed411860ed4f2ba0265705b8793d05] gave the attached presentation at EMEA PUG in 2014. Relevant material starting at slide 26.
I am not sure that will help Paul, but the items 2,3 and 4 are true in Oracle.
Working with Oracle partitioning for the last 12 years I think PS-SQL developers do not see them as gotchas but just as a features of partitioned tables that all programmers need to get used to.
> On Dec 11, 2017, at 3:39 PM, Paul Koufalis wrote:
>
> Update from Progress Community
>
> Paul Koufalis
>
> OE 11.7 on AIX 6.1
>
> Q1: Who is using partitioning in prod? If you prefer, please reach out to me in private.
>
> Q2: What are the programming gotchas? I know
>
> 1. RECIDs not usable
> 2. Modifying the partiition-aligned keys could cause a record move/ROWID change
> 3. Attempting to create a record outside a partition will fail (nowhere to put it)
> 4. Partition field must be set at record creation
>
1. Use ROWID instead. ROWID’s contain the partition id. RECID’s do not since they must be convertible to integers.
2. Yes. Changing the partition key of an existing record will cause the record to be deleted from its current partition and created in its new one. And all index entries have to be updated. And transaction log entries created and written. That’s just the nature of things. In well-designed partitioning schemes, this should not be an issue.
2a. You can take advantage of this behaviour to do "online table move”.
3. Yup.
4. All elements of partition key. And NULLs are not allowed.
None of these things should be showstoppers.
>2a. You can take advantage of this behaviour to do "online table move”.
That is sure a nice feature. Though I would like to see how it works with a large table ( 100+ million rows ).
>
> >2a. You can take advantage of this behaviour to do "online table move”.
>
> That is sure a nice feature. Though I would like to see how it works with a large table ( 100+ million rows ).
>
Check out Mike Furgal’s talk on the subject from last year’s (IIRC) PUGs.=