>>Once you start [if not already] using parameterized views [local and/or remote], you will be mighty grateful that you use meaningless, integer key fields.
>
>Using an Integer also makes incrementing painless on PKs anway

OK. I took the plunge. Performance is a growing issue as some of our tables approach 10s of millions of records. I'm bold so I just backed everything up, converted the keys, tested and moved forward.

The size of the files is way down. Despite my curiosity, I did not measure pre-conversion performance and I have yet to notice improvements but also have few observations under the integer key fields.

There is one downside that is bugging me and making me wished I had considered this more before taking the plunge. I hope to get feedback that helps me through these new challenges.

Here is the big nut: We have several programs that perform SEEKs on multi-key fields. With character key fields that was a cinch. Just concatenate the relevant fields into a variable and SEEK on the variable. I casually overlooked the inability to concatenate integer fields in a meaningful manner. So now our multi-key field SEEKs do not work.

To overcome that hurdle immediately I build indexes using the STR() function. For instance, "INDEX ON STR(Key1) + STR(Key2) TO x" -- though this may be an index in a CDX instead. Then, of course, one has to convert the keys, concatenate the values and store the search value before doing the SEEK. All that seemed pretty ugly and burdensome -- and, perhaps most importantly, seemed to negate the value of using the integer type key fields.

In addition, we do some OUTER JOINs that involve these key fields. With the OUTER JOINs, we use the NVL() function on the key fields but, of course, that won't work with integer type values so it required further work-arounds.

I am guessing that some will advise using SQL more in place of SEEK. There may be some validity to that statement in general. Just guessing, I believe that would be appropriate in some of our cases but definitely not in others.

So... can you offer me any further advice on managing integer key fields (i.e. dealing with problems unique to integer key fields or problems not encountered with character type key fields)? I welcome the opportunity to learn from your experience versus having to learn from my own painful mistakes.

Thanks.