📄 ch12.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 12 -- Working with Tables, Views, and Synonyms</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><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>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 12 -<BR>
Working with Tables, Views, and Synonyms</FONT></H1>
</CENTER>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Today you will begin to learn
about the Oracle schema objects. The <I>schema objects</I> are the collection of
objects associated with the database. They are an abstraction or logical structure
that refers to database objects or structures. Schema objects consist of such things
as clusters, indexes, packages, sequences, stored procedures, synonyms, tables, views,
and so on.</P>
<P>Only tables, views, and synonyms are covered today. Tomorrow you will learn about
indexes, and on Day 14, "Using Oracle Clusters, Stored Procedures, and Database
Links," clusters will be presented. All these objects make up the Oracle schema.
<H2><FONT COLOR="#000077"><B>Tables</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>A <I>table</I>, which is
the most visible object in the Oracle RDBMS, is a structure that is used by Oracle
to store data. Logically the table structure is referenced in terms of rows and columns.
<I>Column</I> refers to the component of the record that is placed in the database.
When you create an empty table, you define the columns. A <I>row</I> can also be
referred to as a <I>record</I>. This is an individual piece of data that contains
information that corresponds to the columns in the table. It is not necessary that
each row have an entry for every column, but this is typically the case. An example
of a table is shown in Figure 12.1.</P>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 12.1.</B></A></P>
<P><I>Example of a table.</I></P>
<P>Because I have several dogs and participate in a number of dog-related activities,
I like to use dog-related data in my examples. As you can see, the columns consist
of particular data types and each row contains data that reflects those columns.
<H3><FONT COLOR="#000077"><B>How Tables Work</B></FONT></H3>
<P>A table is created in a table segment. The table segment in turn consists of one
or more extents. If the table grows to fill the current extents, a new extent is
created for that table. These extents grow in a manner specified by the <TT>STORAGE</TT>
clause used to create the table.</P>
<P>If a <TT>STORAGE</TT> clause is not included at table creation, the default <TT>STORAGE</TT>
clause defined on the tablespace is used. If no default <TT>STORAGE</TT> clause is
defined on the tablespace, system defaults are used.</P>
<P>The data from the table is stored in database blocks. The number of rows put in
one data block depends on the size of the row and the storage parameters. Depending
on the type of data and how it will be accessed, you might want to consider how it
is stored.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Different database types might benefit
from different storage parameters. The goal of storage management is to exploit the
data cache as much as possible. Ideally, all data would be in cache when you need
it, but this is not usually possible. To maximize the cache-hit rate, employ the
table-storage parameters and database-creation options.</P>
<P><BR>
For example, if you know your data will be sequentially accessed most of the time,
you can use a large block size and storage parameters that pack as many rows as possible
into the data blocks. The advantage here is that when the first row in the block
is requested, the entire block is loaded into memory. When subsequent rows are requested,
they are already loaded into memory.</P>
<P><BR>
The opposite of this is a table whose data is randomly accessed with both reads and
inserts. If you know you won't typically be requesting adjacent rows, use a small
block size and pack the data less. Because there will be inserts, reserving some
free space in the block will allow space for those inserts without causing chained
rows (a <I>chained row </I>is a row that spans more than one block).
<HR>
</BLOCKQUOTE>
<P>Table creation should be carefully planned. You should know what the data looks
like as well as how it will be accessed. This information should help you in the
database-creation phase.
<H3><FONT COLOR="#000077"><B>Table Columns</B></FONT></H3>
<P>Table columns are the individual components of a row. They can be of fixed or
variable size, depending on the data type of that column. A row of a database record
consists of one or more columns that hold the data. Each column is assigned a data
type.
<H3><FONT COLOR="#000077"><B>Table Rows</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Although it is desirable
to store one or more rows in a data block, this is not always possible. If the block
has inadequate room, a row is split and referred to as a <I>chained row</I>. Chained
rows consist of multiple <I>row pieces</I>. Even if the entire row is stored in the
same block, it is sometimes referred to as a row piece.</P>
<P>The row piece is effectively a row of data. Each row piece consists of the row
header and the row data. The row header stores information about the row and contains
the following information:
<UL>
<LI>Information about the row piece
<P>
<LI>Information about chaining (if applicable)
<P>
<LI>Cluster keys (if applicable)
<P>
<LI>Column definitions
</UL>
<P>This information is necessary for Oracle to properly process this row. For a typical
nonclustered row, the row header is about three bytes in length.
<H3><FONT COLOR="#000077"><B>Oracle Data Types</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Oracle8 has evolved from
an RDBMS to an ORDBMS (object-relational database management system). With the new
object extensions come several new Oracle data types. The traditional data types
to which you are accustomed are now referred to as <I>scalar</I> data types. Other
new data types include <TT>VARRAY</TT>s, <TT>REF</TT>s, and <TT>LOB</TT>s, as described
later today.
<H4><FONT COLOR="#000077"><B>Scalar Data Types</B></FONT></H4>
<P>The following scalar data types are available in Oracle:
<UL>
<LI><TT>CHAR</TT>--Fixed-length character. This can be any length between 1 and 255
bytes.
<P>
<LI><TT>DATE</TT>--Stores <TT>Year</TT>, <TT>Month</TT>, <TT>Day</TT>, <TT>Hour</TT>,
<TT>Minute</TT>, and <TT>Second</TT> values.
<P>
<LI><TT>LONG</TT>--Variable-length character data up to 2GB in size.
<P>
<LI><TT>LONG RAW</TT>--Similar to long except it is not converted via Oracle utilities
the way <TT>LONG</TT> data is.
<P>
<LI><TT>MLSLABEL</TT>--For use with Trusted Oracle, this data type stores the binary
format of the system label.
<P>
<LI><TT>NUMBER</TT>--Stores fixed and floating-point numbers. Its definition includes
the precision and scale.
<P>
<LI><TT>ROWID</TT>--Stores a triplet that consists of the data block, the row, and
the datafile. Every row in a nonclustered table has a <TT>ROWID</TT>.
<P>
<LI><TT>VARCHAR</TT>--A variable-length character data type. The <TT>VARCHAR</TT>
data type is not guaranteed to remain the same, so <TT>VARCHAR2</TT> is recommended.
Today these two data types are identical.
<P>
<LI><TT>VARCHAR</TT>--A variable-length character data type that stores 1-2,000 characters.
Anything larger requires <TT>LONG</TT> or <TT>LONG RAW</TT>.
</UL>
<P>The data type you choose for each column is defined at table-creation time. Other
data types, such as ANSI data types, are available but are simply converted to Oracle
data types. These are illustrated in Table 12.1.
<H4><FONT COLOR="#000077"><B>Table 12.1. Oracle data types.</B></FONT></H4>
<P>
<TABLE BORDER="1">
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><B>Non-Oracle data type</B></TD>
<TD ALIGN="LEFT"><B>Converted to this Oracle data type</B></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>DECIMAL</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>INTEGER</TT>, <TT>INT</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER(38)</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>SMALLINT</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER(38)</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>FLOAT</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>REAL</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER</TT></TD>
</TR>
<TR ALIGN="LEFT" rowspan="1">
<TD ALIGN="LEFT"><TT>DOUBLE</TT></TD>
<TD ALIGN="LEFT"><TT>NUMBER</TT></TD>
</TR>
</TABLE>
</P>
<P>For more specifics on these data types, see the Oracle documentation.
<H4><FONT COLOR="#000077">VARRAY<B>s</B></FONT></H4>
<P>The <TT>VARRAY</TT> data type consists of a set of built-in types or objects referred
to as <I>elements</I> of the array. Each element has an index that corresponds to
the position in the array. The number of elements in an array varies, which is why
it is referred to as a <TT>VARRAY</TT>, or variable array. To create an array, you
must declare its maximum size because it does vary. To create an array type, use
this syntax:</P>
<PRE><FONT COLOR="#0066FF">CREATE TYPE cost AS VARRAY(20) OF NUMBER(12,2)
</FONT></PRE>
<P>This will create an array of 20 elements, each being a floating-point number with
a precision of 2. This essentially allows you to create an array of values that are
actually stored in one column, which can be useful in a variety of situations.
<H4><FONT COLOR="#000077">REF<B>s</B></FONT></H4>
<P><TT>REF</TT>s are also new in Oracle8. Think of the <TT>REF</TT> data type as
a pointer to an object. A <TT>REF</TT> can also be used in a manner similar to a
foreign key in an RDBMS. A <TT>REF</TT> is used primarily to store an object identifier,
and to allow you to select that object.</P>
<PRE><FONT COLOR="#0066FF">SELECT d.name
FROM dogs d
WHERE d.owner_id = 1;
</FONT></PRE>
<P>In this example, <TT>d</TT> acts as a reference to <TT>dogs</TT> in order to allow
easier access to that table.
<H4><FONT COLOR="#000077">LOB<B>s</B></FONT></H4>
<P><TT>LOB</TT> refers to large schema objects. Oracle recognizes several different
types of <TT>LOB</TT>s, includ-ing the following:
<UL>
<LI><TT>BLOB</TT>--An unstructured binary data field. A <TT>BLOB</TT> might be something
like video or picture information.
<P>
<LI><TT>CLOB</TT>--A large field that consists of standard, single-byte characters.
This might be something like a document.
<P>
<LI><TT>NCLOB</TT>--Similar to the <TT>CLOB</TT> type, but consists of single or
multibyte characters from the National Character Set.
<P>
<LI><TT>BFILE</TT>--Essentially a pointer to an externally stored file.
</UL>
<P>All these types reference large pieces of data, such as video or text. Because
they are large by definition, Oracle does not store the data inline with the other
columns of the table. Instead, a <TT>LOB</TT> pointer is stored there, which points
to the location of the <TT>LOB</TT> data. This makes scanning of that table much
faster, thus improving access to the data.
<H3><FONT COLOR="#000077"><B>Creating Tables</B></FONT></H3>
<P>Tables can be defined and created with several different Oracle tools, including
Enterprise Manager, Schema Manager, and the <TT>CREATE TABLE</TT> command. This command
has many more features than the graphical utilities. If you are creating a simple,
straightforward table, the graphical utilities are good and easy to use; If you are
performing more complex tasks, you should use the <TT>CREATE TABLE</TT> command.
Today you will see all three ways of creating a table.
<H4><FONT COLOR="#000077"><B>Managing Tables with Enterprise Manager</B></FONT></H4>
<P>It is possible to manage tables with Enterprise Manager, but I prefer to use Schema
Manager or Server Manager for schema operations. If you prefer Enterprise Manager,
you should use that tool.</P>
<P>With Enterprise Manager, you can drill down into the database. If you expand the
Schema Objects icon, you will see a list of the schema objects that can be managed
via Enterprise Manager (see Figure 12.2).</P>
<P>A number of schema objects can be managed here, including
<UL>
<LI>Clusters
<P>
<LI>Database links
<P>
<LI>Functions
<P>
<LI>Indexes
<P>
<LI>Package bodies
<P>
<LI>Packages
<P>
<LI>Procedures
<P>
<LI>Refresh groups
<P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -