ch21.htm

21精通SQL

In the following example you do not have to use double quotation marks because <TT>TODAY</TT>
is not a reserved word. To be sure, check your specific implementation.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate TODAY</B>
  2  <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TODAY
--------
15-MAY-97
SQL&gt;
</FONT></PRE>
<H3><FONT COLOR="#000077">The Use of DISTINCT When Selecting Multiple Columns</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select distinct(city), distinct(zip)</B>
  2  <B>from address_tbl;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">select distinct(city), distinct(zip)
                         *
ERROR at line 1:
ORA-00936: missing expression
SQL&gt;
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A city can have more than one ZIP code. As a rule, you should use the <TT>DISTINCT</TT>
command on only one selected column.
<H3><FONT COLOR="#000077">Dropping an Unqualified Table</FONT></H3>
<P>Whenever dropping a table, <I>always</I> use the owner or schema. You can have
duplicate table names in the database. If you don't use the owner/schema name, then
the wrong table could be dropped.</P>
<P>The <I>risky</I> syntax for dropping a table:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; drop table people_tbl;
</FONT></PRE>
<P>The next statement is much safer because it specifies the owner of the table you
want to drop.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; drop table ron.people_tbl;
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Qualifying the table when dropping
	it is always a safe practice, although sometimes this step may be unnecessary. Never
	issue the <TT>DROP TABLE</TT> command without first verifying the user id by which
	you are connected to the database. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">The Use of Public Synonyms in a Multischema Database</FONT></H3>
<P>Synonyms make life easier for users; however, public synonyms open tables that
you might not want all users to see. Use caution when granting public synonyms especially
in a multischema environment.
<H3><FONT COLOR="#000077">The Dreaded Cartesian Product</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select a.ssn, p.last_n</B>
  2  <B>from address_tbl a,</B>
  3       <B>people_tbl p;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SSN       LAST_NAME
--------- ---------------
303785523 SMITH
313507927 SMITH
490552223 SMITH
312667771 SMITH
420001690 SMITH
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES
303785523 OSBORN
313507927 OSBORN
490552223 OSBORN
312667771 OSBORN
420001690 OSBORN
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES

16 rows selected.
</FONT></PRE>
<P>This error is caused when you do not join the tables in the <TT>WHERE</TT> clause.
Notice how many rows were selected. Both of the preceding tables have 4 rows; therefore,
we wanted 4 rows returned instead of the 16 rows that we received. Without the use
of a join in the <TT>WHERE</TT> clause, each row in the first table is matched up
with each row in the second. To calculate the total number of rows returned, you
would multiple 4 rows by 4 rows, which yields 16. Unfortunately, most of your tables
will contain more than 4 rows of data, with some possibly exceeding thousands or
millions of rows. In these cases don't bother doing the multiplication, for your
query is sure to become a run-away query.
<H3><FONT COLOR="#000077">Failure to Enforce Input Standards</FONT></H3>
<P>Assuring that input standards are adhered to is commonly known as quality assurance
(QA). Without frequent checks on the data entered by data entry clerks, you run a
very high risk of hosting trash in your database. A good way to keep a handle on
quality assurance is to create several QA reports using SQL, run then on a timely
basis, and present their output to the data entry manager for appropriate action
to correct errors or data inconsistencies.
<H3><FONT COLOR="#000077">Failure to Enforce File System Structure Conventions</FONT></H3>
<P>You can waste a lot of time when you work with file systems that are not standardized.
Check your implementation for recommended file system structures.
<H3><FONT COLOR="#000077">Allowing Large Tables to Take Default Storage Parameters</FONT></H3>
<P>Default storage parameters will vary with implementations, but they are usually
rather small. When a large or dynamic table is created and forced to take the default
storage, serious table fragmentation can occur, which can severely hinder database
performance. Good planning before table creation will help to avoid this. The following
example uses Oracle's storage parameter options.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>
  2  <B>(ssn   number(9) not null,</B>
  3  <B>name  varchar2(30) not null)</B>
  4 <B> storage</B>
  5<B>  (initial extent 100M</B>
  6  <B> next extent     20M</B>
  7  <B> minextents 1</B>
  8  <B> maxextents 121</B>
  9  <B> pctincrease 0};</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Placing Objects in the System Tablespace</FONT></H3>
<P>The following statement shows a table being created in the <TT>SYSTEM</TT> tablespace.
Although this statement will not return an error, it is likely to cause future problems.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>
  2  <B>(ssn   number(9) not null,</B>
  3  <B>name  varchar2(30) not null)</B>
  4  <B>tablespace SYSTEM</B>
  5  <B>storage</B>
  6 <B> (initial extent 100M</B>
  7  <B>next extent     20M</B>
  8  <B>minextents 1</B>
  9  <B>maxextents 121</B>
  10 <B>pctincrease 0};</B>
