<P>In common with every other <TT>SQL CREATE</TT>... command, <TT>CREATE VIEW</TT>has a corresponding <TT>DROP</TT>... command. The syntax is as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; DROP VIEW view_name;</FONT></PRE><P>The only thing to remember when using the <TT>DROP VIEW</TT> command is that allother views that reference that view are now invalid. Some database systems evendrop all views that used the view you dropped. Using Personal Oracle7, if you dropthe view <TT>BILLS1</TT>, the final query would produce the following error:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>DROP VIEW BILLS1;</B>View dropped.SQL&gt; <B>SELECT * FROM BANKS_IN_TEXAS;</B>*ERROR at line 1:ORA-04063: view &quot;PERKINS.BANKS_IN_TEXAS&quot; has errors</FONT></PRE><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>A view can be dropped without any	of the actual tables being modified, which explains why we often refer to views as	virtual tables. (The same logic can be applied to the technology of virtual reality.)	<HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Using Indexes</FONT></H2><P>Another way to present data in a different format than it physically exists onthe disk is to use an index. In addition, indexes can also reorder the data storedon the disk (something views cannot do).</P><P>Indexes are used in an SQL database for three primary reasons:<UL>	<LI>To enforce referential integrity constraints by using the <TT>UNIQUE</TT> keyword	<P>	<LI>To facilitate the ordering of data based on the contents of the index's field	or fields	<P>	<LI>To optimize the execution speed of queries</UL><H3><FONT COLOR="#000077">What Are Indexes?</FONT></H3><P>Data can be retrieved from a database using two methods. The first method, oftencalled the Sequential Access Method, requires SQL to go through each record lookingfor a match. This search method is inefficient, but it is the only way for SQL tolocate the correct record. Think back to the days when libraries had massive cardcatalog filing systems. Suppose the librarian removed the alphabetical index cards,tossed the cards into the air, then placed them back into the filing cabinets. Whenyou wanted to look up this book's shelf location, you would probably start at thevery beginning, then go through one card at a time until you found the informationyou wanted. (Chances are, you would stop searching as soon as you found any bookon this topic!)</P><P>Now suppose the librarian sorted the book titles alphabetically. You could quicklyaccess this book's information by using your knowledge of the alphabet to move throughthe catalog.</P><P>Imagine the flexibility if the librarian was diligent enough to not only sortthe books by title but also create another catalog sorted by author's name and anothersorted by topic. This process would provide you, the library user, with a great dealof flexibility in retrieving information. Also, you would be able to retrieve yourinformation in a fraction of the time it originally would have taken.</P><P>Adding indexes to your database enables SQL to use the Direct Access Method. SQLuses a treelike structure to store and retrieve the index's data. Pointers to a groupof data are stored at the top of the tree. These groups are called nodes. Each nodecontains pointers to other nodes. The nodes pointing to the left contain values thatare less than its parent node. The pointers to the right point to values greaterthan the parent node.</P><P>The database system starts its search at the top node and simply follows the pointersuntil it is successful.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The result of a query against the	unindexed table is commonly referred to as a <I>full-table scan</I>. A full-table	scan is the process used by the database server to search every row of a table until	all rows are returned with the given condition(s). This operation is comparable to	searching for a book in the library by starting at the first book on the first shelf	and scanning every book until you find the one you want. On the other hand, to find	the book quickly, you would probably look in the (computerized) card catalog. Similarly,	an index enables the database server to point to specific rows of data quickly within	a table. <HR></BLOCKQUOTE><P>Fortunately, you are not required to actually implement the tree structure yourself,just as you are not required to write the implementation for saving and reading intables or databases. The basic SQL syntax to create an index is as follows:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt;  <B>CREATE INDEX index_name</B>  2  <B> ON table_name(column_name1, [column_name2], ...);</B>Index created.</FONT></PRE><P>As you have seen many times before, the syntax for <TT>CREATE INDEX</TT> can varywidely among database systems. For instance, the <TT>CREATE INDEX</TT> statementunder Oracle7 looks like this:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CREATE INDEX [schema.]indexON { [schema.]table (column [!!under!!ASC|DESC]     [, column [!!under!!ASC|DESC]] ...)   | CLUSTER [schema.]cluster }[INITRANS integer] [MAXTRANS integer][TABLESPACE tablespace][STORAGE storage_clause][PCTFREE integer][NOSORT]</FONT></PRE><P>The syntax for <TT>CREATE INDEX</TT> using Sybase SQL Server is as follows:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">create [unique] [clustered | nonclustered]       index index_nameon [[database.]owner.]table_name (column_name    [, column_name]...)[with {fillfactor = x, ignore_dup_key, sorted_data,      [ignore_dup_row | allow_dup_row]}][on segment_name]</FONT></PRE><P>Informix SQL implements the command like this:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">CREATE [UNIQUE | DISTINCT] [CLUSTER] INDEX index_nameON table_name (column_name [ASC | DESC],               column_name [ASC | DESC]...)</FONT></PRE><P>Notice that all of these implementations have several things in common, startingwith the basic statement</P><PRE><FONT COLOR="#0066FF">CREATE INDEX index_name ON table_name (column_name, ...)</FONT></PRE><P>SQL Server and Oracle allow you to create a clustered index, which is discussedlater. Oracle and Informix allow you to designate whether the column name shouldbe sorted in ascending or descending order. We hate to sound like a broken record,but, once again, you should definitely consult your database management system'sdocumentation when using the <TT>CREATE INDEX</TT> command.</P><P>For instance, to create an index on the <TT>ACCOUNT_ID</TT> field of the <TT>BILLS</TT>table, the <TT>CREATE INDEX</TT> statement would look like this:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME                         AMOUNT     ACCOUNT_IDPhone Company                125        1Power Company                75         1Record Club                  25         2Software Company             250        1Cable TV Company             35         3Joe's Car Palace             350        5S.C. Student Loan            200        6Florida Water Company        20         1U-O-Us Insurance Company     125        5Debtor's Credit Card         35         410 rows selected.</FONT></PRE><H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX ID_INDEX ON BILLS( ACCOUNT_ID );</B>Index created.SQL&gt; <B>SELECT * FROM BILLS;</B>NAME                         AMOUNT     ACCOUNT_IDPhone Company                125        1Power Company                75         1Software Company             250        1Florida Water Company        20         1Record Club                  25         2Cable TV Company             35         3Debtor's Credit Card         35         4Joe's Car Palace             350        5U-O-Us Insurance Company     125        5S.C. Student Loan            200        610 rows selected.</FONT></PRE><P>The <TT>BILLS</TT> table is sorted by the <TT>ACCOUNT_ID</TT> field until theindex is dropped using the <TT>DROP INDEX</TT> statement. As usual, the <TT>DROPINDEX</TT> statement is very straightforward:</P><H5>SYNTAX:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>DROP INDEX index_name;</B></FONT></PRE><P>Here's what happens when the index is dropped:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>DROP INDEX ID_INDEX;</B>Index dropped.SQL&gt; <B>SELECT * FROM BILLS;</B>NAME                         AMOUNT     ACCOUNT_IDPhone Company                125        1Power Company                75         1Record Club                  25         2Software Company             250        1Cable TV Company             35         3Joe's Car Palace             350        5S.C. Student Loan            200        6Florida Water Company        20         1U-O-Us Insurance Company     125        5Debtor's Credit Card         35         410 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>Now the <TT>BILLS</TT> table is in its original form. Using the simplest formof the <TT>CREATE INDEX</TT> statement did not physically change the way the tablewas stored.</P><P>You may be wondering why database systems even provide indexes if they also enableyou to use the <TT>ORDER BY</TT> clause.</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS ORDER BY ACCOUNT_ID;</B>NAME                         AMOUNT     ACCOUNT_IDPhone Company                125        1Power Company                75         1Software Company             250        1Florida Water Company        20         1Record Club                  25         2Cable TV Company             35         3Debtor's Credit Card         35         4Joe's Car Palace             350        5U-O-Us Insurance Company     125        5S.C. Student Loan            200        610 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>This <TT>SELECT</TT> statement and the <TT>ID_INDEX</TT> on the <TT>BILLS</TT>table generate the same result. The difference is that an <TT>ORDER BY</TT> clausere-sorts and orders the data each time you execute the corresponding SQL statement.When using an index, the database system creates a physical index object (using thetree structure explained earlier) and reuses the same index each time you query thetable.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>When a table is dropped, all	indexes associated with the table are dropped as well. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">Indexing Tips</FONT></H3><P>Listed here are several tips to keep in mind when using indexes:<UL>	<LI>For small tables, using indexes does not result in any performance improvement.	<P>	<LI>Indexes produce the greatest improvement when the columns you have indexed on	contain a wide variety of data or many <TT>NULL</TT> values.	<P>	<LI>Indexes can optimize your queries when those queries are returning a small amount	of data (a good rule of thumb is less than 25 percent of the data). If you are returning	more data most of the time, indexes simply add overhead.	<P>	<LI>Indexes can improve the speed of data retrieval. However, they slow data updates.	Keep this in mind when doing many updates in a row with an index. For very large	updates, you might consider dropping the index before you perform the update. When	the update is complete, simply rebuild your index. On one particular update, we were	able to save the programmers 18 hours by dropping the index and re-creating it after	the data load.	<P>	<LI>Indexes take up space within your database. If you are using a database management	system that enables you to manage the disk space taken up your database, factor in	the size of indexes when planning your database's size.	<P>	<LI>Always index on fields that are used in joins between tables. This technique	can greatly increase the speed of a join.	<P>	<LI>Most database systems do not allow you to create an index on a view. If your	database system allows it, use the technique clause with the <TT>SELECT</TT> statement	that builds the view to order the data within the view. (Unfortunately, many systems	don't enable the <TT>ORDER BY</TT> clause with the <TT>CREATE VIEW</TT> statement	either.)	<P>	<LI>Do not index on fields that are updated or modified regularly. The overhead required	to constantly update the index will offset any performance gain you hope to acquire.	<P>	<LI>Do not store indexes and tables on the same physical drive. Separating these	objects will eliminate drive contention and result in faster queries.</UL><H3><FONT COLOR="#000077">Indexing on More Than One Field</FONT></H3><P>SQL also enables you to index on more than one field. This type of index is acomposite index. The following code illustrates a simple composite index. Note thateven though two fields are being combined, only one physical index is created (called<TT>ID_CMPD_INDEX</TT>).</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE INDEX ID_CMPD_INDEX ON BILLS( ACCOUNT_ID, AMOUNT );</B>Index created.SQL&gt; <B>SELECT * FROM BILLS;</B>NAME                         AMOUNT         ACCOUNT_IDFlorida Water Company            20                  1Power Company                    75                  1Phone Company                   125                  1