ch12.htm

Teach yourself Oracle8 in 21 day

	<LI>Sequences
	<P>
	<LI>Snapshot logs
	<P>
	<LI>Snapshots
	<P>
	<LI>Synonyms
	<P>
	<LI>Tables
	<P>
	<LI>Triggers
	<P>
	<LI>Views
</UL>

<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 12.2.</B></A></P>
<P><I>Enterprise Manager.</I></P>
<P>This shows only the <TT>SYS</TT> schema because I am logged into Enterprise Manager
as <TT>SYS</TT>. The <TT>SYS</TT> schema objects are created by default when the
database is created. If you expand the SYS icon (by clicking it), you will see a
list of icons that represent the tables that currently exist in your system (see
Figure 12.3).</P>
<P>The tables that you see in the <TT>SYS</TT> schema are the internal tables needed
for the operation of Oracle. Managing and creating tables in Enterprise Manager is
similar to the same operation under Schema Manager, as shown next.</P>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 12.3.</B></A></P>
<P><I>Icons that represent the tables currently in your system.</I></P>
<P>
<H4><FONT COLOR="#000077"><BR>
<B>Managing Tables with Schema Manager</B></FONT></H4>
<P>Schema Manager is a nice tool for managing schema objects. When you invoke Schema
Manager, you immediately see the list of schema objects similar to the one that you
saw with Enterprise Manager.</P>
<P>If you expand the Tables icon, you will see a list of user schemas. When you expand
the Tables icon with Schema Manager, you will see a list of the defined tables on
the right side of the screen. This list contains the schema, the table name, and
the name of the tablespace in which these tables exist (see Figure 12.4).</P>
<P>To create a new table with Schema Manager, right-click the Tables icon and select
Create. The New Table dialog asks whether you want to use the Table wizard or create
the table manually (see Figure 12.5).</P>
<P>Here I have selected the Use Table Wizard radio button. This wizard contains seven
pages that must be completed. The first page asks for the table name, schema, and
tablespace name. I have filled in these values appropriately, as shown in Figure
12.6.</P>
<P>When you finish entering values for these parameters, click the Next button to
reach the next screen. Here you must assign a name and data type to each of the columns
in the table. Do this by filling in the column name, column data type, and column
size for each column, as shown in Figure 12.7.</P>
<P><A NAME="04"></A><A HREF="04.htm"><B>Figure 12.4.</B></A></P>
<P><I>Schema Manager showing tables.</I></P>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 12.5.</B></A></P>
<P><I>The New Table dialog.</I></P>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 12.6.</B></A></P>
<P><I>Screen one of the Create Table wizard.</I></P>
<P><A NAME="07"></A><A HREF="07.htm"><B>Figure 12.7.</B></A></P>
<P><I>Screen two of the Create Table wizard.</I></P>
<P>After you fill out the column information, click Insert and fill out the same
information for all the columns in the table. After you fill out the information
for all the columns, click the Next button to move to the third screen of the Table
Creation wizard. From this screen, shown in Figure 12.8, you can set up the primary
key definitions. If you desire a primary key, click the Yes I Want to Create a Primary
Key button, then click the columns you want the key to be on. The order that you
click the columns is the order in which the primary key is defined. Click Next to
get to the next screen.</P>
<P><A NAME="08"></A><A HREF="08.htm"><B>Figure 12.8.</B></A></P>
<P><I>Screen three of the Create Table wizard.</I></P>
<P>The next screen, shown in Figure 12.9, takes you through the process of defining
null and unique constraints. These constraints will be added to the table as configured
here. Go through this screen for each column to which you want to apply null and
unique constraints. Click Next when you finish.</P>
<P>The fifth screen, shown in Figure 12.10, is used to set up foreign key definitions.
This will define constraints on your table. If defined here, an element in your table
must reside in the table on which the foreign key constraint is defined. Click Next
to move on.</P>
<P><A NAME="09"></A><A HREF="09.htm"><B>Figure 12.9.</B></A></P>
<P><I>Screen four of the Create Table wizard.</I></P>
<P><A NAME="10"></A><A HREF="10.htm"><B>Figure 12.10.</B></A></P>
<P><I>Screen five of the Create Table wizard.</I></P>
<P>The sixth screen of the Create Table wizard, shown in Figure 12.11, is used to
define any check conditions on any of your columns. Simply select the column and
enter the check condition to which the column must adhere. Click Next to move to
the next screen.</P>
<P><A NAME="11"></A><A HREF="11.htm"><B>Figure 12.11.</B></A></P>
<P><I>Screen six of the Create Table wizard.</I></P>
<P>The final screen of the Create Table wizard, shown in Figure 12.12, reviews the
columns, constraints, check conditions, and so on that you have defined for this
table. After you complete the review screen, click the Finish button and the table
will be created for you.</P>
<P><A NAME="12"></A><A HREF="12.htm"><B>Figure 12.12.</B></A></P>
<P><I>Screen seven of the Create Table wizard.</I></P>
<P>Earlier you were given the option of using the Table Creation wizard or manually
creating the table. If you had chosen the manual path, you would see the Create Table
screen (shown in Figure 12.13). From here it is a simple matter of entering the schema
name, the table name, and various column definitions. The Create Table screen is
easy to use but somewhat limited. For more control over the table-creation process,
consider using the <TT>CREATE TABLE</TT> command.</P>
<P><A NAME="13"></A><A HREF="13.htm"><B>Figure 12.13.</B></A></P>
<P><I>The Create Table screen.</I></P>
<P>After the table has been created, you have several options. If you right-click
the table's icon from Schema Manager, you can grant privileges, create synonyms,
or index the table, as shown in Figure 12.14.</P>
<P><A NAME="14"></A><A HREF="14.htm"><B>Figure 12.14.</B></A></P>
<P><I>Modify table attributes.</I></P>
<H4><FONT COLOR="#000077"><BR>
<B>Managing Tables with the </B>CREATE TABLE<B> and </B>ALTER TABLE<B> Commands</B></FONT></H4>
<P>The <TT>CREATE TABLE</TT> command can be used to create new tables, the <TT>ALTER
TABLE</TT> command is used to change parameters on an already existing table, and
the <TT>DROP TABLE</TT> command is used to delete an existing table. The <TT>CREATE
TABLE</TT> and <TT>ALTER TABLE</TT> commands are very similar.</P>
<P>The <TT>CREATE TABLE</TT> command has many options and parameters, which are described
in the Oracle server SQL reference manual. I won't show the syntax here, but I would
like to cover some of the key parameters that are available in the <TT>CREATE TABLE</TT>
and <TT>ALTER TABLE</TT> commands.</P>
<P><FONT COLOR="#000077"><B>The <TT>STORAGE</TT> Clause</B></FONT></P>
<P>The primary advantage of using the <TT>CREATE TABLE</TT> and <TT>ALTER TABLE</TT>
commands is your ability to include the <TT>STORAGE</TT> clause. As you saw on Day
7, &quot;Administering Tablespaces,&quot; the <TT>STORAGE</TT> clause can be used
to define how your space is created and grows. If you recall, Day 7 introduced you
to the <TT>DEFAULT STORAGE</TT> clause; here it is the <TT>STORAGE</TT> clause.</P>
<P>The <TT>DEFAULT STORAGE</TT> clause is used to define the storage parameters on
a tablespace. These <TT>DEFAULT STORAGE</TT> parameters will be used for any table
created within that tablespace that does not have a <TT>STORAGE</TT> clause defined
on it. A <TT>STORAGE</TT> clause will override the <TT>DEFAULT STORAGE</TT> definitions.
The <TT>STORAGE</TT> clause always takes priority over the <TT>DEFAULT STORAGE</TT>
clause.</P>
<P>The <TT>STORAGE</TT> clause is very important because it is used to specify the
initial size and characteristics of the tablespace as well as the future growth of
that tablespace. The <TT>STORAGE</TT> clause has the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">STORAGE
(
[ INITIAL number K or M ]
[ NEXT number K or M ]
[ MINEXTENTS number ]
[ MAXEXTENTS number or MAXEXTENTS UNLIMITED ]
[ PCTINCREASE number ]
[ FREELISTS number ]
[ FREELIST GROUPS number ]
[ OPTIMAL [ number K or M ] or [ NULL ] ]
)
</FONT></PRE>
<P>The parameters used in the <TT>STORAGE</TT> clause are defined as follows:

