ch12.htm

Teach yourself Oracle8 in 21 day

	<P>
	<LI>Data distribution for I/O--If your goal is to distribute data based on I/O rates,
	you must know the I/O distribution of the data.
	<P>
	<LI>Other--You might have other goals, such as keeping new information on certain
	disk volumes and distributing older data elsewhere.
</UL>

<P>Say you want to partition a table that keeps track of warehouse information around
the country. There are 100 warehouses, with 25 in each of four districts. Information
about these warehouses is retrieved for each district about 50% of the time and for
the whole country about 50% of the time. This would be an ideal application for range
partitioning. Let's partition this table by region.</P>
<P>The table should be partitioned on the column named <TT>region</TT>; because each
region has a region number from 1 to 4, <TT>region</TT> is the perfect column to
partition on. The table creation looks something like this:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE warehouses
(region INTEGER,
column datatype,
.
.
.
column datatype)
PARTITION BY RANGE (region)
(PARTITION VALUES LESS THAN 2 TABLESPACE ts_r1
PARTITION VALUES LESS THAN 3 TABLESPACE ts_r2 )
PARTITION VALUES LESS THAN 4 TABLESPACE ts_r3 )
PARTITION VALUES LESS THAN MAXVALUE TABLESPACE ts_r4 )
</FONT></PRE>
<P>This table will then partition the data so that data for each region will be on
a separate tablespace. It is possible to index each partition separately or all of
them together. For queries to a specific region, performance will be improved.
<H3><FONT COLOR="#000077"><B>Object Tables</B></FONT></H3>
<P>As mentioned previously, Oracle8 is an ORDBMS, or object-relational database management
system. The Oracle server has additional object extensions that allow object models
to be used.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>With object tables, instead
of using data types, you use <I>object types</I>. These are similar to a data types
in the way they are used but different in function. An object type consists of attributes
and methods. An object attribute is the structure of the object type. Methods are
functions or procedures that are used by that object type. For example, if I want
to create an object used to categorize dogs by their owner, I could create an object
type using the following SQL statement:</P>
<PRE><FONT COLOR="#0066FF">CREATE TYPE dog_info AS OBJECT
(
dog_breed varchar2(40),
dog_id NUMBER,
MEMBER FUNCTION get_id RETURN VARCHAR2
);
</FONT></PRE>
<P>Here the attributes are <TT>dog_breed</TT> and <TT>dog_id</TT>, whereas the method
is <TT>get_id</TT>. This is how an object is defined. Now that this object type is
defined, it can be used in a table-creation statement. Any column in a table can
now be an object type.</P>
<P>Now that you have created the type <TT>dog_info</TT>, you can create a table with
one of the columns of type <TT>dog_info</TT>. This will use the type definition to
define the column and this column will have both the attributes and the member function.</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE dogs (
Dog_name VARCHAR2(40),
dog_owner VARCHAR2(40),
dog_id dog_info );
</FONT></PRE>
<P>This table can be populated by using a statement such as:</P>
<PRE><FONT COLOR="#0066FF">INSERT INTO dogs VALUES (
`Pierce',
`Whalen',
dog_info(`Border Collie','U1234') );
</FONT></PRE>
<P>Now you can retrieve the dog's ID number via the following SQL statement:</P>
<PRE><FONT COLOR="#0066FF">SELECT dog_id.get_id
FROM dogs
WHERE dog_name = `Pierce' AND dog_owner = `Whalen';
</FONT></PRE>
<P>This allows me to track multiple dogs in one table. This data can be retrieved
easily in a straightforward manner. Of course, if you use objects, you will probably
use structures that are much more complex.
<H3><FONT COLOR="#000077"><B>Index-Only Tables</B></FONT></H3>
<P>Index-only tables are a new feature in Oracle8. The index-only table allows the
table data to be stored within the leaf block of the index. The advantage of this
is that when the leaf block of the index is reached, you have immediate access to
the data rather than a reference. The index-only table is covered tomorrow.
<H3><FONT COLOR="#000077"><B>Nested Tables</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>nested table</I>, new
with Oracle8, is a table that appears as a column in another table. This can be useful
for the creation of information that is a subset of other information. A nested table
can be created with this syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE TYPE dog_info_table (
Dog_id NUMBER,
Dog_breed VARCHAR2(40),
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">
Dog_birthdate DATE);
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">
CREATE TABLE dog_owners (
Name VARCHAR2(40),
Address VARCHAR2(60),
Dog_name VARCHAR2(40),
Dog_info dog_info_table);
</FONT></PRE>
<P>Each item <TT>dog_owners.dog_names</TT> is a nested table. With this, each record
in the <TT>dog_names</TT> table relates to a dog that is owned by the <TT>dog_owner</TT>
referenced here. Data can be inserted into the table in the following manner:</P>
<PRE><FONT COLOR="#0066FF">INSERT INTO dog_owners VALUES (
`Whalen',
`12345 Main St., Houston, TX',
`Pierce'
Dog_info(`U1234', `Border Collie', '18-Nov-1988') );
</FONT></PRE>
<P>An index can be created with the syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE INDEX dogs_index ON dogs_owners.dog_names(dog_id);
</FONT></PRE>
<H2><FONT COLOR="#000077"><B>Views</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>view</I> is a window
into a table. Although a view is treated like a table in that you can select columns
from it, a view is not a table; it is a logical structure that looks like a table
but is actually a superset or subset of a table. A view derives its data from other
tables, which are referred to as <I>base tables</I>. These base tables can be tables
or even other views. Views are used to simplify the access of certain data and to
hide certain pieces of data. Views are very powerful and can be quite useful in many
situations.</P>
<P>By creating views, you can protect sensitive data within a table. For example,
an employee record might contain sensitive information such as salary and job grade.
If you create a view on that table that does not include those columns and allow
users to access only the view, you can protect the sensitive information.
<H3><FONT COLOR="#000077"><B>Managing Views</B></FONT></H3>
<P>To create views with Enterprise Manager or Storage Manager, right-click the Views
icon and select the Create option. This invokes the Create View screen. From here,
you must build a query that will be used to create a view, as shown in Figure 12.15.</P>
<P><A NAME="15"></A><A HREF="15.htm"><B>Figure 12.15.</B></A></P>
<P><I>The Create View screen.</I></P>
<P>You create a view by entering a view name and schema at the top of the screen,
then typing a query into the Query Text area. As you can see, the Show SQL option
has been enabled. Similarly, a view can be created with the <TT>CREATE VIEW</TT>
command like so:</P>
<PRE><FONT COLOR="#0066FF">CREATE VIEW executives AS
SELECT employee_name, employee_rank, employee_salary
FROM employee
WHERE employee_rank &gt; 99;
</FONT></PRE>
<P>Assume you have an employee table where each employee has a rank based on job
grade. Executive job grades start at 100. This view selects employee information
for only those employees who have a 100 or greater job grade.</P>
<P>Views can be built from simple queries that select a subset of a table or they
can be built from joins from multiple tables. After a view is created, it cannot
be modified but it can be replaced. If a view already exists, you can use the <TT>CREATE
OR REPLACE VIEW</TT> syntax. This either creates or replaces a view, depending on
the current status.
<H2><FONT COLOR="#000077"><B>Synonyms</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Synonyms</I> are simply
database objects that point to another object in the system; they are aliases for
tables, views, sequences, or program units. Synonyms are typically used to hide certain
details from the end user, such as object ownership or location of the distributed
object. If you use synonyms, you can ensure that only the information you want seen
will be seen.</P>
<P>Synonyms come in two forms: public and private. A public synonym is owned by the
<TT>PUBLIC</TT> schema and is available to every user in the database. A private
synonym is contained in the schema of the user who created it, and that user has
control over who has access to it.</P>
<P>Synonyms can also be used to simplify the access to certain data in a distributed
system. Instead of attaching system names to the object name, you can use a synonym.
For example, the following SQL query</P>
<PRE><FONT COLOR="#0066FF">SELECT * FROM etw.dogs;
</FONT></PRE>
<P>can be simplified to</P>
<PRE><FONT COLOR="#0066FF">SELECT * FROM dogs;
</FONT></PRE>
<P>When you create a synonym the access path is simplified; because <TT>etw.dogs</TT>
is defined as <TT>dogs</TT>, the user need not know the system name. To create this
synonym via the graphical tools, right-click the Synonym icon and use the Create
Synonym tool, shown in Figure 12.16.</P>
<P><A NAME="16"></A><A HREF="16.htm"><B>Figure 12.16.</B></A></P>
<P><I>The Create Synonym tool.</I></P>
<P>Here I have selected <TT>PUBLIC</TT> as the schema in which I want this synonym
to exist. I have also configured this synonym to be an alias for the <TT>dogs</TT>
table I created earlier. I used the Show SQL option. The SQL syntax used here (which
can also be used with Server Manager) is</P>
<PRE><FONT COLOR="#0066FF">CREATE PUBLIC SYNONYM DOGS for &quot;ETW&quot;.&quot;DOGS&quot;;
</FONT></PRE>
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Today's lesson introduced you to the Oracle schema objects. These consist of the
logical database structures used to organize data within the database itself. The
schema objects consist of tables, indexes, clusters, views, and so on.</P>
<P>You were introduced to the Oracle table; first you learned about the traditional
Oracle table structure, then about the new features of Oracle8. These new schema
objects consist of objects, partitioned tables, object tables, and nested tables.</P>
<P>You also learned about Oracle views. These views are used to simplify data access
or to hide details from the user. With a view, you can allow users to see certain
employee information while hiding more sensitive information such as salaries.</P>
<P>You were also introduced to the Oracle synonym, which is simply a pointer to another
object in the database. Synonyms can hide certain details from the user just as views
can hide certain columns from the user.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>On Day 13, &quot;Using Indexes and Sequences,&quot; you will learn about indexes,
one of the most important performance features in the Oracle RDBMS. Indexes are used
to speed up access to randomly selected data. You will learn how indexes work, how
to create and modify indexes, and how to properly use indexes for enhanced performance.
When used correctly, indexes are very powerful tools; however, they are easy to misuse.
When indexes are misused, their potential is not fully realized.</P>
<P>On Day 14, you will finish learning about the basic building blocks in the Oracle
schema. The main schema objects consist of tables, indexes, and clusters. By the
time you finish Day 14's lesson, you will know about all of these.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What are tables used for?</B>
	<P><B>A</B> A table is the Oracle object that actually holds the data that is entered
	into the database. A table consists of rows of data that are defined by table columns.</P>
	<P><B>Q What is a partitioned table?</B></P>
	<P><B>A</B> A partitioned table uses the new Oracle feature, range partitioning.
	Data is stored in a location based on a range of data you have defined. This range
	can be numeric or a data format.</P>
	<P><B>Q What can nested tables be used for?</B></P>
	<P><B>A</B> Nested tables can be used for many things. They are particularly useful
	for holding information of the same type, thus simplifying the access of that data.
	For example, if you were storing spatial data, you could use a nested table to hold
	compass and altitude information. This way, you could use a single column, <TT>location</TT>,
	in the main table.</P>
	<P><B>Q Why would you use a view?</B></P>
	<P><B>A</B> Views can be useful to hide certain information from the end user. They
	can be used for security purposes (for example, to hide salary information from users)
	or to simplify access to a distributed table or complex join.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Answers to quiz questions can be found in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What is a table column?
	<P><B>2. </B>What is a row in a table?</P>
	<P><B>3.</B> What two components are row pieces made of?</P>
	<P><B>4.</B> What makes up an object?</P>
	<P><B>5.</B> What is a nested table?</P>
	<P><B>6. </B>What is a partitioned table?</P>
	<P><B>7. </B>How are tables partitioned?</P>
	<P><B>8. </B>What is a view?</P>
	<P><B>9. </B>What is the <TT>STORAGE</TT> clause used for?</P>
	<P><B>10. </B>What is a synonym?
</DL>

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

<DL>
	<DD><B>1. </B>Create a simple table.
	<P><B>2.</B> Create a table with a nested column.</P>
	<P><B>3.</B> Create a view on that table.</P>
	<P><B>4.</B> Create a synonym to that view.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../ch11/ch11.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch13/ch13.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>