Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.3 Additional Data Structures

There are several other data structures available in your Oracle
database that can be useful in some circumstances.

4.3.1 Sequences

One of the big problems that occurs in a multiuser database is the
difficulty of supplying unique numbers for use as keys or
identifiers. For this situation, 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 contention and 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.

4.3.2 Synonyms

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.

Prior to Oracle Database 10g, if you changed the
location referenced by a synonym, you would have to recompile any
PL/SQL procedures that accessed the synonym.

4.3.3 Clusters

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, because 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.

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.

4.3.4 Hash Clusters

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 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 introduces 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.