New Term: Today you will begin to learn about the Oracle schema objects. The schema objects 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.

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.

Tables

New Term: A table, 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. Column refers to the component of the record that is placed in the database. When you create an empty table, you define the columns. A row can also be referred to as a record. 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.

Figure 12.1.

Example of a table.

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.

How Tables Work

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 STORAGE clause used to create the table.

If a STORAGE clause is not included at table creation, the default STORAGE clause defined on the tablespace is used. If no default STORAGE clause is defined on the tablespace, system defaults are used.

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.

---

NOTE: 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.

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.

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 chained row is a row that spans more than one block).

---

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.

Table Columns

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.

Table Rows

New Term: 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 chained row. Chained rows consist of multiple row pieces. Even if the entire row is stored in the same block, it is sometimes referred to as a row piece.

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:

• Information about the row piece

• Information about chaining (if applicable)

• Cluster keys (if applicable)

• Column definitions

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.

Oracle Data Types

New Term: 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 scalar data types. Other new data types include VARRAYs, REFs, and LOBs, as described later today.

Scalar Data Types

The following scalar data types are available in Oracle:

• CHAR--Fixed-length character. This can be any length between 1 and 255 bytes.

• DATE--Stores Year, Month, Day, Hour, Minute, and Second values.

• LONG--Variable-length character data up to 2GB in size.

• LONG RAW--Similar to long except it is not converted via Oracle utilities the way LONG data is.

• MLSLABEL--For use with Trusted Oracle, this data type stores the binary format of the system label.

• NUMBER--Stores fixed and floating-point numbers. Its definition includes the precision and scale.

• ROWID--Stores a triplet that consists of the data block, the row, and the datafile. Every row in a nonclustered table has a ROWID.

• VARCHAR--A variable-length character data type. The VARCHAR data type is not guaranteed to remain the same, so VARCHAR2 is recommended. Today these two data types are identical.

• VARCHAR--A variable-length character data type that stores 1-2,000 characters. Anything larger requires LONG or LONG RAW.

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.

Table 12.1. Oracle data types.

Non-Oracle data type	Converted to this Oracle data type
DECIMAL	NUMBER
INTEGER, INT	NUMBER(38)
SMALLINT	NUMBER(38)
FLOAT	NUMBER
REAL	NUMBER
DOUBLE	NUMBER

For more specifics on these data types, see the Oracle documentation.

VARRAYs

The VARRAY data type consists of a set of built-in types or objects referred to as elements 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 VARRAY, 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:

CREATE TYPE cost AS VARRAY(20) OF NUMBER(12,2)

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.

REFs

REFs are also new in Oracle8. Think of the REF data type as a pointer to an object. A REF can also be used in a manner similar to a foreign key in an RDBMS. A REF is used primarily to store an object identifier, and to allow you to select that object.

SELECT d.name
FROM dogs d
WHERE d.owner_id = 1;

In this example, d acts as a reference to dogs in order to allow easier access to that table.

LOBs

LOB refers to large schema objects. Oracle recognizes several different types of LOBs, includ-ing the following:

• BLOB--An unstructured binary data field. A BLOB might be something like video or picture information.

• CLOB--A large field that consists of standard, single-byte characters. This might be something like a document.

• NCLOB--Similar to the CLOB type, but consists of single or multibyte characters from the National Character Set.

• BFILE--Essentially a pointer to an externally stored file.

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 LOB pointer is stored there, which points to the location of the LOB data. This makes scanning of that table much faster, thus improving access to the data.

Creating Tables

Tables can be defined and created with several different Oracle tools, including Enterprise Manager, Schema Manager, and the CREATE TABLE 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 CREATE TABLE command. Today you will see all three ways of creating a table.

Managing Tables with Enterprise Manager

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.

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).

A number of schema objects can be managed here, including

• Clusters

• Database links

• Functions

• Indexes

• Package bodies

• Packages

• Procedures

• Refresh groups

• Sequences

• Snapshot logs

• Snapshots

• Synonyms

• Tables

• Triggers

• Views

Figure 12.2.

Enterprise Manager.

This shows only the SYS schema because I am logged into Enterprise Manager as SYS. The SYS 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).

The tables that you see in the SYS 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.

