ch10.htm

Learn SQL in an easy way...

Software Company                250                  1
Record Club                      25                  2
Cable TV Company                 35                  3
Debtor's Credit Card             35                  4
U-O-Us Insurance Company        125                  5
Joe's Car Palace                350                  5
S.C. Student Loan               200                  6

10 rows selected.

SQL&gt; <B>DROP INDEX ID_CMPD_INDEX;</B>

Index dropped.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You can achieve performance gains by selecting the column with the most unique
values. For instance, every value in the <TT>NAME</TT> field of the <TT>BILLS</TT>
table is unique. When using a compound index, place the most selective field first
in the column list. That is, place the field that you expect to select most often
at the beginning of the list. (The order in which the column names appear in the
<TT>CREATE INDEX</TT> statement does not have to be the same as their order within
the table.) Assume you are routinely using a statement such as the following:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS WHERE NAME = &quot;Cable TV Company&quot;;
</B></FONT></PRE>
<P>To achieve performance gains, you must create an index using the <TT>NAME</TT>
field as the leading column. Here are two examples:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX NAME_INDEX ON BILLS(NAME, AMOUNT);
</B></FONT></PRE>
<P>or</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX NAME_INDEX ON BILLS(NAME);</B>
</FONT></PRE>
<P>The <TT>NAME</TT> field is the left-most column for both of these indexes, so
the preceding query would be optimized to search on the <TT>NAME</TT> field.</P>
<P>Composite indexes are also used to combine two or more columns that by themselves
may have low selectivity. For an example of selectivity, examine the <TT>BANK_ACCOUNTS</TT>
table:</P>
<PRE><FONT COLOR="#0066FF">ACCOUNT_ID     TYPE             BALANCE     BANK
1              Checking         500         First Federal
2              Money Market     1200        First Investor's
3              Checking         90          Credit Union
4              Savings          400         First Federal
5              Checking         2500        Second Mutual
6              Business         4500        Fidelity
</FONT></PRE>
<P>Notice that out of six records, the value <TT>Checking</TT> appears in three of
them. This column has a lower selectivity than the <TT>ACCOUNT_ID</TT> field. Notice
that every value of the <TT>ACCOUNT_ID</TT> field is unique. To improve the selectivity
of your index, you could combine the <TT>TYPE</TT> and <TT>ACCOUNT_ID</TT> fields
in a new index. This step would create a unique index value (which, of course, is
the highest selectivity you can get).


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>An index containing multiple columns
	is often referred to as a <I>composite index</I>. Performance issues may sway your
	decision on whether to use a single-column or composite index. In Oracle, for example,
	you may decide to use a single-column index if most of your queries involve one particular
	column as part of a condition; on the other hand, you would probably create a composite
	index if the columns in that index are often used together as conditions for a query.
	Check your specific implementation on guidance when creating multiple-column indexes.
	
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Using the UNIQUE Keyword with CREATE INDEX</FONT></H3>
<P>Composite indexes are often used with the <TT>UNIQUE</TT> keyword to prevent multiple
records from appearing with the same data. Suppose you wanted to force the <TT>BILLS</TT>
table to have the following built-in &quot;rule&quot;: Each bill paid to a company
must come from a different bank account. You would create a <TT>UNIQUE</TT> index
on the <TT>NAME</TT> and <TT>ACCOUNT_ID</TT> fields. Unfortunately, Oracle7 does
not support the <TT>UNIQUE</TT> syntax. Instead, it implements the <TT>UNIQUE</TT>
feature using the <TT>UNIQUE</TT> integrity constraint. The following example demonstrates
the <TT>UNIQUE</TT> keyword with <TT>CREATE INDEX</TT> using Sybase's Transact-SQL
language.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>create unique index unique_id_name</B>
2&gt; <B>on BILLS(ACCOUNT_ID, NAME)</B>
3&gt; <B>go</B>
1&gt; <B>select * from BILLS</B>
2&gt; <B>go</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                         AMOUNT     ACCOUNT_ID
Florida Water Company        20         1
Power Company                75         1
Phone Company                125        1
Software Company             250        1
Record Club                  25         2
Cable TV Company             35         3
Debtor's Credit Card         35         4
U-O-Us Insurance Company     125        5
Joe's Car Palace             350        5
S.C. Student Loan            200        6
</FONT></PRE>
<P>Now try to insert a record into the <TT>BILLS</TT> table that duplicates data
that already exists.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>insert BILLS (NAME, AMOUNT, ACCOUNT_ID)</B>
2&gt; <B>values(&quot;Power Company&quot;, 125, 1)</B>
3&gt; <B>go</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You should have received an error message telling you that the <TT>INSERT</TT>
command was not allowed. This type of error message can be trapped within an application
program, and a message could tell the user he or she inserted invalid data.
<H4><FONT COLOR="#000077">Example 10.3</FONT></H4>
<P>Create an index on the <TT>BILLS</TT> table that will sort the <TT>AMOUNT</TT>
field in descending order.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX DESC_AMOUNT</B>
     <B>ON  BILLS(AMOUNT DESC);</B>

