Re: Jonathan Lewis Index Question

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 Web ----- 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