Adding a field to a Unique primary index

Posted by ctoman on 02-Mar-2020 13:42

What to look for when changing components for a unique primary index?

Looks like i first need to drop the primary index and then add the new unique primary index. 

Some of the programs are using recid with the existing unique primary index,

OE 11.4.

Thank in advance!

All Replies

Posted by dbeavon on 02-Mar-2020 14:13

>> Looks like i first need to drop the primary index and then add the new primary index.

Yes. We had to do this recently:  documentation.progress.com/.../index.html

"To change the component fields of an index, such as ascending or descending order or uniqueness, you must delete and then recreate the index."

I'm assuming you are asking because the table is large?  How is the point about "recid" relevant to an index change?  Hopefully you aren't permanently storing that, or using it for Pro2SQL , but even if you are, I believe it should survive an index change.  I would test.

My recent experience with reindexing a large table was eye-opening, and reminded me that the OE database must be managed in *two* totally different ways depending on the size of the tables.

Most conventional DDL operations in Progress are single-threaded and limited by the CPU speed of a single core.  So after your tables get to be large (~100 million rows or so) then they become extremely hard to manage in Progress, simply because of the limitations in the database technology.  IE. even if there is plenty of additional I/O and CPU capacity on a server, you will not be able to get the DDL operations to run any faster than it would on a single-core server (if such a thing even existed anymore).

For these very large tables you will need to switch your database management strategies and start looking into the "proutil" commands.  These are less convenient approaches for managing the schema, but Progress has them well-documented in the KB, under keywords like "fastest" way to manage indexes.  See

knowledgebase.progress.com/.../P11656

The main problem with using "proutil" is that you are required to make some compromises (database offline, replication disabled and needs to be re-established afterwards, etc).

Posted by ctoman on 02-Mar-2020 18:22

There are recids tied back with other tables using primary unique index.  Some how the Recids have to be fixed.  I think i will just make the developer work little bit harder.  LOL!

Posted by dbeavon on 02-Mar-2020 20:38

It sounds like you are storing explicit recids in your database?  Yes, that is a problem.

The recid's shouldn't ever be stored in a custom/persistent way.  Many people just use a surrogate SEQUENCE value instead of the recid, and they add an index on that value.   Of course things are a bit slower when you go thru another index to get to the underlying record ... but that's how Progress works.  (In SQL Server you can create a "clustered" index on your own custom IDENTITY column, and that behaves as fast as if you were tying all your tables together by their RECID's).  

Maybe your developer was mishandling the recid in Progress because of some prior experiences they've had with surrogate identifiers in another DBMS.  Just a guess.

PS. I believe I've heard that the Pro2SQL product also uses the RECID or ROWID in a persistent way .  I guess if they are doing it for Pro2SQL, then it becomes that much harder to scold a developer when they are doing a similar thing in their custom programs... Everyone has a reason to excuse themselves from following the normal patterns and practices...

The main problem with persistent RECID/ROWID is that if you ever desire to dump and reload your data, you are going to be in a lot of pain.

This thread is closed