Chapter 12
Oracle Database Objects

by Joe Greene

In This Chapter

•  Oracle8 Database Objects

•  Tables

•  Indexes

•  Views

•  Synonyms

•  Stored Procedures and Packages

•  Clusters

•  Sequences

•  Tablespaces

•  Partitions

•  Object Data Types

•  Constraints

The word object is rather heavily used in the computer industry these days. Development tool vendors argue over whose product is more object-oriented. Almost everyone claims some form of object-oriented capabilities (with Oracle8, that list now includes Oracle). However, the term object has been used for some time in the Oracle database world to describe those entities that exist within the database files. These are the logical entities with which users interact. For developers, understanding these database objects is all that they may need to know about Oracle databases other than their user ID and password. This list of database objects includes:

•  Tables

•  Indexes

•  Views

•  Synonyms

•  Sequences

•  Partitions

•  Clusters

•  Stored procedures and packages

•  User-defined data types (new to Oracle8)

•  Tablespaces

•  Constraints

This chapter explains these objects so you can better understand the tools that are at your disposal during the database design process. Some of the topics, such as extent sizing and grouping tables into tablespaces, may seem to be a bit philosophical at this point. However, knowledge of these features is useful when you are trying to extract the best performance out of a given database system.

Oracle8 Database Objects

In the introduction to this chapter, I listed the database objects that I planned to discuss. In this section, I will go over how these objects fit together to form a database. I will then go into more detail on these objects in the remaining sections of this chapter. The purpose of the basic Oracle8 database objects are as follows:

•  Tables. Tables are the basic unit of data storage within an Oracle database. They are typically the entity that you issue your query against and make your data changes to. It is possible to conceive of a database without any user data tables, but such a database is highly unlikely.

•  Indexes. Some database tables can get quite large. While it would be possible to scan these large tables every time that you wanted to extract a single row of data, it would not be very efficient. Therefore, if there is a common selection criterion used to select a row (for example, employee ID in a table about staff members), you would probably want to build an index to track which row or rows in the table are associated with a given value for this selection criterion. This enables Oracle to retrieve a single row from the large table after scanning the much smaller index, thereby saving processing time.

•  Views. Sometimes, you may want to give a user access to only some of the data in a table. Other times, you may need to join together several tables to produce the data that users want to access. These goals can be accomplished through the use of a view. A view can be thought of as an SQL select statement that selects part of one table or joins multiple tables together but can be accessed as though it were a simple table.

•  Synonyms. There are instances when you may want to use an alternate name for a database table or view. Perhaps you use a very formal scheme for naming tables that makes sense to developers but is a little bit cumbersome to end users. Perhaps you have a number of different table and view owners and you do not want to have your users type in fully qualified table names (for example, owner.table_name). In these and some other circumstances, you may want to use a synonym to give the table or view an alternative name to make typing the SQL statements a little easier.

•  Sequences. There are many cases in database programming where you want to be able to guarantee that something (a row in a table) has a unique attribute value. To implement this rather common need, Oracle8 supports sequences (a unique series of numbers) that can used in your tables. This is often used to generate primary keys.

•  Tablespaces. When databases grow larger, it becomes advantageous to group tables and indexes together to spread data between disk drives for performance reasons. The mechanism to accomplish this task is the tablespace. Tables and indexes are created in a given tablespace. Tablespaces have data files (located on one or more disk drives) associated with them to enable you to control what information is placed on which disk drive.

•  Partitions. A new feature to Oracle8 is the partition. Take the situation where you want to scan January’s transactions in a table that contains 10 years’ worth of data. You would waste a lot of time scanning records that do not meet your criteria. Oracle8 enables you to group data within a table into partitions that can then be placed on explicit disk drives. Oracle’s query engine is smart enough to ignore partitions that do not meet the selection criteria, thereby saving time. A reliability feature is that if a given partition is unavailable due to a disk drive failure, you can still access data in the other partitions associated with that table.

•  Clusters. Clusters are an older construct within Oracle used primarily in the data dictionary of Oracle itself. What this construct enables you to do is group data in different objects together for efficient access. For example, if you have a lookup table that always leads to a main data table, you could cluster them together for rapid access. However, as Oracle’s storage algorithms have improved, it is rare when you have to use a cluster.

•  Stored procedures and packages. The Oracle8 database enables you to store and execute software within the database itself. This software processing mechanism lets you automatically execute certain algorithms (whenever a table is updated) or on demand (the user or program makes an explicit call to that procedure).

•  User-defined data types (new to Oracle8). Real-world objects (here using the business definition of an object) have multiple bits of information that are applicable to them. For example, an employee may have an employee ID, a series of addresses, and perhaps even a photograph. Oracle8 enables you to group these dissimilar or repeating datatypes together logically so that you can access them.

•  Constraints. A primary concern of a production database is not just to store data, but to store good, valid data. This can be quite a challenge when there are a number of programmers working on an application or users who have ad hoc access to the database. Oracle provides constraints to help control what can be put into a given column in a given table to enhance the accuracy of the information.