Re: Index Question

Thanks, this is what I wanted to know.

Van

Jonathan Lewis wrote in message
<926452389.1249.0.nnrp-02.9e984b29_at_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 - 18:10:06 CDT