ch13.htm

Teach yourself Oracle8 in 21 day

row contains the key value; if the bit is not set, the opposite is true.</P>
<P>As you can probably tell, bitmap indexes can probably be quite useful under the
right circumstances, and useless overhead otherwise. An example of a bitmap index
is shown in Figure 13.6.</P>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 13.6.</B></A></P>
<P><I>A bitmap index.</I></P>
<P>
<H4><FONT COLOR="#000077"><B>When to Use Bitmap Indexes</B></FONT></H4>
<P>As you can probably guess, the bitmap index works well on items with low cardinality.
<I>Low cardinality</I> means there is a small amount of variance in the possible
values stored in that column. For example, the column representing the sex of the
dog is said to have low cardinality because only two values are possible. Other column
types that might have low cardinality include

<UL>
	<LI>Marital status
	<P>
	<LI>Account status (good or bad)
	<P>
	<LI>Sales region (if there are only a few)
	<P>
	<LI>Rank (if there are only a few)
	<P>
	<LI>Special notes (whether there is a note)
</UL>

<P>With columns that have low cardinality, the bitmap index can greatly improve performance.
Columns with high cardinality are not candidates for bitmap indexes.
<H4><FONT COLOR="#000077"><B>How to Create Bitmapped Indexes</B></FONT></H4>
<P>A bitmap index is created with the <TT>CREATE INDEX</TT> command with the <TT>BITMAP</TT>
qualifier. For example, the following will create a bitmap index:</P>
<PRE><FONT COLOR="#0066FF">CREATE BITMAP INDEX
</FONT></PRE>
<P>To create a bitmap index on the <TT>SEX</TT> field in the <TT>DOGS</TT> table,
you can use the following syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE BITMAP INDEX &quot;ETW&quot;.dogs_bx1
ON  DOGS (SEX);
</FONT></PRE>
<P>This simple statement will create the bitmap index on the column specified. At
this time, bitmap indexes cannot be created with the graphical tools.
<H2><FONT COLOR="#000077"><B>Table Sequences</B></FONT></H2>
<P>It is frequently necessary to generate a sequence of numbers to use in your database.
For example, you might need these numbers to identify a particular record. 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 with as many as
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.</P>
<P>Sequences can be created either with graphical tools, such as Schema Manager,
or with the <TT>CREATE SEQUENCE</TT> command.
<H3><FONT COLOR="#000077"><B>Creating Sequences with Schema Manager</B></FONT></H3>
<P>To create a sequence with Schema Manager, select the Create option after right-clicking
the Sequence icon. This invokes the Create Sequence screen. By filling in parameters
such as Minimum, Maximum, Increment, and so on, you can specify how the sequence
works. A filled-in Create Sequence screen is shown in Figure 13.7.</P>
<P><A NAME="07"></A><A HREF="07.htm"><B>Figure 13.7.</B></A></P>
<P><I>The Create Sequence screen.</I></P>
<P>
<H3><FONT COLOR="#000077"><B>Creating Sequences with the </B>CREATE SEQUENCE<B> Command</B></FONT></H3>
<P>Sequences can be created with the <TT>CREATE SEQUENCE</TT> command. This command
has the following syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE SEQUENCE DOG_ID
  INCREMENT BY 1
  START WITH 1
  NOMAXVALUE
  NOCYCLE
  CACHE 4;
</FONT></PRE>
<P>The following provides brief descriptions of each parameter:

<UL>
	<LI><TT>INCREMENT BY</TT>--This parameter specifies the amount by which to increment
	the sequence each time a value is obtained.
	<P>
	<LI><TT>START WITH</TT>--This parameter specifies the starting value.
	<P>
	<LI><TT>MAXVALUE</TT>n--This parameter specifies the maximum value that the sequence
	can obtain.
	<P>
	<LI><TT>NOMAXVALU</TT> (Default)--This parameter specifies no maximum value for a
	sequence. The sequence can grow to 10<SUP>27</SUP> for ascending sequences and -1
	for descending sequences.
	<P>
	<LI><TT>MINVALUE</TT>n--This parameter specifies the minimum value of the sequence.
	<P>
	<LI><TT>NOMINVALU</TT> (Default)--This parameter specifies no minimum value for a
	sequence. The sequence can have a minimum of 1 for ascending sequences and -10<SUP>26</SUP>
	for descending sequences.
	<P>
	<LI><TT>CYCLE</TT>--This parameter specifies that a sequence will restart after reaching
	the maximum or minimum value.
	<P>
	<LI><TT>NOCYCLE</TT> (Default)--This parameter specifies that the sequence cannot
	recycle after reaching the maximum or minimum value.
	<P>
	<LI><TT>CACHE</TT>n--This parameter specifies the number of sequence entries to cache
	for quick access. (The default is 20 values.)
	<P>
	<LI><TT>NOCACHE</TT>--This parameter specifies that no sequence entries should be
	cached.
	<P>
	<LI><TT>ORDER</TT>--This parameter specifies that sequence entries are generated
	in the order in which they are requested. By default, this is not the case.
	<P>
	<LI><TT>NOORDER</TT> (Default)--This parameter specifies that sequence numbers are
	not necessarily generated in the order in which they are requested. This is usually
	fine for primary key values.
</UL>

