The Progress compiler selects the index that fits best for every query. The primary index only has precedence when all other choices are equal good or bad.
Please check: http://peg.com/techpapers/monographs/query/query.html- may look a bit outdated, but most of it is still true (even with dynamic queries available).
Mike
Progress allows creation of a primary key which is not unique, which can be useful if the data in question isn't unique in nature. It's also possible to specify key entries which have a missing value (ie are ?), and to have multiple "duplicate" records with matching ? values.
So, while a "theoretical" table design would require a unique, non-null primary key, Progress provides the flexibility to "break the rules" when appropriate.
And, just because you can do it, doesn't mean you should. A lot of older ABL programs and data structures used "meaningful" primary keys, e.g., a customer number or a name. Modern design points us in the direction of using "meaningless" primary keys such as a sequence or a GUID.
And, just because you can do it, doesn't mean you
should.
I agree.
But if the option is needed, it's still there.
Just to be argumentative, since it is Monday morning and the PSDN forums haven't been very lively lately, can you cite a case in which it is actually desirable to have the primary key be non-unique?
Where such a key would blow the limit on index key sizes.
And how could that ever be the case using a GUID?
GUID? That only works on more recent OE versions...
As a builtin function, perhaps, but one can certainly program some version of UUID in any version of ABL. For that matter, a simple sequence number will handle most tables and two sequences will handle anything else.
While it is possible to create a unique primary key, the actual implementation could be problematic with older systems. Some old legacy code I've run into pre-date sequences, and used a central "counter" for a sequence instead. This worked fine, except that the counter would get locked into the TX with the calling process, and couldn't be locked by another process until the original processes's TX cleared it's locks.
As time moved forward and the code was used for a larger user base, this can be a significant problem.
I didn't say that it couldn't be done poorly ...
Siebel handled that problem with a sort of "tear off" pad system. Each session was issued something like 10 new unique keys to used in creating new records of whatever type. The session used those until they were used up and then got another batch. Any that were unused were simply never used, but if one isn't trying to inappropriately track sequence, that isn't an issue.
Let me rephrase ... can you come up with any reason why one should not use a unique primary key when working with any new system or table and any reasonably modern version of ABL?
I didn't say that it couldn't be done poorly ...
Siebel handled that problem with a sort of "tear off"
pad system. Each session was issued something like
10 new unique keys to used in creating new records of
whatever type. The session used those until they
were used up and then got another batch. Any that
were unused were simply never used, but if one isn't
trying to inappropriately track sequence, that isn't
an issue.
That would presume a level of insight not always present in some shops....
Let me rephrase ... can you come up with any reason
why one should not use a unique primary key when
working with any new system or table and any
reasonably modern version of ABL?
I'll have to think about that. I was thinking something like a log file where a unique PK isn't really necessary, although there's typically there's some way to make each record unique.
1) How'd you do that 'jive quote header'?
2) Yes, a GUID can be used to do that. The question remains are there situations where putting a GUID on a record is contra-indicated.
2) The only situation I can think of is if there a also other indexes (in your log file sample possibly a DATETIME and an author field) and the order of records counts during dump and load. That would probably the only situation where you are not able to influence the sort criteria using a BY clause.
1) Im my IE7 there is a "Quote" button beside the smiley button.
2) The only situation I can think of is if there a
also other indexes (in your log file sample possibly
a DATETIME and an author field) and the order of
records counts during dump and load. That would
probably the only situation where you are not able to
influence the sort criteria using a BY
clause.
I can see cases where one would want a non-unique index - such as during a D&L a unique PK is slower than a non-unique key ordered dump.
1) Im my IE7 there is a
"Quote" button beside the smiley button.
I'm using firefox here, and I don't see a smiley or the "quote" button here. Is there a setting that needs to be turned on somewhere?
Getting real off topic, but...
timk519 wrote:
I'm using firefox here, and I don't see a smiley or the "quote" button here. Is there a setting that needs to be turned on somewhere?
Must be Firefox. I just tried Firefox (on a Linux VM) and don't get the Smiley and the quote button either.
Mike
Must be Firefox. I just tried
Firefox (on a Linux VM) and don't get the Smiley and
the quote button either.
Mike
Maybe it's missing it's primary key somewhere.
That would presume a level of insight not always present in some shops....
Again ... just because it can be done poorly doesn't mean that we should advocate it in a discussion of best practices.
I'll have to think about that.
You do that!
Yes, there are times when one wants a non-unique index ... doesn't mean it should be the primary one.
Yes, there are times when there might be reasons to dump and load on something other than the primary index.
None of which impacts the question of whether the primary index should be non-unique.
Ok, how about if this is turned this around - what's the case for always having a unique PK? To do updates and edits? If the data's archival, and not going to be edited, then what's the point of a unique PK?
So you always know which unique record it is and can find it without resorting to kludges like recid.
Just because something is archival is no reason to design in sloppiness.
Thanks ALL
If I recall correctly George Potemkin has argued that primary indexes should always be non-unique.
Aside from it's role as a tie-breaker the other significant use of primary indexes is that they determine dump & load order.