Home » RDBMS Server » Performance Tuning » Unindexing foreign key (Oracle 10.2G)
icon5.gif  Unindexing foreign key [message #434282] Tue, 08 December 2009 06:29 Go to next message
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
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
Inserts would be performed in a bulk - with same parent_FK1, type_FK2 would have ~500 distinct values per insert, total number of inserted rows would be ~2000 to ~20000.
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 Go to previous message
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.
Previous Topic: Horizontal vs Vertical data table design
Next Topic: Need to tune a Query - xjd
Goto Forum:

Current Time: Fri Jul 28 06:38:20 CDT 2017

Total time taken to generate the page: 0.05199 seconds