Figure 12.3.

Icons that represent the tables currently in your system.

Managing Tables with Schema Manager

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.

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).

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).

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.

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.

Figure 12.4.

Schema Manager showing tables.

Figure 12.5.

The New Table dialog.

Figure 12.6.

Screen one of the Create Table wizard.

Figure 12.7.

Screen two of the Create Table wizard.

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.

Figure 12.8.

Screen three of the Create Table wizard.

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.

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.

Figure 12.9.

Screen four of the Create Table wizard.

Figure 12.10.

Screen five of the Create Table wizard.

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.

Figure 12.11.

Screen six of the Create Table wizard.

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.

Figure 12.12.

Screen seven of the Create Table wizard.

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 CREATE TABLE command.

Figure 12.13.

The Create Table screen.

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.

Figure 12.14.

Modify table attributes.

Managing Tables with the CREATE TABLE and ALTER TABLE Commands

The CREATE TABLE command can be used to create new tables, the ALTER TABLE command is used to change parameters on an already existing table, and the DROP TABLE command is used to delete an existing table. The CREATE TABLE and ALTER TABLE commands are very similar.

The CREATE TABLE 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 CREATE TABLE and ALTER TABLE commands.

The STORAGE Clause

The primary advantage of using the CREATE TABLE and ALTER TABLE commands is your ability to include the STORAGE clause. As you saw on Day 7, "Administering Tablespaces," the STORAGE clause can be used to define how your space is created and grows. If you recall, Day 7 introduced you to the DEFAULT STORAGE clause; here it is the STORAGE clause.

The DEFAULT STORAGE clause is used to define the storage parameters on a tablespace. These DEFAULT STORAGE parameters will be used for any table created within that tablespace that does not have a STORAGE clause defined on it. A STORAGE clause will override the DEFAULT STORAGE definitions. The STORAGE clause always takes priority over the DEFAULT STORAGE clause.

The STORAGE 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 STORAGE clause has the following syntax:

SYNTAX:

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 ] ]
)

The parameters used in the STORAGE clause are defined as follows:

• INITIAL number K or M--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 K (kilobytes) or M (megabytes). The default is about five data blocks. The size is rounded up to the nearest multiple of five blocks.

• NEXTnumber K or M--The NEXT parameter specifies the size of subsequent extents to be number K (kilobytes) or M (megabytes). This number is also rounded up to the nearest multiple of five data blocks, and defaults to five data blocks.

• MINEXTENTSnumber--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 INITIAL extent and Oracle will use NEXT and PCTINCREASE to calculate the size of subsequent extents. The default value is 1 except for rollback segments, where the default is 2.

• MAXEXTENTSnumber--This parameter specifies the maximum number of extents that can be created for a schema object. This includes the first extent.

• MAXEXTENTS UNLIMITE--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.

• PCTINCREASEnumber--This parameter specifies the size of extents after the second. The initial extents are sized via the INITIAL parameter. The next extent is sized via the NEXT parameter. If nonzero, all subsequent extents are sized to be NEXT multiplied by the PCTINCREASE 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 NEXT parameter. The default value is 50, except for rollback segments which can only have a PCTINCREASE of 0.

• FREELISTSnumber--The FREELISTS 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.

• FREELIST GROUPSnumber--The FREELIST GROUPS parameter does not apply to tablespaces. This parameter specifies the number of groups of freelists.

• OPTIMAL number K or M]--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.

• OPTIMAL [NULL--This parameter specifies that the rollback segments never shrink, as they can with the OPTIMAL parameter set to a value. This is described in detail tomorrow.

These storage parameters are defined on the schema object that is being created. Even though you may have defined a DEFAULT STORAGE parameter, you might define a different STORAGE parameter on each schema object.

Using the STORAGE Clause

By using the STORAGE 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 STORAGE clause on the schema objects.

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 STORAGE clause to define how the table will grow and use space.

CREATE TABLE dogs
(
Dog_name VARCHAR2(40),
Owner VARCHAR2(40),
Breed VARCHAR2(20)
)
TABLESPACE dogs
STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0 MINEXTENTS 2
);

Remember that the STORAGE 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.

Partitioned Tables

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.

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 range partitioning 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:

