Indexing

Oracle7 introduced several new indexing options that improve on the traditional B*tree indexes. Oracle7 introduced star queries, histograms, and hash joins. Oracle8 introduced bitmap indexes, index-only tables, and reverse key indexes.

Bitmap indexes are for those columns in larger tables that have just a few possible values such as sex fields, color fields, and so on. A bitmap index is really just a two dimensional array of bits with one axis containing the index values and the other axis containing a bit position for each row in the table. Oracle can quickly find rows in the index by simply searching for nonzero values. Figure 24.6 pictures how the famous EMP table might look in a bitmap index.

Figure 24.6.  EMP bitmap index.

Low-cardinality indexes are easily identified for possible bitmap indexes. The SQL statement in the following code will return a list of existing indexes with just a few distinct values.

select owner "Owner", index_name "Index Name", distinct_keys "Distinct

Keys"

from DBA_INDEXES

where distinct_keys < 15

A star query involves one larger table being joined with several smaller tables, where the larger table has a concatenated index key and each of the smaller tables is referenced by part of that concatenated key. The Oracle cost-based optimizer (cost-based optimizer details later in this chapter) recognizes this arrangement and builds a memory-based temporary table very similar to a cluster of all of the smaller tables.

A histogram might be useful when some column values occur more often than others so that there is not an even distribution of values throughout the object. Prior to histograms, the cost-based optimizer assumed even distribution of values throughout the object. Think of histograms as a series of buckets making up a percentage of the size of an object (see Figure 24.7). Histograms come in two flavors: width-balanced and height-balanced. Width-balance is where the values are divided into an even number of buckets and the optimizer can easily determine which buckets have a higher count. In height-balanced histograms, each bucket has the same number of column values, but a column value may span more than one bucket. Figure 24.7 illustrates what the EMP table might look like in both kinds of histograms.

Figure 24.7.  EMP histogram.

Histograms are implemented via the analyze SQL statement by specifying column parameter. Histograms defaults to 75 buckets; a maximum of 255 buckets can be defined. Histograms can be created for indexes, tables, or clusters.

---

Note:  
Histogram information can be viewed by accessing USER_HISTOGRAMS, ALL_HISTOGRAMS, or DBA_HISTOGRAMS. The number of buckets in a column’s histogram can be viewed by accessing USER_TAB_COLUMNS, ALL_TAB_COLUMNS, or DBA_TAB_COLUMNS.

---

Hash joins greatly enhance the performance of two joined tables, where one table is significantly larger than the other. Hash joins replace the “sort-merge” join algorithm and is only supported by the cost-based optimizer. The hash join works by splitting two tables into partitions. A partition is read into memory and a hash table is created. This hash table is then used to map join columns from the other table, eliminating the sometimes resource-expensive sort-merge. In the hash join method, both tables are scanned only once. In sort-merge, the smaller table can actually be read many times. The sort-merge works by sorting the two result sets being joined over the join columns and then merging the results via the join columns. Hash joins are only supported by the cost-based optimizer and are implemented by the init.ora parameters HASH_JOIN_ENABLED, HASH_MULTIBLOCK_IO_COUNT, and HASH_AREA_SIZE.

The reverse-key index reverses the order of the bytes of the key but keeps the order of the keys in a concatenated key. Reverse-key indexes are really useful in keeping the index balanced with sequence-number type keys. Regular indexes can be made reverse key, and reverse-key indexes can be made regular (see the following syntax examples).

create index <INDEX NAME> on <TABLE NAME> (<COLUMN NAME(S)>) reverse



alter index <INDEX NAME> rebuild noreverse/reverse

Index-only tables keeps the data with the index key in the index. There is no separate table structure. The index will not perform as well if the data takes most of the room in the block. The overflow option is where the nonindexed part of the row will be stored if the row size is over the percentage given. This will create a similar effect to that of row chaining. Index-only tables may not be replicated, they have additional indexes, and partitioning will be supported in Oracle8.1. Listing 24.13 is a syntax example for index-only tables.

---

Tip:  
The smaller tables associated with a star query are an excellent use for index-only tables.

---

Listing 24.13. Index-only table syntax.

create table <TABLE NAME>

    (field descriptions

    .

    .

    <FIELD NAME>

    primary key (<KEY FIELDS>))

organization index tablespace <TABLESPACE NAME>

pctthreshold 20

overflow tablespace <TABLESPACE NAME>

Parsing

When Oracle receives a SQL statement for processing, it parses it, checking it for proper syntax, and develops an execution plan for that particular query. This execution plan can be visualized with the explain-plan feature described in detail later in this chapter. Oracle7 introduced the shared SQL area, where identical (and I do mean identical) SQL statements need not be reparsed but can be stored in memory for possible reuse. The SQL area, combined with the library and dictionary cache, comprises the shared pool, and allows identical, frequently used SQL statements to be parsed only once. Tuning this area is discussed in the “Oracle RDBMS-Level Tuning” section earlier in this chapter.