Table Partitioning: Two Questions

Posted by Paul Koufalis on 11-Dec-2017 14:38

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

All Replies

Posted by Peter Judge on 11-Dec-2017 14:42

RECIDs? Even a lowly programmer knows that it’s ROWIDs that aren’t reusable .
 

Posted by Paul Koufalis on 11-Dec-2017 15:05

RECID's are not usable at all. RECID(myPartitionedTable) fails at runtime.

Paul

Posted by Paul Koufalis on 11-Dec-2017 19:14

[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.

Posted by Dmitri Levin on 03-Jan-2018 15:27

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.

Posted by gus bjorklund on 04-Jan-2018 09:40

> 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.

Posted by Dmitri Levin on 05-Jan-2018 15:09

>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 ).

Posted by gus bjorklund on 08-Jan-2018 07:52

>

> >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.=

This thread is closed