Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Question

Re: Index Question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 11 May 1999 20:40:16 +0100
Message-ID: <926452389.1249.0.nnrp-02.9e984b29@news.demon.co.uk>


Sounds like your index has degraded over time:

Number(12) would take about 7 bytes to store if you always used 12 digits. Add 6 bytes for the non-unique index rowid column, 6 bytes overhead for 19 bytes per row.

9M * 19 bytes = 171 MB

Assume 75% efficiency for a typical B-tree.

    171 * 4 /3 = 228 Mb

So your index is a bit over the top, and may need a rebuild.

Is it worth having:

    1 Gb scanned at (say) 128K per read request gives 8192 reads

Index scanning for 500 rows in your index

    500 table reads (worst case)
Assume an 8K block size and we need

    (300M * 500 / 9M) / 8K leaf blocks at present which is seems to be ca. 3
add a couple of branch and root.

The index gets you 500 rows in 505-ish read requests

    (10 seconds say)
The tablescan gets you the rows in 8192 read requests

    (2.5 minutes)

Stick with the index.

--

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

Van Messner wrote in message <0KIZ2.1978$LP2.40455_at_news6.ispnews.com>...
>Under version 7.3:
>I have a 1 Gig table with 9 million rows. One column is a number(12).
This
>column is not unique and there are about 500 rows for each different
number.
>The column has a non-unique index which takes about 300M. The table is
>updated frequently.
>
>Is this index giving enough benefit to justify the additional 300 Meg?
It's
>reasonably selective but the column on which it's based is a short number.
>Based on the size of the index it seems like the index values might be
>longer than the column values. Is that a problem?
>
>Thanks
>
>Van
>
>
Received on Tue May 11 1999 - 14:40:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US