RelationalDBDesign RelationalDBDesign

Oracle Indexes   «Prev 

Database Structures Optimize Disk Performance

There are three other database structures you can use to optimize disk performance:
Hash index:In a hash index, the values for the index are run through a hashing algorithm, and the index stores the result. This type of index isdesigned to avoid clumping when there are many similar index values.
Hash cluster:The hash cluster takes the results of the hash and creates a cluster on disk, so that Oracle can simply run the hash algorithm and thengo directly to the location of the value.
Index organized table:This type of table includes all the table information within an index structure, so that no additional I/O is needed to retrieve thetable data after the index value is found.

The 1) hash and 2) hash cluster indexes cannot be used for sorting, because they do not use a real value, and because there can only be one hash cluster index per table.
You can also partition your indexes, which spreads the disk I/O out over multiple partitions. The Oracle query optimizer will ignore partitions that will not satisfy a selection condition, or will suppress imposing a selection condition if it knows that all the values in a partition will satisfy it. For instance, if you had partitions based on states, and a query requested all entries from “OH”, Oracle would ignore all of the partitions other than the one for Ohio, and not bother to test the values in that partition.

Overview of Hash Clusters

A hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index. With an indexed table or indexed cluster, Oracle Database locates table rows using key values stored in a separate index. To find or store a row in an indexed table or table cluster, the database must perform at least two I/O operations:
  1. One or more I/Os to find or store the key value in the index
  2. Another I/O to read or write the row in the table or table cluster
To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database reads or writes on behalf of the issued statement. Hashing is an optional way of storing table data to improve the performance of data retrieval. Hash clusters may be beneficial when the following conditions are met:
  1. A table is queried much more often than modified.
  2. The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20. For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.
  3. You can reasonably guess the number of hash keys and the size of the data stored with each key value.