Index created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This is the first time you have used the <TT>DESC</TT> operator, which tells SQL
to sort the index in descending order. (By default a number field is sorted in ascending
order.) Now you can examine your handiwork:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS;</B>

NAME                         AMOUNT     ACCOUNT_ID
Joe's Car Palace             350        5
Software Company             250        1
S.C. Student Loan            200        6
Phone Company                125        1
U-O-Us Insurance Company     125        5
Power Company                75         1
Cable TV Company             35         3
Debtor's Credit Card         35         4
Record Club                  25         2
Florida Water Company        20         1

10 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example created an index using the <TT>DESC</TT> operator on the column amount.
Notice in the output that the amount is ordered from largest to smallest.
<H3><FONT COLOR="#000077">Indexes and Joins</FONT></H3>
<P>When using complicated joins in queries, your <TT>SELECT</TT> statement can take
a long time. With large tables, this amount of time can approach several seconds
(as compared to the milliseconds you are used to waiting). This type of performance
in a client/server environment with many users becomes extremely frustrating to the
users of your application. Creating an index on fields that are frequently used in
joins can optimize the performance of your query considerably. However, if too many
indexes are created, they can slow down the performance of your system, rather than
speed it up. We recommend that you experiment with using indexes on several large
tables (on the order of thousands of records). This type of experimentation leads
to a better understanding of optimizing SQL statements.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Most implementations have a mechanism
	for gathering the elapsed time of a query; Oracle refers to this feature as timing.
	Check your implementation for specific information. 
<HR>


</BLOCKQUOTE>

<P>The following example creates an index on the <TT>ACCOUNT_ID</TT> fields in the
<TT>BILLS</TT> and <TT>BANK_ACCOUNTS</TT> tables:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX BILLS_INDEX ON BILLS(ACCOUNT_ID);</B>

Index created.

SQL&gt; <B>CREATE INDEX BILLS_INDEX2 ON BANK_ACCOUNTS(ACCOUNT_ID);</B>

Index created.

SQL&gt; <B>SELECT BILLS.NAME NAME, BILLS.AMOUNT AMOUNT, BANK_ACCOUNTS.BALANCE</B>
  2  <B>ACCOUNT_BALANCE</B>
  3 <B> FROM BILLS, BANK_ACCOUNTS</B>
  4  <B>WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;</B>

NAME                         AMOUNT     ACCOUNT_BALANCE
Phone Company                125        500
Power Company                75         500
Software Company             250        500
Florida Water Company        20         500
Record Club                  25         1200
Cable TV Company             35         90
Debtor's Credit Card         35         400
Joe's Car Palace             350        2500
U-O-Us Insurance Company     125        2500
S.C. Student Loan            200        4500

