Databases Reference
In-Depth Information
For instance, if there were 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 cur‐
rent 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 10
g
, if you changed the location referenced by a synonym, you
would have to recompile any PL/SQL procedures that accessed the synonym.
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.
