Clusters and Other Data Structures for Oracle (Page 1 of 4 )
In this fourth part of a six-part series on Oracle database data structures, you will learn about clusters, constraints, and more. This article is excerpted from chapter four of the book
Oracle Essentials, Fourth Edition Oracle Database 11g, written by Rick Greenwald, Robert Stackowiak, and Jonathan Stern (O'Reilly, 2007; ISBN: 0596514549). Copyright © 2007 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Clusters
A cluster is a data structure that improves retrieval performance. A cluster, like an index, does not affect the logical view of the table.
A cluster is a way of storing related data values together on disk. Oracle reads data a block at a time, so storing related values together reduces the number of I/O operations needed to retrieve related values, since a single data block will contain only related rows.
A cluster is composed of one or more tables. The cluster includes a cluster index, which stores all the values for the corresponding cluster key. Each value in the cluster index points to a data block that contains only rows with the same value for the cluster key.
If a cluster contains multiple tables, the tables should be joined together and the cluster index should contain the values that form the basis of the join. Because the value of the cluster key controls the placement of the rows that relate to the key, changing a value in that key can cause Oracle to change the location of rows associated with that key value.
Clusters may not be appropriate for tables that regularly require full table scans, in which a query requires the Oracle database to iterate through all the rows of the table. Because you access a cluster table through the cluster index, which then points to a data block, full table scans on clustered tables can actually require more I/O operations, lowering overall performance.
Hash Clusters
A hash cluster is like a cluster with one significant difference that makes it even faster. Each request for data in a clustered table involves at least two I/O operations, one for the cluster index and one for the data. A hash cluster stores related data rows together, but groups the rows according to a hash value for the cluster key. The hash value is calculated with a hash function, which means that each retrieval operation starts with a calculation of the hash value and then goes directly to the data block that contains the relevant rows.
By eliminating the need to go to a cluster index, a hash clustered table can be even faster for retrieving data than a clustered table. You can control the number of possible hash values for a hash cluster with the HASHKEYS parameter when you create the cluster.
Because the hash cluster directly points to the location of a row in the table, you must allocate all the space required for all the possible values in a hash cluster when you create the cluster.
Hash clusters work best when there is an even distribution of rows among the various values for the hash key. You may have a situation in which there is already a unique value for the hash key column, such as a unique ID. In such situations, you can assign the value for the hash key as the value for the hash function on the unique value, which eliminates the need to execute the hash function as part of the retrieval process. In addition, you can specify your own hash function as part of the definition of a hash cluster.
Oracle Database 10g introduced sorted hash clusters, where data is not only stored in a cluster based on a hash value, but is also stored in the order in which it was inserted. This data structure improves performance for applications that access data in the order in which it was added to the database.
Next: Extended Logic for Data >>
More Database Articles Articles
More By O'Reilly Media
|
 This article is excerpted from chapter four of the book Oracle Essentials, Fourth Edition Oracle Database 11g, written by Rick Greenwald, Robert Stackowiak, and Jonathan Stern (O'Reilly, 2007; ISBN: 0596514549). Check it out today at your favorite bookstore. Buy this book now.
|
|
