📄 ch14.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 14 -- Using Oracle Clusters, Stored Procedures, and Database Links</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="../ch13/ch13.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../rev2/rev2.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 14 -<BR>
Using Oracle Clusters, Stored Procedures, and Database Links</FONT></H1>
</CENTER>
<P>In addition to the regular tables you've seen in previous lessons, Oracle has
an object known as a cluster, which is used to modify the way table data is stored.
In fact, there are two different types of clusters available within the Oracle RDBMS:
the index cluster, sometimes just referred to as a cluster, and the hash cluster.
In this lesson you will learn about both the index cluster and the hash cluster--what
they are, how they work, and how to effectively use them.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Today you will also learn
how to use Oracle <I>procedures</I> and <I>functions</I>. Procedures and functions
are similar; in fact, they are so much alike that they are typically referred to
indiscriminately as <I>stored procedures</I> when they are used in packages and stored
in the database. Procedures and functions are subprograms made up of PL/SQL code
that take a set of parameters given to them by the calling program and perform a
set of actions. Both can modify and return data passed to them as a parameter. The
difference between a procedure and a function is that a function will always return
a single value to the caller, whereas a procedure does not. Usually, procedures are
used unless only a single return value is needed. A procedure or function that has
been stored in the library cache is referred to as a <I>stored procedure</I> or a
<I>stored function</I>; typically both procedures and functions are collectively
referred to as procedures.</P>
<P>Because these procedures and functions are stored in the library cache in an already-parsed
form, the parsing stage of execution can be bypassed. Also, because the SQL statements
are identical each time they are used, they will be taken from the shared SQL area
in the SGA if they have been executed recently. These features of stored procedures,
in conjunction with the fact that network traffic is reduced (because the SQL statements
are not transmitted), greatly enhance performance.</P>
<P>This lesson also deals with <I>database links</I>, which are exactly what you
would think--links from one database to another. Using database links greatly simplifies
accessing these other databases, thus making it easier for users to take advantage
of remote data.
<H2><FONT COLOR="#000077"><B>Clusters</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>cluster</I>, sometimes
called an <I>index cluster</I>, is an optional method of storing tables in an Oracle
database. Within a cluster, multiple related tables are stored together to improve
access time to the related items. Tables that share a common column can be clustered
around this column, thus speeding access to those rows that are accessed based on
that column. The existence of a cluster is transparent to users and to applications;
the cluster affects only how data is stored.</P>
<P>Remember that when Oracle retrieves data from disk, that operation is done on
data blocks, not rows; therefore, if the data is stored together it will be copied
from disk to memory together in the same data block. When the data block is read,
all data from the clustered tables in that data block is read. If you will be using
both pieces of data most of the time, this can be a real advantage; in fact, this
is a major reason why you would create a cluster. So if the data that is clustered
is primarily used in your application with a join operation, a cluster is advantageous.</P>
<P>If you have two tables that have related data and are frequently accessed together,
using clusters can improve performance by preloading the related data into the SGA.
Because you frequently use the data together, having that data already in the SGA
greatly reduces access time.</P>
<P>Clusters are beneficial in joins where the join occurs on the cluster data because
the data is all retrieved in one I/O operation. For example, suppose you are keeping
a database of information for a dog club. (Because I am a dog lover and have several
dogs of my own, I can easily relate to this.) In this database, you want to keep
track of all the dogs and their owners as well as some information about each of
the dogs. To do this, you must create several tables. First, you need a table of
all the dogs who are members of the dog club. You also need a table of the dog owners,
as shown in Figure 14.1. (This is the same table used in previous lessons.)</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 14.1.</B></A></P>
<P><I>The <TT>Dogs</TT> and <TT>Dog_Owners</TT> tables.</I></P>
<P>By combining the two tables into a cluster, you can save time when retrieving
the data (because the dog owner information for a particular dog is essentially read
into the SGA when the information for that dog is read in). The common column(s)
of the cluster is called the <I>cluster key</I>, and must be indexed.</P>
<P>Figure 14.2 shows what the tables look like as a cluster. Note that the cluster
key is the owner identification number.</P>
<P>If the data from the two tables is frequently used together, this cluster arrangement
is a performance win. It provides the ease of use of individual tables but the additional
performance of a cluster.</P>
<P>If you do not typically use their information together, there is no performance
benefit of putting tables into a cluster. There is even a slight disadvantage because
more SGA space is taken up by the additional data when the cluster is read in.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 14.2.</B></A></P>
<P><I>The cluster including the <TT>Dogs</TT> and <TT>Dog_Owners</TT> tables.</I></P>
<P>An additional disadvantage of clusters is a reduction of the performance of <TT>INSERT</TT>
statements. This performance loss is caused by the fact that the data must be inserted
based on the cluster key and the fact that there are multiple tables in the same
block. The clustered table also spans more blocks than the individual tables, thus
causing more data to be scanned.</P>
<P>In summary, a cluster can be useful for tables where data is primarily accessed
together in a join. The reduced I/O needed to bring the additional data into the
SGA and the fact that the data is already cached can be a big advantage.</P>
<P>If the tables have a large number of <TT>INSERT</TT> statements or if the data
is not frequently accessed together, a cluster is not useful and should not be used.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Do not cluster tables if full-table
scans are often performed on only one of the tables in the cluster. The additional
space required by the cluster and the additional I/O will reduce performance.
<HR>
</BLOCKQUOTE>
<H3><FONT COLOR="#000077"><B>Hash Clusters</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>hash cluster</I> is
similar to a cluster but uses a hash function rather than an index to reference the
cluster key. A <I>hash function</I> is a numeric function that determines the data
block in the cluster based on the value of the cluster key. A hash cluster stores
the data based on the result of a hash function. Figure 14.3 shows a hash cluster.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 14.3.</B></A></P>
<P><I>A hash cluster.</I></P>
<P>To find the data block in an index cluster, there must first be one or more I/Os
to the cluster index to find the correct data block. In a hash cluster, the cluster
key itself tells Oracle where the data block is, an arrangement that can reduce to
one the number of I/Os needed to retrieve the row.</P>
<P>In contrast to the index cluster, which stores related data together based on
the row's cluster-key value, the hash cluster stores related rows together based
on their hash values.</P>
<P>The number of hash values is determined by the value of the <TT>HASHKEYS</TT>
parameter of the <TT>CREATE CLUSTER</TT> command. The number and size of the cluster
keys are very important and should be carefully calculated.</P>
<P>Do not use hash clusters on tables where table scans are often performed on only
one of the tables in the cluster. The additional space required by the cluster and
the additional I/O required can reduce performance.</P>
<P>Also, do not use a hash cluster on a table where the application frequently modifies
the cluster key or when the table is constantly being modified. Because the cluster
key is based on a calculation, significant overhead is involved in constantly recalculating
the key.
<H4><FONT COLOR="#000077"><B>When to Hash</B></FONT></H4>
<P>Although hash clusters can be used in a similar fashion to index clusters, you
do not have to cluster multiple tables. In fact, it is frequently useful to create
a single table as a hash cluster to take advantage of the hashing feature. By using
hashing, you might be able to retrieve your data with only one I/O rather than the
multiple I/Os required to retrieve data using a B*-tree index.</P>
<P>Because hashing uses the value of the data to calculate the data block in which
the desired data is located, hashing is best used on tables that have unique values
for the cluster key and where the majority of queries are equality queries on the
cluster key. For equality queries, the data is usually retrieved in one read operation;
the cluster key does not have to be a single column. If the typical query uses an
equality on a set of columns, use these columns to create a composite key. A composite
key is one that is made up of more than one column.</P>
<P>Hashing is also most optimal when the table or tables are fairly static in size.
If the table stays within its initial storage allocation, hashing usually does not
cause a performance degradation. If the table grows out of its initial allocation,
however, performance can degrade because overflow blocks are required.</P>
<P>Hashing might degrade the performance of table scans because the hashing process
reads blocks that may not have much data in them. Because the table is originally
created by laying out the data into the cluster based on the value of the cluster
key, some blocks might have only a few rows.</P>
<P>Hashing can also degrade performance when the value of the cluster key changes.
Because the location of the block in which the data resides is based on the cluster
key's value, a change in that value can cause the row to migrate in order to maintain
the cluster.</P>
<P>A good candidate for hashing has the following properties:
<UL>
<LI>The cluster-key value is unique.
<P>
<LI>The majority of queries are equality queries on the cluster key.
<P>
<LI>The size of the table is static; very little growth occurs.
<P>
<LI>The value of the cluster key does not change.
</UL>
<P>An example of a good hashing candidate is a table used for storing parts information.
By using a hash cluster keyed on the part number, access can be extremely efficient
and fast. Any time you have a somewhat static table with a unique column value or
set of column values, consider creating a hash cluster.</P>
<P>Just as with index clusters, there are both advantages and disadvantages in using
hash clusters. Hash clusters are efficient in retrieving data based on equality queries
on the cluster key. If you are not retrieving data based on that key, the query is
not hashed. As with the index cluster, you see a performance decrease when executing
<TT>INSERT</TT> statements in a hashed table.</P>
<P>With both index clusters and hash clusters, make a careful determination about
whether a cluster can help performance based on the access patterns on the tables.
As with many aspects of RDBMS, tuning based on a wrong decision can end up costing
in performance.</P>
<P>If you can take advantage of hashing by meeting somewhat strict criteria, you
can see very good performance. Hashing is extremely efficient if you can meet the
criteria described in this section.
<H3><FONT COLOR="#000077"><B>Managing Clusters</B></FONT></H3>
<P>Once you have decided whether to use an index cluster or a hash cluster, you can
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -