Home » SQL & PL/SQL » SQL & PL/SQL » DO We Require Index on Foreign Keys
DO We Require Index on Foreign Keys [message #164705] Fri, 24 March 2006 17:02 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi

Do we need to have indexes on each and every column having
foreign key reference, because i have seen at new place almost all the Foreign Key have indexes.
Do it helps in performance or it is waste.


Thanks
Re: DO We Require Index on Foreign Keys [message #164713 is a reply to message #164705] Fri, 24 March 2006 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>Do it helps in performance or it is waste.
YES it helps performance and YES it "wastes" disk space.
Re: DO We Require Index on Foreign Keys [message #164733 is a reply to message #164705] Sat, 25 March 2006 02:53 Go to previous messageGo to next message
pallavigs
Messages: 37
Registered: March 2006
Location: India
Member
Hi,

If you have index for each and every column with foreign key reference ,it will hit the performance in run time.
Having,more indexes would make the performance slower.

Pal.
Re: DO We Require Index on Foreign Keys [message #164756 is a reply to message #164705] Sat, 25 March 2006 12:27 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
It might or might not help performance, depending on
1. the queries frequently issued
2. how often you delete the parent rows.

Say EMPLOYEES.DEPARTMENT_ID is a FK column referencing DEPARTMENTS, and it is indexed. If you frequently query employees where department_id = n, the index is probably useful. Also, if you are likely to delete rows from DEPARTMENTS, the index is useful, because without it the database must full-scan EMPLOYEES to find dependent rows.
Re: DO We Require Index on Foreign Keys [message #164757 is a reply to message #164705] Sat, 25 March 2006 12:59 Go to previous messageGo to next message
sandeepwaghmare
Messages: 8
Registered: March 2006
Junior Member
Hey - maybe i'm missing out on something, but ain't foreign keys ar e supposed to be based on PK's of the table?? Shocked
Re: DO We Require Index on Foreign Keys [message #164760 is a reply to message #164705] Sat, 25 March 2006 13:02 Go to previous messageGo to next message
sandeepwaghmare
Messages: 8
Registered: March 2006
Junior Member
oh oh...i got the que wrong..

Faiz - do you mean indexes on the child columns or the parent colums?Confused
Re: DO We Require Index on Foreign Keys [message #164765 is a reply to message #164760] Sat, 25 March 2006 13:45 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Maybe it's ambiguous, but he did say "i have seen at new place almost all the Foreign Key have indexes" so I assumed he was referring to columns of dependant tables that implement a foreign key to some parent table, for example EMP.DEPTNO.

[Updated on: Sat, 25 March 2006 13:46]

Report message to a moderator

Re: DO We Require Index on Foreign Keys [message #164909 is a reply to message #164765] Mon, 27 March 2006 09:10 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi All

Yes i mean indexes on all child columns , as i said at new place i saw almost all the foreign key columns have indexes.
Well i don't know about performance till now, but what i am looking is logically do we need indexes or not on foreign key columns.



Thanks
Re: DO We Require Index on Foreign Keys [message #164910 is a reply to message #164909] Mon, 27 March 2006 09:16 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
There is no hard requirement for indexes on FK columns. It is purely a matter of performance.

I also seem to remember a locking issue in which the absence of an index would cause the database to lock more rows than necessary in the case where you delete the parent row of an ON DELETE CASCADE foreign key, however I couldn't reproduce this in a quick test.
Re: DO We Require Index on Foreign Keys [message #164911 is a reply to message #164910] Mon, 27 March 2006 09:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
@William,

I think that issue was (said to be) resolved somewhere in the 9i releases.
If I remember well, it was that the entire child-table was locked whenever you deleted a record from the parent-table. (regardless the delete-rule on the FK).
Re: DO We Require Index on Foreign Keys [message #164914 is a reply to message #164911] Mon, 27 March 2006 09:41 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi all


So can i conclude this thing regarding index on FK that if i am using those columns in Queries, or DML then i will go for index or else no,
Another thing related to this, Do we need to create a Unique index seperately on Primary Key or no need.
like this or
CREATE UNIQUE INDEX name ON table_name(pk_clolumn) ;     


or else
create index index_name on table_name (pk_column); 
thanks
Re: DO We Require Index on Foreign Keys [message #164917 is a reply to message #164914] Mon, 27 March 2006 10:12 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
A primary key constraint cannot exist without an index. However, it can make use of an existing index, which does not have to be unique. Using an existing nonunique index can be useful as you can disable/drop the primary key and then enable/recreate it without an index rebuild.

If there is no suitable index at the time when you define a primary key, a unique index of the same name will be created automatically.

[Updated on: Mon, 27 March 2006 10:14]

Report message to a moderator

Re: DO We Require Index on Foreign Keys [message #164950 is a reply to message #164705] Mon, 27 March 2006 15:17 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Found this on asktom regarding locking of child table when there is no FK index:

Quote:


The child table lock is taken for the duration of the parent update/delete NOT
the entire transaction in 9i. Lessens the problem but does not remove it.


Re: DO We Require Index on Foreign Keys [message #164953 is a reply to message #164705] Mon, 27 March 2006 15:27 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And just created this, which I believe shows it:

create table parent
(
	pid number not null,
	value varchar2(30) not null,
	constraint parent_pk primary key(pid)
);

create table child
(
	cid number not null,
	pid number not null,
	child_value varchar2(30) not null,
	constraint child_pk primary key(cid),
	constraint child_fk foreign key(pid) references parent(pid) on delete cascade
);


insert into parent values (1, 'p1');
insert into parent values (2, 'p2');

insert into child values (1, 1, 'c11');
insert into child values (2, 1, 'c21');
insert into child values (3, 2, 'c32');

commit;

create index child_index on child(pid);

prompt Go to another session and insert a random child record but don't commit.
prompt Then come back here, hit return, and notice the delete being blocked.
prompt Next, go back to other session and commit.
prompt Finally, come back here, and notice the block released, deleting the row.
prompt Rerun this whole script again, this time uncomment the create fk index above.
pause

delete from parent where pid = 2;

commit;

prompt Now go back to the other session and commit, then come back here to close.
pause

drop table child;
drop table parent;

Re: DO We Require Index on Foreign Keys [message #165151 is a reply to message #164705] Tue, 28 March 2006 08:57 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
One big reason to index child records is if you have cascade delete on the foreign key. That way when you delete the parent, you won't get the big delay removing the children. Having multiple indexes will slow up (a little) inserts and updates. It will NOT slowdown selects.
Re: DO We Require Index on Foreign Keys [message #203279 is a reply to message #165151] Tue, 14 November 2006 10:12 Go to previous messageGo to next message
kitoneto
Messages: 1
Registered: November 2006
Junior Member

He would like to receive article or other document on, multiple indexes or behavior of multiple indexes in the tree of binary research.

I´m wating.
Re: DO We Require Index on Foreign Keys [message #203304 is a reply to message #203279] Tue, 14 November 2006 12:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
He who? "I'm waiting" sounds a little rude, as if you are commanding us to do something.
Re: DO We Require Index on Foreign Keys [message #203429 is a reply to message #164911] Wed, 15 November 2006 02:15 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Even in 9i, if you delete from the parent table, Oracle needs to check that there are no child rows, and if the child table is big and has no FK index, the resulting FTS can be a right pain in the users (experience speaking here) until you catch it.
Previous Topic: Converting Date of Birth to Age
Next Topic: Transaction Management in Stored Procedures....
Goto Forum:
  


Current Time: Tue Dec 06 08:42:28 CST 2016

Total time taken to generate the page: 0.05594 seconds