Re: Jonathan Lewis Index Question

Hi Jonathan:

    Thanks for the answers. In these databases there are no routine deletes or updates to the primary keys. Database deletes are all soft deletes with periodic cleanouts. Fortunately point c is not an issue with any of these tables.

    I very much enjoyed your book. I understood about half of it and I'm plugging away at the rest.

Van

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:987324138.23657.0.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> 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
> >
> >
>
>
Received on Sun Apr 15 2001 - 13:59:14 CDT