Re: Table Locking

The script is out of date.

There were a few versions of Oracle
where the index columns had to be in
the same order as the constraint columns, but I think that was only briefly in the 7.2 or 7.3 era.

Bear in mind that you only need indexes if you update the primary key, or delete rows from the primary key table - so this script may supply give lots of redundant directives.

Finally, the index only has to start with the columns of the constraint in order to support the constraint.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





"Joseph Dimech" <joe_at_tsc-corp.com> wrote in message 
news:trkud.2349$Zn6.1715_at_trnddc08...


> Thanks Mladen. I have found a script which I used to help me look for what

> you suggested about problem foreign keys. I am posting it below in case

> somone else may be interested or has the same issues.

>

> JCD

>

> EM NAME:   TFSFKCHLK.SQL

> REM USAGE:"@path/tfsfkchk"

> REM ------------------------------------------------------------------------

> --

> REM REQUIREMENTS:

> REM    None -- checks only the USER_ views

> REM ------------------------------------------------------------------------

> --

> REM    This file checks the current users Foreign Keys to make sure of the

> REM    following:

> REM

> REM    1) All the FK columns are have indexes to prevent a possible 

> locking

> REM       problem that can slow down the database.

> REM

> REM    2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking

> REM       problem the columns MUST be index in the same order as the FK is

> REM       defined.

> REM

> REM    3) If the script finds and miss match the script reports the 

> correct

> REM       order of columns that need to be added to prevent the locking

> REM       problem.

> REM

> REM

> REM

> REM ------------------------------------------------------------------------

> -

> REM Main text of script follows:

>