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: iot & reverse-key indexes design insight ?

Re: iot & reverse-key indexes design insight ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 19 Aug 1999 21:01:47 +0100
Message-ID: <935093293.9140.0.nnrp-10.9e984b29@news.demon.co.uk>

The obvious examples for index-only tables are things like time-series, e.g. end of day stock market prices.

Every day you get 10,000 prices, one per stock. The analysts are interested in the last 100 prices for a single stock, or pair of stocks.

Pre-IOT, you would typically create a full table index on the table so that a query for 'last 100 prices of stock X' would find all the data in a handful of blocks rather than having to go to 100 separate locations in the table.

This gave a 100% overhead on data storage. IOTs mean you get the same effect in half the space.

Reverse-key columns - personally I think this are only useful in special cases where you have a meaningless key (possibly connecting parent and child tables).

In a system with a very high insert-rate, with several concurrent processes doing the insertion you would get a lot of contention on the 'trailing edge' of the index - imagine 10 processes inserting rows with keys: 1,000,000 to 1,000,009 - with multiple free lists the rows might all go into different blocks of the table with no contention, but all 10 rows would HAVE to go into the same index leaf with a high cost of 'buffer busy waits'.

By creating a reverse-key index, the 10 different values would become extremely different, and go into 10 different index blocks as well:

e.g.

Value:              Dump Reverse dump
1000000        196,2                2,196
1000001        196,2,1,1,2        2,1,1,2,196
1000002        196,2,1,1,3        3,1,1,2,196
1000003        196,2,1,1,4        4,1,1,2,196

--

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

tedchyn_at_yahoo.com wrote in message <7pekp5$3s1$1_at_nnrp1.deja.com>...
>sir, can anybody share some of insights related to index only table and
>reverse-key indexes related to:
>1. what type of data is best suitable to use iot from performance
> point of view ?
>2. what type of columns will reverse-key index benefit most and what
> type of query?
>
>Thanks in advance Ted chyn(tedchyn_at_yahoo.com)
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Aug 19 1999 - 15:01:47 CDT

Original text of this message

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