Re: Is it possible to alter an FK to make it indexed?

On Tue, 31 Aug 2004 13:16:12 +0100, Jeremy <newspostings_at_hazelweb.co.uk> wrote:

>Version: Oracle 8i (8.1.7.2) / Solaris 2.6
>
>Unindexed FKs are generally a 'bad thing' (although I recognise this is
>not necessarily always so) - for one thing performance can be affected
>and another is that deadlocks may become more likely.
>
>Is there an alter table statement that can be issued to modify an FK
>constraint to specify that an index should be used? I believe this is
>possible when using the 'ADD constraint' clause of alter table just.

 If you follow the syntax diagram in the manual for constraint specification it does give the 'using index' clause as an option for all constraint types, including foreign keys, however there's then a restrictions section later on the page saying:

"Restrictions on the using_index_clause
<snip>
* You cannot specify this clause for a NOT NULL, foreign key, or check constraint."

 So you just have to create the index separately.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool