Re: Indexes - Are yours larger then the table?

In article <BwI51p.C44_at_mach1.wlu.ca>, derwin_at_mach2.wlu.ca (Daryl Erwin) writes:
> I have a 40 meg table and the indexes are 30+ megs each! I would just
> like some assurance that this is 'normal'.Can any thing be done
> to lower this size? My table has 10 fields and the index has 2, so the
> index is carrying approx. 1/5th the data the table is but yet the index
> is so large. I chalked this upto a Binary Tree and how each node would
> need to be stored - is this the case? Can I reduce the # of levels? the
> number of elements per node or am I just stuck with what
> 'create index xyz on table abc (myfiled); ' creates?
>
> Any and all comments welcome.
>
> --
> Daryl Erwin Net Address: derwin_at_mach2.WLU.CA
> Computing Services Surface Mail: P4-1, Main Campus
> Wilfrid Laurier University, Waterloo, Ontario
> Bus:(519) 884-1970 x2910 ... Go Hawks!

-- 

One of the first things you can do to improve a databases performance, (after
exhausting all your options improving SQL statements), its to drop and
re-create indexes.  An index will continue growing and block-splitting whenever
you update your tables.  The more often you update a table, the more often you
need to re-create indexes.  I have seen many cases in the past where the index
was LARGER than the table, and selecting one record via an index was slower
than a full table scan.  But how do you know if the size of your index is due
to fragmentation instead of poorly chosen storage parameters ?

The test I use is to run a select on a couple of rows using an index.  Run
TKPROF on the trace files.  How many consistant reads did you require to get
an index ?  If you had to read more than 10% of the number of blocks in your
table, you need to re-arrange your indexes, (this is assuming a large number of
blocks in your table).  Try the same test on a full range scan.  Subtract the number
of blocks for single row and range scan + 1, will be the exact number of leaf
nodes in your index.  THIS is the number that should be about 1/5th of your
data, (assuming your data has not been too fragmented).

You are stuck with the number of nodes and levels Oracle gives you, but that
number happens to be equal to the maximum number it can squeeze into each
database block.  Therefore, you COULD control the number of levels by modifying
block size, (although you can decide this only once per database creation).

================================================================================
Scott Stephens				inet:	sstephen.us.oracle.com
Oracle WorldWide Support                Redwood City, California