• 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.

• 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.

• 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.

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.

Creating Partitioned Tables

Partitioned tables are created with the following CREATE TABLE options:

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])

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.

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

• 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.

• 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.

• Other--You might have other goals, such as keeping new information on certain disk volumes and distributing older data elsewhere.

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.

The table should be partitioned on the column named region; because each region has a region number from 1 to 4, region is the perfect column to partition on. The table creation looks something like this:

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 )

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.

Object Tables

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.

New Term: With object tables, instead of using data types, you use object types. 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:

CREATE TYPE dog_info AS OBJECT
(
dog_breed varchar2(40),
dog_id NUMBER,
MEMBER FUNCTION get_id RETURN VARCHAR2
);

Here the attributes are dog_breed and dog_id, whereas the method is get_id. 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.

Now that you have created the type dog_info, you can create a table with one of the columns of type dog_info. This will use the type definition to define the column and this column will have both the attributes and the member function.

CREATE TABLE dogs (
Dog_name VARCHAR2(40),
dog_owner VARCHAR2(40),
dog_id dog_info );

This table can be populated by using a statement such as:

INSERT INTO dogs VALUES (
`Pierce',
`Whalen',
dog_info(`Border Collie','U1234') );

Now you can retrieve the dog's ID number via the following SQL statement:

SELECT dog_id.get_id
FROM dogs
WHERE dog_name = `Pierce' AND dog_owner = `Whalen';

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.

Index-Only Tables

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.

Nested Tables

New Term: A nested table, 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:

CREATE TYPE dog_info_table (
Dog_id NUMBER,
Dog_breed VARCHAR2(40),

Dog_birthdate DATE);

CREATE TABLE dog_owners (
Name VARCHAR2(40),
Address VARCHAR2(60),
Dog_name VARCHAR2(40),
Dog_info dog_info_table);

Each item dog_owners.dog_names is a nested table. With this, each record in the dog_names table relates to a dog that is owned by the dog_owner referenced here. Data can be inserted into the table in the following manner:

INSERT INTO dog_owners VALUES (
`Whalen',
`12345 Main St., Houston, TX',
`Pierce'
Dog_info(`U1234', `Border Collie', '18-Nov-1988') );

An index can be created with the syntax:

CREATE INDEX dogs_index ON dogs_owners.dog_names(dog_id);

Views

New Term: A view 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 base tables. 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.

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.

Managing Views

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.

Figure 12.15.

The Create View screen.

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 CREATE VIEW command like so:

CREATE VIEW executives AS
SELECT employee_name, employee_rank, employee_salary
FROM employee
WHERE employee_rank > 99;

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.

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 CREATE OR REPLACE VIEW syntax. This either creates or replaces a view, depending on the current status.

Synonyms

New Term: Synonyms 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.

Synonyms come in two forms: public and private. A public synonym is owned by the PUBLIC 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.

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

SELECT * FROM etw.dogs;

can be simplified to

SELECT * FROM dogs;

When you create a synonym the access path is simplified; because etw.dogs is defined as dogs, 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.

Figure 12.16.

The Create Synonym tool.

Here I have selected PUBLIC as the schema in which I want this synonym to exist. I have also configured this synonym to be an alias for the dogs table I created earlier. I used the Show SQL option. The SQL syntax used here (which can also be used with Server Manager) is

CREATE PUBLIC SYNONYM DOGS for "ETW"."DOGS";

Summary

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.

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.

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.

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.

What's Next?

On Day 13, "Using Indexes and Sequences," 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.

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.

Q&A

Q What are tables used for?

A 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.

Q What is a partitioned table?

A 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.

Q What can nested tables be used for?

A 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, location, in the main table.

Q Why would you use a view?

A 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.

Workshop

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, "Answers."

Quiz

1. What is a table column?

2. What is a row in a table?

3. What two components are row pieces made of?

4. What makes up an object?

5. What is a nested table?

6. What is a partitioned table?

7. How are tables partitioned?

8. What is a view?

9. What is the STORAGE clause used for?

10. What is a synonym?

Exercises

1. Create a simple table.

2. Create a table with a nested column.

3. Create a view on that table.

4. Create a synonym to that view.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.