Very Computer

by **Brian Tkat** » Sun, 31 Dec 1899 09:00:00

Should foreign keys usually be indexed?

If a table has its own unique id and a non-unique foreign key which is
always used to select from the table, will an index automatically
speed it up? Is it automatically indexed being it is a foreign key?

Brian

by **Niall Litchfiel** » Sat, 09 Dec 2000 00:18:43

yes they should be and no I believe they are not automatically.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

Quote:> Should foreign keys usually be indexed?

> If a table has its own unique id and a non-unique foreign key which is
> always used to select from the table, will an index automatically
> speed it up? Is it automatically indexed being it is a foreign key?

> Brian

by **Achille Carett** » Sat, 09 Dec 2000 00:15:30

Creating a foreign key doesn't automatically create an index on the child
table (Columns on parent table are always indexed).

But usually, foreign key columns should be indexed to increase performance.
Because those columns usually appear in WHERE clauses in join queries like
"select * from parent, child where child.id = parent.id;"

Moreover, such indexes speed up cascade deletes.

Achille Carette
Bluegate Group
http://www.bluegate.be

Quote:> Should foreign keys usually be indexed?

> If a table has its own unique id and a non-unique foreign key which is
> always used to select from the table, will an index automatically
> speed it up? Is it automatically indexed being it is a foreign key?

> Brian

by **Mark D Powel** » Sat, 09 Dec 2000 00:27:17

Quote:> Should foreign keys usually be indexed?

> If a table has its own unique id and a non-unique foreign key which is
> always used to select from the table, will an index automatically
> speed it up? Is it automatically indexed being it is a foreign key?

> Brian

No, FK columns in the referencing table are not automatically indexed.

Will an index on the FK column automatically speed access up?
Maybe, it will depend on the table size and the amount of update and
delete activity on the parent table that affects the FK column. If the
referencing table has any size or there is heavy update activity
against the parent then yes the index will definitely help.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy.

by **Brian Tkat** » Sat, 09 Dec 2000 22:44:32

Quote:>For more details, look into the Oracle docs (Server Concepts, Ch. 25,
>"Concurrency Control, Indexes, and Foreign Keys") or under
>http://govt.us.oracle.com/~tkyte/unindex/index.html.

Thanx. I am reading the docs. Unfortunately, very slowly.

Brian

Very Computer

Indexing foriegn keys

Indexing foriegn keys

Indexing foriegn keys

Indexing foriegn keys

Indexing foriegn keys

Indexing foriegn keys