10 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This example first created an index for the <TT>ACCOUNT_ID</TT> on both tables
in the associated query. By creating indexes for <TT>ACCOUNT_ID</TT> on each table,
the join can more quickly access specific rows of data. As a rule, you should index
the column(s) of a table that are unique or that you plan to join tables with in
queries.
<H3><FONT COLOR="#000077">Using Clusters</FONT></H3>
<P>Although we originally said that indexes can be used to present a view of a table
that is different from the existing physical arrangement, this statement is not entirely
accurate. A special type of index supported by many database systems allows the database
manager or developer to cluster data. When a clustered index is used, the physical
arrangement of the data within a table is modified. Using a clustered index usually
results in faster data retrieval than using a traditional, nonclustered index. However,
many database systems (such as Sybase SQL Server) allow only one clustered index
per table. The field used to create the clustered index is usually the primary key
field. Using Sybase Transact-SQL, you could create a clustered, unique index on the
<TT>ACCOUNT_ID</TT> field of the <TT>BANK_ACCOUNTS</TT> table using the following
syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">create unique clustered index id_index
on BANK_ACCOUNTS(ACCOUNT_ID)
 go
</FONT></PRE>
<P>Oracle treats the concept of clusters differently. When using the Oracle relational
database, a cluster is a database object like a database or table. A cluster is used
to store tables with common fields so that their access speed is improved.</P>
<P>Here is the syntax to create a cluster using Oracle7:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE CLUSTER [schema.]cluster
(column datatype [,column datatype] ... )
[PCTUSED integer] [PCTFREE integer]
[SIZE integer [K|M] ]
[INITRANS integer] [MAXTRANS integer]
[TABLESPACE tablespace]
[STORAGE storage_clause]
[!!under!!INDEX
| [HASH IS column] HASHKEYS integer]
</FONT></PRE>
<P>You should then create an index within the cluster based on the tables that will
be added to it. Then you can add the tables. You should add tables only to clusters
that are frequently joined. Do not add tables to clusters that are accessed individually
through a simple <TT>SELECT</TT> statement.</P>
<P>Obviously, clusters are a very vendor-specific feature of SQL. We will not go
into more detail here on their use or on the syntax that creates them. However, consult
your database vendor's documentation to determine whether your database management
system supports these useful objects.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Views are virtual tables. Views are simply a way of presenting data in a format
that is different from the way it actually exists in the database. The syntax of
the <TT>CREATE VIEW</TT> statement uses a standard <TT>SELECT</TT> statement to create
the view (with some exceptions). You can treat a view as a regular table and perform
inserts, updates, deletes, and selects on it. We briefly discussed the use of database
security and how views are commonly used to implement this security. Database security
is covered in greater detail on Day 12.</P>
<P>The basic syntax used to create a view is</P>
<PRE><FONT COLOR="#0066FF">CREATE VIEW view_name AS
SELECT field_name(s) FROM table_name(s);
</FONT></PRE>
<P>Here are the most common uses of views:

<UL>
	<LI>To perform user security functions
	<P>
	<LI>To convert units
	<P>
	<LI>To create a new virtual table format
	<P>
	<LI>To simplify the construction of complex queries
</UL>

<P>Indexes are also database design and SQL programming tools. Indexes are physical
database objects stored by your database management system that can be used to retrieve
data already sorted from the database. In addition, thanks to the way indexes are
mapped out, using indexes and properly formed queries can yield significant performance
improvements.</P>
<P>The basic syntax used to create an index looks like this:</P>
<PRE><FONT COLOR="#0066FF">CREATE INDEX index_name
ON table_name(field_name(s));
</FONT></PRE>
<P>Some database systems include very useful additional options such as the <TT>UNIQUE</TT>
and <TT>CLUSTERED</TT> keywords.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q If the data within my table is already in sorted order, why should I use
	an index on that table?</B>
	<P><B>A</B> An index still gives you a performance benefit by looking quickly through
	key values in a tree. The index can locate records faster than a direct access search
	through each record within your database. Remember--the SQL query processor doesn't
	necessarily know that your data is in sorted order.</P>
	<P><B>Q Can I create an index that contains fields from multiple tables?</B></P>
	<P><B>A</B> No, you cannot. However, Oracle7, for instance, allows you to create
	a cluster. You can place tables within a cluster and create cluster indexes on fields
	that are common to the tables. This implementation is the exception, not the rule,
	so be sure to study your documentation on this topic in more detail.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> What will happen if a unique index is created on a nonunique field?
	<P><B>2.</B> Are the following statements true or false?</P>
	<P>Both views and indexes take up space in the database and therefore must be factored
	in the planning of the database size.</P>
	<P>If someone updates a table on which a view has been created, the view mus