<UL>
	<LI><TT>INITIAL </TT>number<TT> K or M</TT>--This parameter specifies the initial
	size of the extents. These extents are created when the schema object is created.
	This parameter specifies the size to be number<TT> K</TT> (kilobytes) or <TT>M</TT>
	(megabytes). The default is about five data blocks. The size is rounded up to the
	nearest multiple of five blocks.
	<P>
	<LI><TT>NEXT</TT>number<TT> K or M</TT>--The <TT>NEXT</TT> parameter specifies the
	size of subsequent extents to be number<TT> K</TT> (kilobytes) or <TT>M</TT> (megabytes).
	This number is also rounded up to the nearest multiple of five data blocks, and defaults
	to five data blocks.
	<P>
	<LI><TT>MINEXTENTS</TT>number--This specifies the minimum number of extents that
	are created when the schema object is created. Each of these extents will be the
	size of the <TT>INITIAL</TT> extent and Oracle will use <TT>NEXT</TT> and <TT>PCTINCREASE</TT>
	to calculate the size of subsequent extents. The default value is <TT>1</TT> except
	for rollback segments, where the default is <TT>2</TT>.
	<P>
	<LI><TT>MAXEXTENTS</TT>number--This parameter specifies the maximum number of extents
	that can be created for a schema object. This includes the first extent.
	<P>
	<LI><TT>MAXEXTENTS UNLIMITE</TT>--This parameter specifies that the maximum number
	of extents that can be created for a schema object is unlimited. Oracle does not
	recommend that you use this option with any schema objects except for rollback segments.
	<P>
	<LI><TT>PCTINCREASE</TT>number--This parameter specifies the size of extents after
	the second. The initial extents are sized via the <TT>INITIAL</TT> parameter. The
	next extent is sized via the <TT>NEXT</TT> parameter. If nonzero, all subsequent
	extents are sized to be <TT>NEXT</TT> multiplied by the <TT>PCTINCREASE </TT>number
	parameter. This number is the percentage, therefore 40 is 40% larger, and so on.
	A value of 0 specifies that all subsequent extents are the same size as specified
	in the <TT>NEXT</TT> parameter. The default value is <TT>50</TT>, except for rollback
	segments which can only have a <TT>PCTINCREASE</TT> of <TT>0</TT>.
	<P>
	<LI><TT>FREELISTS</TT>number--The <TT>FREELISTS</TT> parameter does not apply to
	tablespaces. This parameter specifies the number of groups of freelists for each
	of the freelist groups for tables, indexes, partitions, and clusters.
	<P>
	<LI><TT>FREELIST GROUPS</TT>number--The <TT>FREELIST GROUPS</TT> parameter does not
	apply to tablespaces. This parameter specifies the number of groups of freelists.
	<P>
	<LI><TT>OPTIMAL </TT>number <TT>K or M]</TT>--This parameter applies only to rollback
	segments; it specifies the ideal size of the rollback segment. Because the rollback
	segment grows, as described tomorrow, this describes what size Oracle should attempt
	to keep them.
	<P>
	<LI><TT>OPTIMAL [NULL</TT>--This parameter specifies that the rollback segments never
	shrink, as they can with the <TT>OPTIMAL</TT> parameter set to a value. This is described
	in detail tomorrow.
</UL>

<P>These storage parameters are defined on the schema object that is being created.
Even though you may have defined a <TT>DEFAULT STORAGE</TT> parameter, you might
define a different <TT>STORAGE</TT> parameter on each schema object.
<H4><FONT COLOR="#000077"><B>Using the </B>STORAGE<B> Clause</B></FONT></H4>
<P>By using the <TT>STORAGE</TT> clause, you can be very efficient with how the schema
objects are stored. If you know you will be loading a large amount of data into a
certain table, it is much more efficient to have a few large extents rather than
many small extents. This will typically be done using the <TT>STORAGE</TT> clause
on the schema objects.</P>
<P>In this example, I will create a table similar to the one that was created with
Schema Manager. In this case, I will add the <TT>STORAGE</TT> clause to define how
the table will grow and use space.</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE dogs
(
Dog_name VARCHAR2(40),
Owner VARCHAR2(40),
Breed VARCHAR2(20)
)
TABLESPACE dogs
STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);
</FONT></PRE>
<P>Remember that the <TT>STORAGE</TT> clause is used for the creation of extents.
Extents are used to hold schema objects. When the schema objects are created and
grow, the default storage parameters are used. These parameters are simply defaults
for the schema objects that are created on these tablespaces. Schema objects that
are created with their own storage parameters override the tablespace defaults.
<H3><FONT COLOR="#000077"><B>Partitioned Tables</B></FONT></H3>
<P>Traditionally, you have had very little control over where your table data physically
resided in the database. A tablespace is created using one or more datafiles. Extents
are then created on that tablespace using the datafiles, but you had no control over
which datafiles your data resided on. This is usually not a problem for most systems,
but it would occasionally cause an I/O imbalance.</P>
<P>With Oracle8, you have much more control over where your data will reside. This
is accomplished through the use of the partitioned table. Oracle8 currently supports
only <I>range partitioning</I> on tables, which assigns rows to different tablespaces
or partitions based on the value of that data. Each partition is assigned a range
of data based on column data. As data is loaded into the system, it is sent to particular
partition based on that data. The partitioned table looks and feels the same to the
end user, but there are many advantages:

