| PK or no PK [message #128799] |
Wed, 20 July 2005 13:18  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I need a couple of good reasons for why we need to have a PK on every table and how that will reflect the performance and/or Database behaviour as a whole.
As a DBA I'm use to running every month a small script that finds tables with no PKs, disabled PK and other constraint and to send them to DEV for fixing.
Now, the statement is that there's too much overhead to support a PK. So, if this is a audit/log table and may be we are going to pull something from there 2-3 times a year, to keep a PK and created by this Unique index is too much overhead. It'l slow down our inserts into this table and the overall performance on the system.
Please, help me to clear the role of the PK.
Thanks,mj
|
|
|
|
| Re: PK or no PK [message #128801 is a reply to message #128799] |
Wed, 20 July 2005 13:54  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'd agree that there are some situations where not having a pk is ok. Maybe some DSS type operations, or a log table. And I can see disabling/enabling them during large volume data loads and ETL operations. But in general, in an oltp environment, you should have PK's and FK's and whatever other integrity constraints to support your data. You do so with the understanding that the data is more important than any one application that may currently be using it. And that the data may be accessed by many applications at the same time or over a long period of time, and by many ad hoc users, and that the data needs to protect itself or it will be worthless.
It is a case by case situation though, and we'd need specific examples to commen on. But, I'd expect to see some hard numbers / proof / evidence / statistics that maintaining them was "too expensive" and slowed things down "too much". But having a PK is part of having a relational database.
Also, having a pk (and other constraints, not nulls, FKs, etc) gives valuable information to the optimizer in determining its access paths and query plans. Those performance gains may easily outweigh any overhead in maintaining the constraint.
Oh, and do something to prevent people from disabling constraints if it is innapropriate to do so. Running your script to check is excellent, but if you find some, prevent them from happening again.
|
|
|
|
| Re: PK or no PK [message #128802 is a reply to message #128801] |
Wed, 20 July 2005 14:12 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
This is the situation: huge table with many inserts - sometimes about 18,000 a minute. In this case, the guys said that when removed the index/PK they have gained performance on the inserts - and I could understand, but it was dropped and never built back.
The other type is initialization tables for the app. They are red once in the beginning and are small 5 to 500 row - all the time full table scanned. I do not understand why we do not need to have PK on them... How much is the overhead of suporting a constraint - PK + the index on it...
If I keep the PK even without the index - does it make sense?
And what other way I would have to optimize the inserts?
Thanks a lot.
|
|
|
|
| Re: PK or no PK [message #128806 is a reply to message #128802] |
Wed, 20 July 2005 14:33 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>If I keep the PK even without the index - does it make sense?
when you create a Pk an index is always created.
>>The other type is initialization tables for the app. They are red once in the beginning and are small 5 to 500 row
You mean to say, you use persistent connections?
That is a bad idea / logic for the application.
Primary keys are for integrity.
Index is for speeed of querying ( to identify what exactly oracle wants).
Presence of index, will definatly slow down the insert (depends.Like, If there are too many index on table ) but will help with query ( depends on available statistics and skewness of data and if the index is used by CBO. Sometimes a FTS is faster ).
>>what other way I would have to optimize the inserts?
depends how you insert.
a bulk insert with APPEND hint, nologging would be faster.
If there are concurrent inserts, increasing the freelists would
help.
[Updated on: Wed, 20 July 2005 14:37] Report message to a moderator |
|
|
|
| Re: PK or no PK [message #128811 is a reply to message #128799] |
Wed, 20 July 2005 15:15 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
>>If I keep the PK even without the index - does it make sense?
when you create a Pk an index is always created.
I can disable the PK by name and then enable it by name without creating an index though - but does it make sense?
>>a bulk insert with APPEND hint, nologging would be faster.
I had a huge increase by using that but right now there are huge amount of multiple single inserts and cannot touch the app - 3rd party... running multiple sessions like 72 to 96 of the same app.
The major point was that if PKs are for integrity, then we do not need them - the tables are stand alone and may in use - may be not...
So, may be I'm not right... we do not need PKs...
Thanks a lot,mj
|
|
|
|
| Re: PK or no PK [message #128985 is a reply to message #128799] |
Thu, 21 July 2005 07:57 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Don't forget that in maintaining RI in the system, to have a FK in a table, you need a PK on the master table it is referencing ....
And as you have figurd out by now, a PK does make and underlying index.
Sometimes doing a bulk load, it is good idea to disable the PK for speed purposes. This works good for bulk loads manily. Say before the load, disbale PK or drop indexes, load data (bulk) and then enable the PK or create index. You can bundle all these in the same script of task.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
