ch13.htm

Teach yourself Oracle8 in 21 day

	is displayed. If you click the Order column in the order you want the index columns
	to be created, you will see a number appear. This indicates the index order.
	<P>
	<LI>Options--Optional parameters define whether the index is unique, sorted, and
	whether the index creation is written to the log file. For large indexes, it might
	be beneficial to abstain from logging the index creation to save log space and improve
	performance.
</UL>

<P><A NAME="04"></A><A HREF="04.htm"><B>Figure 13.4.</B></A></P>
<P><I>The Create Index screen.</I></P>
<P>A completed Create Index screen is shown in Figure 13.5. Here I have selected
to index the <TT>DOGS</TT> <TT>ID</TT> field. This will speed up access to the <TT>DOGS</TT>
table whenever I am using the <TT>ID</TT> column in the <TT>WHERE</TT> clause. When
I access a row in the database where the selection criteria are based on a value
of the <TT>ID</TT> column, the index will be used.</P>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 13.5.</B></A></P>
<P><I>Creating an index for the <TT>DOGS</TT> table on the <TT>ID</TT> field.</I></P>
<P>That is really all there is to creating an index using Schema Manager. The index
will be automatically maintained by Oracle after the index has been created. If you
use the <TT>CREATE INDEX</TT> command, you have more options available.
<H4><FONT COLOR="#000077"><B>Creating Indexes with the </B>CREATE INDEX<B> Command</B></FONT></H4>
<P>With this command you can specify all the options available with Schema Manager,
as well as other options. Rather than go over all the options available with the
<TT>CREATE INDEX</TT> command, I will highlight some of the optional parameters that
are not available with the graphical tools. Please reference the Oracle SQL Reference
Manual for the complete syntax of the <TT>CREATE INDEX</TT> command.

<UL>
	<LI><TT>BITMAP</TT>--This parameter is used to specify a bitmap index. The bitmap
	index is described in detail in the section titled &quot;Bitmap Indexes.&quot;
	<P>
	<LI><TT>NOSORT</TT>--This specifies to the index-creation process that the rows are
	already in sorted order, thus skipping the sort process. This can save substantial
	time in creating the index.
	<P>
	<LI><TT>REVERSE</TT>--This parameter specifies that the blocks in the index are stored
	in reverse order.
	<P>
	<LI><TT>GLOBAL</TT>--This parameter specifies a global partitioned index, as described
	in the section titled &quot;Global Indexes.&quot;
	<P>
	<LI><TT>PARTITION</TT>--This parameter is used for partitioned indexes, as described
	in the section titled &quot;Partitioned Indexes.&quot;
	<P>
	<LI><TT>VALUES LESS THAT</TT>--This parameter is also used for partitioned indexes,
	as described in the section titled &quot;Partitioned Indexes.&quot;
	<P>
	<LI><TT>PARALLEL</TT>--This parameter allows you to specify the degree of parallelism
	for the index-creation process. For large indexes, the time savings can be substantial.
	<P>
	<LI><TT>STORAGE</TT> clause--This parameter allows you to modify the storage parameters
	for the index. This is the same <TT>STORAGE</TT> clause you saw before.
</UL>

<P>In many cases, creating the index by hand with the <TT>CREATE INDEX</TT> command
can be beneficial. In addition to allowing you more control over the storage and
parallelism of the index creation, through scripting you have a saved record of the
index-creation process. If you are creating indexes on large tables, the <TT>CREATE
INDEX</TT> command might be the best way for you to create the index. An example
of the <TT>CREATE INDEX</TT> procedure is shown in Listing 13.1.
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT><FONT COLOR="#000077"><B> </B></FONT></H4>

<H4><FONT COLOR="#000077"><B>Listing 13.1. Using the </B>CREATE INDEX<B> command.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">CREATE INDEX &quot;ETW&quot;.dogs_ix1
ON  &quot;ETW&quot;.&quot;DOGS&quot; (&quot;ID&quot;)
PARALLEL (DEGREE 10)
<TT>TABLESPACE DOGS;</TT> </FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>This creates the same index as shown with Schema Manager except that it uses a
parallelism of 10. This allows 10 parallel processes to scan the table to retrieve
the data necessary to create the index.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>There is no limit to the number
	of indexes that you can create on a table. Remember that each time a column is updated
	or inserted, the index might need to be modified. This causes overhead on the system.
	For tables whose access pattern is mostly read, there is very little penalty for
	multiple indexes except in terms of the space they use. Even though indexes can take
	up considerable space, they are well worth it. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Partitioned Indexes</B></FONT></H3>
