Re: Oracle indexes

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1996/02/22
Message-ID: <Pine.SUN.3.91.960222151143.18233D-100000_at_seatimes>#1/1

On Tue, 20 Feb 1996, Bill Cummins wrote:
> Can (does) Oracle 'unbundle' a complex index so that the first two columns
> are used if the third isn't needed?

Oracle will use a composite index as long as the leading field(s) are specified in the query. On your example, the YEAR would have to be known either externally or as a match against another table. If the trailing columns in the index are not specified, then Oracle will still use the index but assumes wild-card values.

Now, if the cost based optimizer is specified and Oracle figures out that only the first column of a composite index is known, then Oracle may choose to do a full table scan depending on the sample size of that column versus the number of rows in the table. I think the magic number is 20%. If Oracle figures it will read at least 20% of the table, then it is much faster to do the serial scan and ignore the index.

For multiple single column indexes, Oracle will read all applicable indexes and get the intersection of the rowids before reading from the table. Thus, multiple indexes (up to 5) can benefit even beyond a single index. This may be better if you can't guarentee that the leading column in a composite index will always be known.

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Thu Feb 22 1996 - 00:00:00 CET