Table Partitioning: Two Questions - Forum - OpenEdge RDBMS - Progress Community

Table Partitioning: Two Questions

 Forum

Table Partitioning: Two Questions

This question is not answered

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

Paul Koufalis
White Star Software

pk@wss.com
@oeDBA (https://twitter.com/oeDBA)

ProTop: The #1 Free OpenEdge DB Monitoring Tool
http://protop.wss.com
All Replies
  • RECIDs? Even a lowly programmer knows that it’s ROWIDs that aren’t reusable .
     
  • RECID's are not usable at all. RECID(myPartitionedTable) fails at runtime.

    Paul

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • TP_Application_And_Design.pdf

     gave the attached presentation at EMEA PUG in 2014. Relevant material starting at slide 26.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • 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.

    Dmitri Levin

    Alphabroder

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

    Dmitri Levin

    Alphabroder

  • >

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