📄 ch13.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 13 -- Using Indexes and Sequences</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="../ch12/ch12.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch14/ch14.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 13 -<BR>
Using Indexes and Sequences</FONT></H1>
</CENTER>
<P>An index is an optional structure designed to help you gain faster access to data.
Just like the index in this book, an Oracle index is logically and physically independent
of the data in the associated table or cluster. You can use the index to speed access
to the data or you can retrieve the data independently from the index by searching
the tables for it. When optimally configured and used, indexes can significantly
reduce I/O to the datafiles and greatly improve performance.</P>
<P>The presence of an index is transparent to the user or application and requires
no application changes. However, if you are aware of an index, you should be able
to design your applications to take better advantage of those indexes. The only indication
of an index might be an improved access time to data.</P>
<P>The index itself should be created with some knowledge of the application and
data-access patterns. If indexes are created on columns that are not used to access
the data, the index is useless.</P>
<P>After an index has been created for a table, Oracle automatically maintains that
index. Insertions, updates, and deletions of rows in the table automatically update
the related indexes.</P>
<P>A table can have any number of indexes, but the more indexes there are, the more
overhead is incurred during table updates, insertions, and deletions. This overhead
is incurred because all associated indexes must be updated whenever table data is
altered.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Indexes can be created with the Parallel
Index Creation feature of the Parallel Query option. Using this feature greatly reduces
index-creation time. Because all data must be read to create the index, a table scan
is forced. This table scan is parallelized, greatly improving performance.
<HR>
</BLOCKQUOTE>
<P>It is often necessary to create a sequence of numbers to be used as an identifier
in your application. This number might be an account number, order number, or some
other sort of ID number. Rather than generating these numbers manually, Oracle has
a facility to generate these sequences of numbers automatically. To create a unique
sequence of numbers on your own, you would have to lock the record that has the last
value of the sequence, generate a new value, and then unlock the record. To avoid
locking these records, Oracle provides a sequence generator that performs this service
for you.</P>
<P>The Oracle sequence generator can generate sequential numbers of up to 38 digits,
without having to manually lock records. When you define a sequence, you can specify
the original values of the sequence, whether the sequence should be cached, and whether
the sequence should be in ascending or descending order. Later today you will learn
how to use the Oracle sequence generator to automatically generate sequences of numbers
for you.
<H3><FONT COLOR="#000077"><B>Index Types</B></FONT></H3>
<P>There are several different types of indexes. An index can be limited to one column
value or can consist of several columns. An index can be either unique or nonunique.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>unique index</I> is
an index value that has the additional constraint that the set of indexed columns
defines a unique row. Although this constraint might be specified, it is usually
better to associate this constraint with the table itself rather than with the index.
Oracle enforces <TT>UNIQUE</TT> integrity constraints by automatically defining a
unique index on the unique key.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>nonunique index</I>
does not impose the constraint that the index value be unique. Such an index can
be quite useful when quick access is desired on a nonunique value.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Another type of index is
a <I>composite index</I>, which indexes several columns in a table. These column
values can be in any order and the columns do not have to be adjacent in the table.</P>
<P>A composite index is useful when <TT>SELECT</TT> statements have <TT>WHERE</TT>
clauses that reference several values in the table. Because the index is accessed
based on the order of the columns used in the definition, it is wise to base this
order on the frequency of use. The most-referenced column should be defined first,
and so on.</P>
<P>The index should be created based on the values accessed in the application; the
application should be developed to take advantage of these indexes. Having knowledge
of and influence over these indexes can be very useful to the application developer.
<H3><FONT COLOR="#000077"><B>How the Oracle Index Works</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>When an index is created,
an index segment is automatically allocated. This index segment contains information
that speeds access to data by determining the location of indexed data with as few
I/Os as possible. Oracle indexes data by using an index structure known as a <I>B*-tree
index</I>. A B<SUP>*</SUP>-tree index is designed to balance the access time to any
row. A B<SUP>*</SUP>-tree index is a tree of descending comparison values, as shown
in Figure 13.1. As you traverse down the index, you compare the desired value with
the values in the upper-level index blocks, called <I>branch blocks</I>. Based on
the outcome of the comparison with the branch blocks, you compare the desired value
with more branch blocks until you reach the lowest-level index blocks. The index
blocks on the lowest level, called <I>leaf blocks</I>, contain every indexed data
value and the associated <TT>ROWID</TT> of that data.</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 13.1.</B></A></P>
<P><I>The B<SUP>*</SUP>-tree index structure.</I></P>
<P>With a unique index, there is one <TT>ROWID</TT> per data value in the leaf block,
as shown in Figure 13.2. With a nonunique index, there might be several values associated
with the data value. In the case of the nonunique index, the data values are sorted
first by the index key and then by the <TT>ROWID</TT>.</P>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 13.2.</B></A></P>
<P><I>The index block structure.</I></P>
<P>With a B<SUP>*</SUP>-tree index, all the leaf blocks are at the same level. Access
of index data takes approximately the same time regardless of the value of the data.
B<SUP>*</SUP>-tree indexes provide quick access to data whether it is an exact match
or a range query. In addition, B<SUP>*</SUP>-tree indexes provide good performance
regardless of the size of the table; performance does not degrade as the table grows.
<H3><FONT COLOR="#000077"><B>Deciding What to Index</B></FONT></H3>
<P>An index is effective only when it is used. The use of the index is determined
primarily by the column values that are indexed. Remember that the more indexes you
have on a table, the more overhead is incurred during updates, insertions, and deletions.
Therefore, it is important to index selectively. Use the following guidelines for
deciding which tables to index:
<UL>
<LI>Index tables when queries select only a small number of rows. Queries that select
a large number of rows defeat the purpose of the index. Use indexes when queries
access less than 5% of the rows in the table.
<P>
<LI>Don't index tables that are frequently updated. Updates, insertions, and deletions
incur extra overhead when indexed. Base your decision to index on the number of updates,
insertions, and deletions relative to the number of queries to the table.
<P>
<LI>Index tables that don't have duplicate values on the columns usually selected
in <TT>WHERE</TT> clauses. Tables in which the selection is based on <TT>TRUE</TT>
or <TT>FALSE</TT> values are not good candidates for indexing.
<P>
<LI>Index tables that are queried with relatively simple <TT>WHERE</TT> clauses.
Complex <TT>WHERE</TT> clauses might not take advantage of indexes.
</UL>
<P>If you decide to use an index, it is important to determine the columns on which
you put the index. Depending on the table, you might choose to index one or more
columns. Use the following guidelines for deciding which columns to index:
<UL>
<LI>Choose columns that are most frequently specified in <TT>WHERE</TT> clauses.
Frequently accessed columns can benefit most from indexes.
<P>
<LI>Don't index columns that do not have many unique values. Columns in which a good
percentage of rows are duplicates cannot take advantage of indexing.
<P>
<LI>Columns that have unique values are excellent candidates for indexing. Oracle
automatically indexes columns that are unique or primary keys defined with constraints.
These columns are most effectively optimized by indexes.
<P>
<LI>Columns that are commonly used to join tables are good candidates for indexing.
<P>
<LI>Frequently modified columns probably should not be index columns because of the
overhead involved in updating the index.
</UL>
<P>In certain situations, the use of composite indexes might be more effective than
individual indexes. Here are some examples of where composite indexes might be quite
useful:
<UL>
<LI>When two columns are not unique individually but are unique together, composite
indexes might work very well. For example, although columns A and B have few unique
values, rows with a particular combination of columns A and B are mostly unique.
Look for <TT>WHERE</TT> clauses with <TT>AND</TT> operators.
<P>
<LI>If all values of a <TT>SELECT</TT> statement are in a composite index, Oracle
does not query the table; the result is returned from the index.
<P>
<LI>If several different queries select the same rows with different <TT>WHERE</TT>
clauses based on different columns, consider creating a composite index with all
the columns used in the <TT>WHERE</TT> statements.
</UL>
<P>Composite indexes can be quite useful when they are carefully designed. As with
single-column indexes, they are most effective if applications are written with the
indexes in mind.</P>
<P>After you create the index, you should periodically use the SQL Trace facility
to determine whether your queries are taking advantage of the indexes. It might be
worth the effort to try the query with and without indexes and then compare the results
to see whether the index is worth the space it uses.</P>
<P>In summary, indexes can significantly improve performance in your system if they
are used properly. You must first decide whether an index is appropriate for the
data and access patterns in your particular system. After you decide to use an index,
you must decide which columns to index. Indexing an inappropriate column or table
can reduce performance. Indexing appropriately can greatly improve performance by
reducing I/Os and speeding access times. Careful planning and periodic testing with
the SQL Trace feature can lead to a very effective use of indexes, with optimal performance
being the outcome.
<H2><FONT COLOR="#000077"><B>Taking Advantage of Indexes</B></FONT></H2>
<P>Because one or more columns are indexed, it is necessary to include the indexed
column or columns in the <TT>WHERE</TT> clause of the SQL statement. Because the
indexed column or columns are referenced in the <TT>WHERE</TT> clause, the optimizer
will immediately know to use an index scan rather than a table scan to access the
requested data. If you do not include the columns that are indexed in the <TT>WHERE</TT>
clause of the SQL statement, the index will probably be bypassed, thus causing a
table scan.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>It takes the right application coding
to take advantage of indexes. If one or more columns are indexed but are not referenced
in the <TT>WHERE</TT> clause of the SQL statements accessing that table, the index
will not be used. Coordination between the DBA and the application developers is
required to take advantage of indexes.
<HR>
</BLOCKQUOTE>
<P>You can tell whether you are taking advantage of indexes by using the Oracle EXPLAIN
PLAN facility to show the query execution plan that the optimizer has chosen for
your SQL statements. If you are using the index as expected, you will see an index
scan in the execution plan.
<H3><FONT COLOR="#000077"><B>How Indexes Improve Performance</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>If there are no indexes on
your table, the only way Oracle can find the data you want is to search every piece
of data in the table and compare it against the requested data. This is typically
referred to as a <I>table scan</I> or <I>full-table scan</I>. A table scan is not
very efficient in most cases because you typically want to select or update only
a few rows.</P>
<P>Because I/Os are expensive operations on a computer system, table scans are very
expensive. Reducing the amount of data that is read from the disk is desirable. By
reducing the amount of data that is read, you reduce system overhead. An index improves
your performance by knowing exactly where the data is on disk and avoiding costly
table scans, thus reducing I/O overhead.
<H3><FONT COLOR="#000077"><B>Creating Indexes</B></FONT></H3>
<P>Indexes can be created either via the graphical utilities provided with Oracle
Enterprise Manager or via the <TT>CREATE INDEX</TT> command. Because Enterprise Manager
and Schema Manager provide essentially the same functionality, I will not discuss
Enterprise Manager here. Schema Manager provides all the functionality of Enterprise
Manager, but with additional features.
<H4><FONT COLOR="#000077"><B>Creating Indexes with Schema Manager</B></FONT></H4>
<P>Schema Manager is used to administer all Oracle schema objects. As you have seen,
indexes and tables are schema objects. Schema Manager provides an easy way to create
indexes graphically. To create an index with Schema Manager, drill down into the
table on which you want to create the index. Right-click the Indexes option under
the table's entry, as shown in Figure 13.3.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 13.3.</B></A></P>
<P><I>Creating an index with Schema Manager.</I></P>
<P>This invokes the Create Index screen, where you provide the information required
to create the index on the selected table. The initial Create Index screen is shown
in Figure 13.4. A number of options must be filled in before the index can be created:
<UL>
<LI>Name--The name of the index. Each index must have a unique identifier.
<P>
<LI>Schema--The schema where the index resides. The index and the table need not
be within the same schema.
<P>
<LI>Type--This is used for partitioning. If this were a partitioned table, you would
be able to choose whether your index was local or global.
<P>
<LI>Index On--You can choose whether you are indexing a table or a cluster.
<P>
<LI>Schema--The schema where the table resides.
<P>
<LI>Table--The table to be indexed.
<P>
<LI>Index Columns--After the table has been selected, a list of the table's columns
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -