 Unindexing foreign key [message #434282] |
Tue, 08 December 2009 06:29  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Hello,
I am designing a large database (several TB of data). Largest table looks as the following:CREATE TABLE a_lot_of_data
(parent_FK1 NUMBER
,type_FK2 NUMBER
,value NUMBER
);
This is a warehousing application. Processing queries would always access data using parent_FK1 only and reading all the matching rows.
In other words: type_FK2 is not going to be used for the data access.
Assuming that the constraints are not that important (I could live with having invalid type_FK2) and we never perform DELETE (not CASCADE DELETE) - can you see any reason why the type_FK2 should not be unindexed?
Tahnks in advance
|
|
|
|
| Re: Unindexing foreign key [message #434286 is a reply to message #434282] |
Tue, 08 December 2009 06:37  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There's a difference between dropping the index and removing the constraint.
As long as you never delete from the table that the constraint points, and don't update the PK on the table that the key points to to then not having an index on the column shouldn't cause any problems.
It's worth keeping the constaint though, as that provides useful information to the optimiser.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
