033-036 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Schemas

A schema is a set of objects owned by a particular user s account. For instance, if your login name is jschmoe , then jschmoe is your schema. If someone wants to reference the table PHONE_NUMBERS in your schema, the reference would be as follows :

 jschmoe.PHONE_NUMBERS

Referencing every object with a schema name is not a good idea, because it s quite common for code to be developed in one schema and moved to another, once testing has been completed. Fortunately, objects referenced without a schema are presumed to exist in the schema of the current user.

It is important to remember that a schema is not an object within the database, but a way of referencing objects.

Sequences

Oracle provides sequences that allow unique integers to be generated. These integers are typically used as primary key values in tables. Sequence numbers can become quite large and can be configured to roll over once they reach their maximum size (an extremely large number; the actual maximum value for a sequence number depends on your hardware and operating system).

Snapshots

Oracle7 provides the ability to create snapshots . A snapshot is a table that contains the result set of a query on one or more tables or views. This is typically used when dealing with remote databases, but can also be used to store the results of complex queries for reporting purposes. A snapshot is automatically refreshed by the database at specified times (which are defined when the snapshot is created).

TIP: Using A Snapshot To Simplify Reports
If you need to develop a fairly complex report that must be run several times a day at regular intervals, your best bet is to create a snapshot. Once the snapshot is defined, the database populates the snapshot automatically. The snapshot holds the result set from a query, so your report will be much easier to generate because it must only query and format the data contained in the snapshot.

Another benefit of using snapshots is that the data contained in a snapshot can be presented to users for the development of ad hoc queries, reducing the number of custom reports that have to be developed.

Synonyms

When a typical user references an object without a schema qualifier, it is quite uncommon for the referenced object to exist in that user s schema. You re probably thinking, Didn t I just read that objects are presumed to exist inside the user s schema? You did.

The objects being referenced do exist inside the user s schema, but the user is not accessing the real objects; instead, that user is referencing a synonym that points to the real object (much like your telephone number isn t you, but still allows people to get in touch with you). Because the user accesses a synonym for an object, the user is able to reference objects without using schema qualifiers.

A public synonym is a synonym to which all database users have access, unless overridden by a private synonym. A private synonym works only for the user who owns the synonym. Public synonyms are created by the DBA to allow all users to reference an object; private synonyms are created by the DBA for specific users as the need arises. If a private and public synonym have the same name, the private synonym takes precedence. Figure 2.4 illustrates this concept.

Figure 2.4 The resolution of public and private synonyms.

Tables And Columns

Data in an Oracle database resides in tables . A database typically contains many tables, each of which typically has many referential integrity links to other tables. There are two types of elements within tables: columns and rows.

A column is a single data element that contributes to the structure of a row. All tables must have at least one column; most have at least four or five columns. The maximum number of columns that can exist in a table is 255. Each column within the table is given a datatype.

A row is a group of related data elements in which each item of the group corresponds to a column. A table typically contains many rows. In large data warehouses, large tables may contain tens of millions of rows.

Views

A structure quite similar to a table is a view . Views are essential SQL queries stored in a database as an object. When a reference is made to the object, the query is executed and the result set is returned to the user. Figure 2.5 illustrates the relationship of a view to its base tables.

Figure 2.5 The relationship of a view to its base tables.

The use of views has serious performance implications. While the views makes SQL and PL/SQL code easier to develop and maintain, there is significant overhead involved (remember, views are essentially queries that execute when someone references the view).

Views are often used in systems when a certain functionality requires a complex join of tables. This is often the case when several tables must be joined to create the structure of the view, since the typical user doesn t have the knowledge needed to join the tables effectively.

Table of Contents