Re: Jonathan Lewis Index Question

Thank you very much for the additional information.

Van

<xmark.powell_at_eds.com.x> wrote in message news:9bhed9$u81$1_at_news.netmar.com...
> In article <Xr_B6.116$9d.31179_at_newshog.newsread.com>, Van Messner
> <vmessner_at_bestweb.net> writes:
> >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
> >
> Van, I would look at the explain plans for the joins. If the FK indexes
 are
> used in the plans then I would definitely keep them, but if they are not
 then
> I would look into the delete and update activity against the parents and
> children to see if I need the indexes to support the activity level. If
 the
> activity is low/never then they are probably not needed.
>
> For making a decision on compress I would look to see how many rows per
 key
> exist, that is, if there are 10,000 rows in the parent but only 10
 distinct
> keys I would say that at a 1000 rows per value and at least assuming 2
> columns in the FK then yes compress may be a benefit. But if there are
 5000
> distinct values so that there are only 2 rows per key then compress is not
 a
> benefit. I do not know where you draw the line but I would want at least
 20
> keys per value. Obviously the FK needs to be made up of the compress
 value
> plus one columns.
>
> I have had Janathan's book for several months, but have yet to read it
 from
> cover to cover having read just a section here and there. I did start
 just
> the other day on the full read, and if I read anything that contridicts
 the
> above I will re-post.
>
> -- Mark D. Powell --
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
 eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
 groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Tue Apr 17 2001 - 16:33:51 CDT