There are several other data structures available in your Oracle database that are useful in some circumstances.
One of the big problems that occurs in a multiuser database is the difficulty of supplying unique numbers for use as keys or identifiers.
In such a case, Oracle allows you to create an object called a sequence . The sequence object is fairly simple. Whenever anyone requests a value from it, it returns a value and increments its internal value, avoiding time-consuming interaction with the requesting application. Oracle can cache a range of numbers for the sequence so that access to the next number doesn’t have to involve disk I/O; rather, it can be satisfied from the range in the SGA.
Sequence numbers are defined with a name, an incremental value, and some additional information about the sequence. Sequences exist independently of any particular table, so more than one table can use the same sequence number.
Consider what might happen if you didn’t use Oracle sequences. You might store the last sequence number used in a column in a table. A user who wanted to get the next sequence number would read the last number, increment it by a fixed value, and write the new value back to the column. But if many users tried to get a sequence number at the same time, they might all read the “last” sequence number before the new “last” sequence number had been written back. You could lock the row in the table with the column containing the sequence number, but this would cause delays as other users waited on locks. What’s the solution? Create a sequence.
All data structures within an Oracle database are stored within a specific schema . A schema is associated with a particular username, and all objects are referenced with the name of the schema followed by the name of the object.
For instance, if there is a table named EMP in a schema named DEMO, the table would be referenced with the complete name of DEMO.EMP. If you don’t supply a specific schema name, Oracle assumes that the structure is in the schema for your current username.
Schemas are a nice feature because object names have to be unique only within their own schemas, but the qualified names for objects can get confusing, especially for end users. To make names simpler and more readable, you can create a synonym for any table, view, snapshot, or sequence, or for any PL/SQL procedure, function, or package.
Synonyms can be either public , which means that all users of a database can use them, or private , which means that only the user whose schema contains the synonym can use it. For example, if the user DEMO creates a public synonym called EMP for the table EMP in his schema, all other users can simply use EMP to refer to the EMP table in DEMO’s schema. Suppose that DEMO didn’t create a public synonym and a user called SCOTT wanted to use the name EMP to refer to the EMP table in DEMO’s schema. The user SCOTT would create a private synonym in his schema. Of course, SCOTT must have access to DEMO’s EMP table for this to work.
Synonyms simplify user access to a data structure. You can also use synonyms to hide the location of a particular data structure, making the data more transportable and increasing the security of the associated table by hiding the name of the schema owner.
A cluster is a data structure that improves retrieval performance. A cluster, like an index, doesn’t 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 comprised 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.
A cluster can reduce the performance of INSERTs, so you shouldn’t use a value for a cluster column that changes often. 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 can actually require more I/O operations, lowering overall performance.
A hash cluster is like a cluster with one significant difference that can make it even faster: the values for the cluster key are stored in the cluster index. 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, 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.
Get Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
