ch10.htm

Learn SQL in an easy way...

<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 all
other views that reference that view are now invalid. Some database systems even
drop all views that used the view you dropped. Using Personal Oracle7, if you drop
the 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 on
the disk is to use an index. In addition, indexes can also reorder the data stored
on 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, often
called the Sequential Access Method, requires SQL to go through each record looking
for a match. This search method is inefficient, but it is the only way for SQL to
locate the correct record. Think back to the days when libraries had massive card
catalog filing systems. Suppose the librarian removed the alphabetical index cards,
tossed the cards into the air, then placed them back into the filing cabinets. When
you wanted to look up this book's shelf location, you would probably start at the
very beginning, then go through one card at a time until you found the information
you wanted. (Chances are, you would stop searching as soon as you found any book
on this topic!)</P>
<P>Now suppose the librarian sorted the book titles alphabetically. You could quickly
access this book's information by using your knowledge of the alphabet to move through
the catalog.</P>
<P>Imagine the flexibility if the librarian was diligent enough to not only sort
the books by title but also create another catalog sorted by author's name and another
sorted by topic. This process would provide you, the library user, with a great deal
of flexibility in retrieving information. Also, you would be able to retrieve your
information 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. SQL
uses a treelike structure to store and retrieve the index's data. Pointers to a group
of data are stored at the top of the tree. These groups are called nodes. Each node
contains pointers to other nodes. The nodes pointing to the left contain values that
are less than its parent node. The pointers to the right point to values greater
than the parent node.</P>
<P>The database system starts its search at the top node and simply follows the pointers
until 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 in
tables 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 vary
widely among database systems. For instance, the <TT>CREATE INDEX</TT> statement
under Oracle7 looks like this:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE INDEX [schema.]index
ON { [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_name
on [[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_name
ON table_name (column_name [ASC | DESC],
               column_name [ASC | DESC]...)
</FONT></PRE>
<P>Notice that all of these implementations have several things in common, starting
with 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 discussed
later. Oracle and Informix allow you to designate whether the column name should
be 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's
documentation 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_ID
Phone Company                125        1
Power Company                75         1
Record Club                  25         2
Software Company             250        1
Cable TV Company             35         3
Joe's Car Palace             350        5
S.C. Student Loan            200        6
Florida Water Company        20         1
U-O-Us Insurance Company     125        5
Debtor's Credit Card         35         4

10 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_ID
Phone Company                125        1
Power Company                75         1
Software Company             250        1
Florida Water Company        20         1
Record Club                  25         2
Cable TV Company             35         3
Debtor's Credit Card         35         4
Joe's Car Palace             350        5
U-O-Us Insurance Company     125        5
S.C. Student Loan            200        6

10 rows selected.
</FONT></PRE>
<P>The <TT>BILLS</TT> table is sorted by the <TT>ACCOUNT_ID</TT> field until the
index is dropped using the <TT>DROP INDEX</TT> statement. As usual, the <TT>DROP
INDEX</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_ID
Phone Company                125        1
Power Company                75         1
Record Club                  25         2
Software Company             250        1
Cable TV Company             35         3
Joe's Car Palace             350        5
S.C. Student Loan            200        6
Florida Water Company        20         1
U-O-Us Insurance Company     125        5
Debtor's Credit Card         35         4

10 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now the <TT>BILLS</TT> table is in its original form. Using the simplest form
of the <TT>CREATE INDEX</TT> statement did not physically change the way the table
was stored.</P>
<P>You may be wondering why database systems even provide indexes if they also enable
you 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_ID
Phone Company                125        1
Power Company                75         1
Software Company             250        1
Florida Water Company        20         1
Record Club                  25         2
Cable TV Company             35         3
Debtor's Credit Card         35         4
Joe's Car Palace             350        5
U-O-Us Insurance Company     125        5
S.C. Student Loan            200        6

10 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> clause
re-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 the
tree structure explained earlier) and reuses the same index each time you query the
table.


<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 a
composite index. The following code illustrates a simple composite index. Note that
even 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_ID
Florida Water Company            20                  1
Power Company                    75                  1
Phone Company                   125                  1