<H3><FONT COLOR="#000077"><B>Tuning Sequences</B></FONT></H3>
<P>To get the best performance out of sequences, you should cache as many sequences
as you think you will have simultaneous requests for. By over-specifying the number
of cached sequences, you use more memory than necessary. By under-specifying the
number of cached entries, you cause undue waiting for the sequences.</P>
<P>Using the Oracle sequence generator is much more efficient than manually generating
sequences. If you have a series of values that must be sequential, I recommend using
the Oracle sequence generator.
<H3><FONT COLOR="#000077"><B>Using Sequences</B></FONT></H3>
<P>To generate a new sequence value, simply reference the value of sequence_name<TT>.NEXTVAL</TT>.
To re-reference that number from within the same SQL block, reference the value of
sequence_name<TT>.CURVAL</TT>. When you reference sequence_name<TT>.NEXTVAL</TT>,
a new sequence number is generated.</P>
<P>Listing 13.4 contains the sequence created in the preceding section to generate
a new value in the <TT>DOGS</TT> table. The result of this <TT>INSERT</TT> statement
is to insert a dog with a sequentially growing value for the <TT>ID</TT> column.
Remember, the <TT>ID</TT> column of the <TT>DOGS</TT> table is a sequential value
for the dog's ID number.
<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 13.4. Inserting a row using a sequence.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">SQL&gt; INSERT INTO dogs
  2  ( id, name)
  3  VALUES
  4  ( DOG_ID.NEXTVAL, `Shasta');
<TT>1 row created.</TT> </FONT></PRE>
<P>Sequences are incremented as they are accessed, independent of rollback or commit.
If a transaction generates a sequence and then rolls back, the sequence is not replaced.
Therefore, there might be holes in your sequential values. This is usually not a
problem.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Because sequences are generated
	independently of commits or rollbacks, you might have gaps in the sequences. Although
	this is usually not an issue, you should make a note of it. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Using Cached Sequences for Primary Key Values</B></FONT></H3>
<P>As shown in the preceding example, it can be efficient to use cached sequences
to generate unique primary-key values. Not only is the performance of the cached
sequence good, you are guaranteed a unique number (unless you have enabled <TT>CYCLE</TT>).


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>If you use cached sequences to
	generate primary-key values, be sure to set the <TT>NOCYCLE</TT> parameter for the
	sequence and make sure the minimum and maximum values are sufficiently high. Cycling
	sequences causes integrity constraints to be violated. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Today you learned about indexes and sequences. 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 better design your applications to take advantage of it. 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>You also learned about the Oracle sequence generator. Using the sequence generator,
you can generate a unique sequence of numbers quickly and without duplication. 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 with as many as
38 digits, without having to manually lock records. When you define a sequence, you
can specify the original values of that sequence, whether the sequence should be
cached, and whether the sequence should be in ascending or descending order.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>Tomorrow you will learn about Oracle clusters: the index cluster and the hash
cluster. You will learn what these schema objects are and how to use them. You will
also learn about another Oracle schema object, the stored procedure. Stored procedures
are used to perform application functions within the Oracle instance itself. Finally,
you will learn about database links, which are also Oracle schema objects. After
you complete Day 14, &quot;Using Oracle Clusters, Stored Procedures, and Database
Links,&quot; you will be familiar with all the Oracle schema objects.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q How are indexes important to an RDBMS?</B>
	<P><B>A</B> Indexes are important mainly from a performance standpoint. Without indexes,
	every access to the database would have to be done based on a scan of all records
	in the table. An index provides a mechanism by which to find data quickly without
	having to read all records in a table.</P>
	<P><B>Q What are some important factors involved in creating an index?</B></P>
	<P><B>A</B> Several factors should be kept in mind when creating an index. First,
	an index is only useful when it is accessed. If you do not access the table using
	the columns that you have indexed in the <TT>WHERE</TT> clause, the index will not
	be used. Second, if the table is used primarily for historical data (many insertions,
	few selections), it is not a good candidate for indexing.</P>
	<P><B>Q What makes a column a good candidate to be used in an index?</B></P>
	<P><B>A</B> Columns that have many unique values are good candidates for indexing.
	If a combination of columns is unique, a complex index might be better.</P>
	<P><B>Q How are sequences useful?</B></P>
	<P><B>A</B> Sequences are useful because they provide a mechanism to generate a unique
	sequence of numbers quickly with reduced locking on the database.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Answers to the question in the workshop can be found in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1.</B> How many indexes can be created on a single table?
	<P><B>2.</B> Can an index be created in parallel?</P>
	<P><B>3.</B> What is a B<SUP>*</SUP>-tree index?</P>
	<P><B>4.</B> Can indexes be partitioned?</P>
	<P><B>5.</B> What is an index-only table?</P>
	<P><B>6. </B>What is a bitmap index?</P>
	<P><B>7. </B>What is a sequence used for?</P>
	<P><B>8. </B>How big can a sequence be?</P>
	<P><B>9. </B>Does an index need to be based on unique values?</P>
	<P><B>10. </B>Can an index be used to enforce uniqueness?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1.</B> Create a simple index on one column using Schema Manager.
	<P><B>2.</B> Create a compound index using Schema Manager.</P>
	<P><B>3. </B>Delete that index using Schema Manager.</P>
	<P><B>4.</B> Create and delete a sequence using Schema Manager.
</DL>

<CENTER>
<P>
<HR>
<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> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>