</FONT></PRE>
<P>The next example corrects this so-called problem:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create table test_tbl</B>
  2  <B>(ssn   number(9) not null,</B>
  3  <B>name  varchar2(30) not null)</B>
  4 <B> tablespace linda_ts</B>
  5 <B> (initial extent 100M</B>
  6   <B>next extent     20M</B>
  7 <B> minextents 1</B>
  8  <B>maxextents 121</B>
  9  <B>pctincrease 0};</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In Oracle, the <TT>SYSTEM</TT> tablespace is typically used to store <TT>SYSTEM</TT>
owned objects, such as those composing the data dictionary. If you happen to place
dynamic tables in this tablespace and they grow, you run the risk of corrupting or
at least filling up the free space, which in turn will probably cause the database
to crash. In this event the database may be forced into an unrecoverable state. Always
store application and user tables in separately designated tablespaces.
<H3><FONT COLOR="#000077">Failure to Compress Large Backup Files</FONT></H3>
<P>If you do large exports and do not compress the files, you will probably run out
of disk space to store the files. Always compress the export files. If you are storing
archived log files on hard disk instead of on tape, these files can be and probably
should be compressed to save space.
<H3><FONT COLOR="#000077">Failure to Budget System Resources</FONT></H3>
<P>You should always budget your system resources before you create your database.
The result of not budgeting system resources could be a poorly performing database.
You should always know whether the database is going to be used for transactions,
warehousing, or queries only. The database's function will affect the number and
size of rollback segments. The number of database users will inevitably affect the
sizing of the <TT>USERS</TT> and <TT>TEMP</TT> tablespaces. Do you have enough space
to stripe your larger tables? Tables and indexes should be stored on separate devices
to reduce disk contention. You should keep the redo logs and the data tablespaces
on separate devices to alleviate disk contention. These are just a few of the issues
to address when considering system resources.
<H2><FONT COLOR="#000077">Preventing Problems with Your Data</FONT></H2>
<P>Your data processing center should have a backup system set up. If your database
is small to medium, you can take the extra precaution of using <TT>EXPORT</TT> to
ensure that your data is backed up. You should make a backup of the export file and
keep it in another location for further safety. Remember that these files can be
large and will require a great deal of space.
<H3><FONT COLOR="#000077">Searching for Duplicate Records in Your Database</FONT></H3>
<P>If your database is perfectly planned, you should not have a problem with duplicate
records. You can avoid duplicate records by using constraints, foreign keys, and
unique indexes.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Many different types of errors--literally hundreds--can stand in the way of you
and your data. Luckily, most errors/mistakes are not disasters and are easy to remedy.
However, some errors/mistakes that happen are very serious. You need to be careful
whenever you try to correct an error/mistake, as the error can multiply if you do
not dig out the root of the problem. When you do make mistakes, as you definitely
will, use them as learning experiences.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>We prefer to document everything
	related to database errors, especially uncommon errors that we happen to stumble
	upon. A file of errors is an invaluable <FONT COLOR="#000000">Troubleshooting</FONT>
	reference. 
<HR>
</P>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Day 21 provides you with a sample
	of some of the most common Personal Oracle7 errors. For a complete list of errors
	and suggested resolutions, remember to refer to your database documentation. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q You make it sound as if every error has a remedy, so why worry?</B>
	<P><B>A </B>Yes, most errors/mistakes are easy to remedy; but suppose you drop a
	table in a production environment. You might need hours or days to do a database
	recovery. The database will be done during this time, and your company will be paying
	overtime to several people to complete the fix. The boss will not be happy.</P>
	<P><B>Q Any advice on how to avoid errors/mistakes?</B></P>
	<P><B>A </B>Being human, you will never avoid all errors/mistakes; however, you can
	avoid many of them through training, concentration, self-confidence, good attitude,
	and a stress-free work environment.
</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> A user calls and says, &quot;I can't sign on to the database. But everything
	was working fine yesterday. The error says invalid user/password. Can you help me?&quot;
	What steps should you take?
	<P><B>2. </B>Why should tables have storage clauses and a tablespace destination?
</DL>

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

<DL>
	<DD><B>1. </B>Suppose you are logged on to the database as <TT>SYSTEM</TT>, and you
	wish to drop a table called <TT>HISTORY</TT> in your schema. Your regular user id
	is <TT>JSMITH</TT>. What is the correct syntax to drop this table?
	<P><B>2. </B>Correct the following error:</P>
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select sysdate DATE</B>
  2  <B>from dual;</B></FONT></PRE>
	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
	<PRE><FONT COLOR="#0066FF">select sysdate DATE
               *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected</FONT></PRE>

</BLOCKQUOTE>

<H1><FONT COLOR="#0066FF"></FONT></H1>
<CENTER>
<P>
<HR>
<A HREF="ch20.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch20.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="wk3rev.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk3rev.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>