<P>As you saw yesterday, a new feature of Oracle8 is range partitioning. Like tables,
indexes can also be partitioned; but with indexes you have a few more options because
the underlying table might or might not also be partitioned. There are essentially
two different types of partitioned indexes available with Oracle8:

<UL>
	<LI>Global indexes--These are created in a manner different from the underlying partitioning
	of the table that is indexed.
</UL>


<UL>
	<LI>Local indexes--These are partitioned in the same manner as the underlying table
	partitioning.
</UL>

<H4><FONT COLOR="#000077"><B>Global Indexes</B></FONT></H4>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>global index</I> can
be thought of as a single B<SUP>*</SUP>-tree index over the entire contents of the
table. Even though this is one large index, it can be partitioned. This single index
has information about all rows in all partitions in the table.</P>
<P>To create a global partitioned index, use the <TT>CREATE INDEX</TT> parameter
<TT>GLOBAL</TT>. This specifies that the index will be a global index. Further partitioning
of the index is accomplished by using the following parameters:

<UL>
	<LI><TT>GLOBAL</TT>--This parameter specifies a global partitioned index.
	<P>
	<LI><TT>PARTITION</TT>part_name--This parameter is used to identify the partition.
	If you do not specify the partition name, a default name will be provided. It is
	not usually necessary to provide the partition name.
	<P>
	<LI><TT>VALUES LESS THAT</TT>--This parameter is used to specify the range that is
	allocated for that particular partition in the same way as the partition was specified
	in the <TT>CREATE TABLE</TT> statement (discussed yesterday).
</UL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The last partition should contain
	the keyword <TT>MAXVALUE</TT> for its range. 
<HR>


</BLOCKQUOTE>

