large column primary key

[rnv]

My primary keys are varchar2(60)
These are system generated using an algorithm .We choose to use this over sequences etc.
Any ideas on how to improve performance, indexes, storage etc
thanks

[chrisrlong]

Yes, don't use them!

Trust me, you are going to kill the performance of your database. You should always strive to make your keys numeric. You have gone to the opposite end of the spectrum by making them huge strings. You are *not* going to get good (relative) performance out of that scenario. Period.

I'm sorry, but I'm trying to be honest with you. You really need to have your people re-visit that decision. I would really question the need for such extremes first. If they absolutely think they need to encrypt the ID's, then you need to convince them that they must stay numeric anyway.

The drawbacks of using large, character values as keys are many:

- Increased base table size
- Increased PK Index size
- Increased child table size
- Increased child table FK index sizes
- Slower Indexed lookups.
etc.

... and all these drawbacks are multiplied by size and repetition. ie. *Every* table will have a larger PK field and every FK field in it will be larger. You could easily be quadrupling (or worse) the size of your entire database. *Every* index will be larger and slower. You will *always* be moving more data between your database and the application. *Every* resultset will be larger. *Every* maintenance procedure will take longer. Since your tables are larger, the same amount of data will take up a larger percentage of the SGA. Therefore, you will actually be able to hold less data in the SGA. And so on and so on...

Sorry to give you the bad news.

Okay, now if, after all that, you still go ahead with this...
Buy a very, very big machine. :) And hope that someone has some better pointers for you than I did.


- Chris

[tamilselvan]

Is it on single column or on multiple columns?

[rnv]

single column