Page 146
Listing 6.25 L_06_25.SQLCreating a Partitioned Table in Oracle8
create table ex_employee (payroll_number number(4), last_name varchar2(15), first_name varchar2(15), . . fk_department char(4)) partition by range (payroll_number) (partition t1 values less than (250) tablespace sp1, partition t2 values less than (500) tablespace sp2, partition t3 values less than (750) tablespace sp3, partition t4 values less than (maxvalue) tablespace sp4);
The partition clause follows the Create statement. The first part of the statement specifies the variable(s) that control where the data will reside. In the case of Listing 6.25, this variable is payroll_number. Employees with a payroll_number values less than 250 are placed in the first partition located in tablespace sp1. The next partition contains the payroll_number values greater than 250 and less than 500. The last partition uses an upper bound called maxvalue. This represents the greatest value in the table. If you do not use this bound, Oracle8 will not allow a payroll_number value greater than the specified bound. The maxvalue ensures that all records can be added to the database.
After the table has been created, the partitions can be managed through the use of the alter table command. This command is used in conjunction with the drop, add, modify, rename, and truncate options. Listing 6.26 illustrates several reference only versions of the alter table command.
Listing 6.26 L_06_26.SQLUsing the Alter Command to Manage Partitions
Alter table ex_employee add partition t5 values less than (900) tablespace sp5; Alter table ex_employee drop partition t5; Alter table ex_employee rename partition t5 to t10; Alter table ex_employee truncate partition t10 drop storage;
Oracle8 also gives you the ability to partition indexes. A partitioned index is an index that exists on multiple partitions. The declaration of a partitioned index is similar to that of a table. The partition clause follows the Create Index clause. Listing 6.27 illustrates an example of this. This listing is for reference, and not for execution.
Page 147
Listing 6.27 L_06_27.SQLCreating a Partitioned Table in Oracle8
create index empindex on ex_employee (payroll_number) partition by range (payroll_number) (partition t1 values less than (250) tablespace sp1, partition t2 values less than (500) tablespace sp2, partition t3 values less than (750) tablespace sp3, partition t4 values less than (maxvalue) tablespace sp4);
The command in Listing 6.27 partitioned the index on the payroll_number column. This type of index is called a local index. In this example, the index is partitioned and placed in the same tablespaces as the partitioned table. However, it is not necessary for the index partition to be the same as the table.
The normal Create Index command can still be used in Oracle8. The normal index command creates a global index. This means the index contains the keys for all of the table partitions.
Oracle8 has a Create Type command that allows you to create additional data types and use them within another object. These data types consist of one or more variables. For instance, Listing 6.28 contains a reference only command to create a data type called features that contain variables for height, weight, and eye color.
Listing 6.28 L_06_28.SQLCreating a Data Type for Features
create type features (height number(2), weight number(3), eye_color varchar2(2));
This data type exists independent of any table. It can be used with any number of other objects. For instance, Listing 6.29, a reference only example, uses the features data type as an attribute in a table.
Listing 6.29 L_06_29.SQLUsing the Features Data Type in the ex_employee Table
create table ex_employee (payroll_number number(4), last_name varchar2(15), first_name varchar2(15), employee_features features, . fk_department char(4))
Page 148
The beauty of these defined data types is that they are objects that can be used within many other objects. For instance, the features data type can be used with customers, vendors, or any other table or entity that tracks human features. It brings the power of object-oriented components to the database.
The objects contained in a type are not limited to variables. A type can also contain functions. This allows you to bind code and the data together. This is an important object-oriented concept called encapsulation. Data and code are bound together and can be used freely by other objects that need the data and functions.
A new data type in Oracle8 is Varray. This type is an ordered list or array of elements. The array can be included as a type in a table. When specifying the varray you must declare the maximum number of values. Listing 6.30 contains two reference only commands. The first creates a varry of phone numbers. The second command uses the varray in a table. The effect is that the ex_employee table has the ability to retain multiple telephone numbers for an employee. Since this is a variable array, only the space contained by values will be used.
Listing 6.30 L_06_30.SQLCreating a Varray and Using It in a Table
Create type numbers as varray(3) of varchar2(10); create table ex_employee (payroll_number number(4), last_name varchar2(15), first_name varchar2(15), employee_features features, telephone numbers, . fk_department char(4))
Databases are comprised on a number of objects. The Create, Alter, and Drop commands are used to make, modify, and eliminate the objects. Tables are objects that contain the data. They are comprised of items or fields that have alphanumeric or numeric properties. The table definitions often contain constraints used to maintain the integrity of the database.
Database performance is enhanced through the use of indexes. Indexes are mini-tables that reduce the amount of disk Oracle7 must read to acquire data. Indexes are also used to ensure uniqueness of the primary key. A view is a database object that creates a virtual table when executed. Views may be used as a table name in a Select command.
If the data you need is on a remote database, you can create a database link that tells Oracle7 where the data is located. A user account owns database objects. Before the user account can