Software Company                250                  1Record Club                      25                  2Cable TV Company                 35                  3Debtor's Credit Card             35                  4U-O-Us Insurance Company        125                  5Joe's Car Palace                350                  5S.C. Student Loan               200                  610 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 uniquevalues. 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 firstin the column list. That is, place the field that you expect to select most oftenat 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 withinthe 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, sothe 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 themselvesmay have low selectivity. For an example of selectivity, examine the <TT>BANK_ACCOUNTS</TT>table:</P><PRE><FONT COLOR="#0066FF">ACCOUNT_ID     TYPE             BALANCE     BANK1              Checking         500         First Federal2              Money Market     1200        First Investor's3              Checking         90          Credit Union4              Savings          400         First Federal5              Checking         2500        Second Mutual6              Business         4500        Fidelity</FONT></PRE><P>Notice that out of six records, the value <TT>Checking</TT> appears in three ofthem. This column has a lower selectivity than the <TT>ACCOUNT_ID</TT> field. Noticethat every value of the <TT>ACCOUNT_ID</TT> field is unique. To improve the selectivityof your index, you could combine the <TT>TYPE</TT> and <TT>ACCOUNT_ID</TT> fieldsin a new index. This step would create a unique index value (which, of course, isthe 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 multiplerecords 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 companymust come from a different bank account. You would create a <TT>UNIQUE</TT> indexon the <TT>NAME</TT> and <TT>ACCOUNT_ID</TT> fields. Unfortunately, Oracle7 doesnot support the <TT>UNIQUE</TT> syntax. Instead, it implements the <TT>UNIQUE</TT>feature using the <TT>UNIQUE</TT> integrity constraint. The following example demonstratesthe <TT>UNIQUE</TT> keyword with <TT>CREATE INDEX</TT> using Sybase's Transact-SQLlanguage.</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_IDFlorida Water Company        20         1Power Company                75         1Phone Company                125        1Software Company             250        1Record Club                  25         2Cable TV Company             35         3Debtor's Credit Card         35         4U-O-Us Insurance Company     125        5Joe's Car Palace             350        5S.C. Student Loan            200        6</FONT></PRE><P>Now try to insert a record into the <TT>BILLS</TT> table that duplicates datathat 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 applicationprogram, 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 SQLto sort the index in descending order. (By default a number field is sorted in ascendingorder.) 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_IDJoe's Car Palace             350        5Software Company             250        1S.C. Student Loan            200        6Phone Company                125        1U-O-Us Insurance Company     125        5Power Company                75         1Cable TV Company             35         3Debtor's Credit Card         35         4Record Club                  25         2Florida Water Company        20         110 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 takea 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 performancein a client/server environment with many users becomes extremely frustrating to theusers of your application. Creating an index on fields that are frequently used injoins can optimize the performance of your query considerably. However, if too manyindexes are created, they can slow down the performance of your system, rather thanspeed it up. We recommend that you experiment with using indexes on several largetables (on the order of thousands of records). This type of experimentation leadsto 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_BALANCEPhone Company                125        500Power Company                75         500Software Company             250        500Florida Water Company        20         500Record Club                  25         1200Cable TV Company             35         90Debtor's Credit Card         35         400Joe's Car Palace             350        2500U-O-Us Insurance Company     125        2500S.C. Student Loan            200        450010 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>This example first created an index for the <TT>ACCOUNT_ID</TT> on both tablesin 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 indexthe column(s) of a table that are unique or that you plan to join tables with inqueries.<H3><FONT COLOR="#000077">Using Clusters</FONT></H3><P>Although we originally said that indexes can be used to present a view of a tablethat is different from the existing physical arrangement, this statement is not entirelyaccurate. A special type of index supported by many database systems allows the databasemanager or developer to cluster data. When a clustered index is used, the physicalarrangement of the data within a table is modified. Using a clustered index usuallyresults in faster data retrieval than using a traditional, nonclustered index. However,many database systems (such as Sybase SQL Server) allow only one clustered indexper table. The field used to create the clustered index is usually the primary keyfield. 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 followingsyntax:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">create unique clustered index id_indexon BANK_ACCOUNTS(ACCOUNT_ID) go</FONT></PRE><P>Oracle treats the concept of clusters differently. When using the Oracle relationaldatabase, a cluster is a database object like a database or table. A cluster is usedto 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 willbe added to it. Then you can add the tables. You should add tables only to clustersthat are frequently joined. Do not add tables to clusters that are accessed individuallythrough a simple <TT>SELECT</TT> statement.</P><P>Obviously, clusters are a very vendor-specific feature of SQL. We will not gointo more detail here on their use or on the syntax that creates them. However, consultyour database vendor's documentation to determine whether your database managementsystem 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 formatthat is different from the way it actually exists in the database. The syntax ofthe <TT>CREATE VIEW</TT> statement uses a standard <TT>SELECT</TT> statement to createthe view (with some exceptions). You can treat a view as a regular table and performinserts, updates, deletes, and selects on it. We briefly discussed the use of databasesecurity and how views are commonly used to implement this security. Database securityis 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 ASSELECT 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 physicaldatabase objects stored by your database management system that can be used to retrievedata already sorted from the database. In addition, thanks to the way indexes aremapped out, using indexes and properly formed queries can yield significant performanceimprovements.</P><P>The basic syntax used to create an index looks like this:</P><PRE><FONT COLOR="#0066FF">CREATE INDEX index_nameON 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 thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers 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