<P>For example, to range-partition the index that was created earlier in this lesson,
use the syntax that appears in Listing 13.2.
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 13.2. Creating a partitioned index.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">CREATE INDEX &quot;ETW&quot;.dogs_ix1
ON  DOGS (ID)
PARTITION BY RANGE (ID)
PARTITION pt1 VALUES LESS THAN (`1000') TABLESPACE ts1,
<TT>PARTITION pt2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2);</TT></FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>This create two partitions, the first holding values of <TT>ID</TT> that are less
than 1,000, the second holding the remaining values of <TT>ID</TT>. If you do not
specify the partition name, as is the case here, a default name will be provided.
<H4><FONT COLOR="#000077"><B>Local Indexes</B></FONT></H4>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>In contrast to the global
index, a <I>local partitioned index</I> is individually created on each partition.
If you specify a local partitioned index, Oracle automatically maintains the index's
partitioning along with that of the underlying table.</P>
<P>Local partitioned indexes are created through the use of the <TT>LOCAL</TT> parameter
with the <TT>CREATE INDEX</TT> statement. It is unnecessary to provide partitioning
information because the underlying table partitioning will be used. A local index
can be created with the following syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE INDEX &quot;ETW&quot;.dogs_ix1
ON  DOGS (ID)
LOCAL;
</FONT></PRE>
<P>Because the index is local, all partition changes to the table will be automatically
reflected on the index partitions as well.</P>
<P>Local partitioned indexes have some inherent advantages that are similar to the
advantages you get from partitioned tables. These advantages include the following:

<UL>
	<LI>Because the index exists entirely on one partition, any maintenance operations
	affect only that one partition.
	<P>
	<LI>The Oracle optimizer can use the local index to generate better query plans based
	on the fact that a local index is used.
	<P>
	<LI>If a partition is lost and must be recovered, only the data and index for that
	particular partition needs to be recovered. With a global index, the entire index
	would need recovery.
</UL>

<P>As you can see, there are many advantages of using both global and local partitioned
indexes.
<H3><FONT COLOR="#000077"><B>Index-Only Tables</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>An <I>index-only table</I>
is a schema object introduced in Oracle8. An index-only table is similar to an index,
but whereas an index contains the primary key value and a <TT>ROWID</TT> pointing
to where the data is kept, the index-only table stores the column data in the leaf
block of the index.</P>
<P>Because the leaf blocks of the Oracle index are traditionally very small and tightly
packed, there can be some drawbacks to having large rows stored there. Oracle has
developed a way to compensate for this: If rows become too large (by a set threshold),
the row data is stored in an overflow area as specified in the <TT>CREATE TABLE</TT>
statement. This creates storage more like the traditional index and table relationship.</P>
<P>An index-only table contains the same structure as the Oracle B*-tree index. Only
the leaf blocks have changed. Index-only tables have many of the attributes of both
indexes and tables, but there are a few exceptions:

<UL>
	<LI>Because it is part index and part table, no other indexes can be added to the
	index-only table.
	<P>
	<LI>The <TT>UNIQUE</TT> constraint is not allowed on an index-only table.
	<P>
	<LI>A trigger can be added to the index-only table.
	<P>
	<LI>An index-only table cannot be stored in a cluster.
	<P>
	<LI>Replication is not supported at this time.
</UL>

<P>As you can see, there are some restrictions on index-only tables, but there are
also a great deal of benefits.
<H4><FONT COLOR="#000077"><B>When to Use Index-Only Tables</B></FONT></H4>
<P>Index-only tables are very useful whenever data is always accessed via the primary
key index. If this is the case with your data, the index-only table will cut down
on the space used by both the index and the table (by combining them) and improve
performance. Performance is improved because, by the time the <TT>ROWID</TT> would
have been retrieved, you have the data.</P>
<P>Tables that are not accessed via the primary key value are not good candidates
for index-only tables. Also, tables whose primary key values are updated and tables
that have frequent insertions are not good candidates for index-only tables.
<H4><FONT COLOR="#000077"><B>How to Create Index-Only Tables</B></FONT></H4>
<P>Index-only tables are created with the <TT>CREATE TABLE</TT> command; the <TT>ORGANIZATION
INDEXED</TT> qualifier is used to identify the table as index-only. The following
qualifiers are used in creating index-only tables:

<UL>
	<LI><TT>ORGANIZATION INDEXED</TT>--This qualifier specifies an index-only table organization.
	<P>
	<LI><TT>OVERFLOW TABLESPACE</TT>ts_name--This qualifier specifies the overflow tablespace
	name.
	<P>
	<LI><TT>PCTTHRESHOLD</TT>threshold--This qualifier specifies the percent of a block
	that a row must be larger than in order to be offloaded to the overflow tablespace.
</UL>

<P>An example of how to create an index-only table is shown in Listing 13.3.
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 13.3. Creating an index-only table with the
</B>CREATE TABLE<B> command.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">CREATE TABLE &quot;ETW&quot;.DOGS (
ID NUMBER,
NAME VARCHAR2(40),
OWNER_ID NUMBER,
BREED_ID NUMBER,
RANK NUMBER NULL,
NOTES VARCHAR2(80)
PRIMARY KEY(ID) )
ORGANIZATION INDEXED
PCTTHRESHOLD 40
OVERFLOW TABLESPACE &quot;DOGS2&quot;
<TT>TABLESPACE &quot;DOGS&quot;;</TT> </FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>This specifies that the index-only tablespace be created on the <TT>DOGS</TT>
tablespace, whereas the overflow tablespace used is <TT>DOGS2</TT>.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>It is necessary to specify the <TT>PRIMARY
	KEY</TT> value when creating an index-only table. This is the value on which the
	index is created. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Bitmap Indexes</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Another type of index available
in Oracle8 is the <I>bitmap index</I>. With the traditional index you saw earlier,
Oracle uses a B<SUP>*</SUP>-tree method to traverse the index to find the leaf block.
With a bitmap index, a bitmap of <TT>ROWID</TT>s is kept; this bitmap indicates which
rows correspond to the index item. If the bit is set, this indicates that the corresponding