<UL>
	<LI>Smaller data segments, thus smaller table scans--Because the data can be divided
	into ranges such as month, year, and so on, if you select the data correctly (for
	example, by month), you can reduce a table scan to just use one partition.
	<P>
	<LI>Smaller indexes--With range partitioning, you have the ability to create indexes
	individually on each partition. This cuts down on the size of each index, speeding
	up access to it.
	<P>
	<LI>Easier backup and recovery--Because partitioned tables can be backed up and recovered
	on a partition basis, the backup operation can be split and run in parallel.
</UL>

<P>In many cases, the ability to partition your tables can be very effective and
provide increased performance and ease of use. Partitioned tables are typically used
when the table is very large and logically can be divided into reasonable pieces.
<H4><FONT COLOR="#000077"><B>Creating Partitioned Tables</B></FONT></H4>
<P>Partitioned tables are created with the following <TT>CREATE TABLE</TT> options:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE [schema.] tablename
(column datatype)
PARTITION BY RANGE (column_list)
(PARTITION [partition_name] VALUES LESS THAN column_value
TABLESPACE ts_name
[, (PARTITION [partition_name] VALUES LESS THAN column_value
TABLESPACE ts_name])
</FONT></PRE>
<P>By specifying the ranges, all rows with values less than the specified values
will be placed in the specified tablespace. This partitions the data based on these
columns. Using partitioning, you can better distribute I/O usage, thus improving
performance by not overloading any specific components.</P>
<P>To create a partitioned table you must first determine which columns to partition.
This is very important because the layout of your data depends on this partitioning.
Key issues involved in determining the partitioning parameters include

<UL>
	<LI>Data distribution for space--If you want to distribute data such that data is
	evenly distributed among tablespaces, you must know the data distribution and variance.