 |
|
PL/SQL Schemas
Oracle Tips by
Burleson
|
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.
Oracle 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).
Oracle 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.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
