Re: Jonathan Lewis Index Question

I have two general rules - examine each case on its own merits, and declare the FK columns not null.

Looking a little more closely at your example,: Since you join the child table to the parent table by pk/fk based on a single pk fairly frequently, there is a reasonable chance that the indexes should exist, and this
would be my starting point in the absence of any other information.

Other factors though -
Do you delete or update the PK with any
degree of regularity, and are the circumstances in which you do this such that you would get massive contention on the child table lock that would occur in the absence of the

If the PK/FK reviews says you can drop an index then try a simple test -

    select * from child where {fk columns} = {constants}

If this doesn't cause an indexed access for the 'best value' of constants, then the index is probably a waste of space. Watch out for the usual CBO side-effects, though skewed data and the benefit of histograms. Also there may be queries which are designed to go faster because they use one of the 'index only' access methods.

>b) using a compress = 1 clause on the indexes

Definitely look at compressing the index - if there are only one or two child rows per parent, and the PK is very short, this may not reduce the size of the index, but it is almost inevitable that compression will be effective on FK indexes. Remember that you can have multiple column keys/indexes, so you can apply compression to more than just one column.

>c) something else

Remember that the index does not have to be an exact match for the FK - it need only start with the same columns as the FK. (At one stage these had to be in the order of the PK declaration - but this has changed over time, and the last time I checked (8.1.7) is wasn't a requirement).

It is a surprisingly common error for people (or possible their code generators to produce) to generate the following pair of indexes on a child table:

    child_PK(col1, col2, col3)
    child_FK_parent(col1,col2)

Just occasionally (in the past) there may have been a very specific performance
benefit that the CBO could spot in using the second index for an index_only query, but in general (and almost inevitably in 8.1) you need only:

    child_pk( col1, col2, col3) compress 2

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Van Messner wrote in message ...


>Or anyone else who knows indexes to the same level.  I have tables joined

 to


>others by foreign keys.  The foreign keys are used fairly often for query

>joins between the tables and usually refer to a single sequence-based

>primary key.  The foreign keys have B-tree indexes.

>Given that the keys are used for queries, as a general rule do you suggest

>a)    dropping the indexes

>b)    using a compress = 1 clause on the indexes

>c)    something else

>

>Thanks,

>

>Van

>

>