Oracle7 Server Application Developer's Guide

Contents Index Home Previous Next

Designing the Application

After completing your database design, you are ready to begin designing the application itself. This, too, is an iterative process, and might also cause you to rethink your database design. As much as possible, you should involve your audience in these design decisions. You should make your application available to the end-users as early as possible in order for them to provide you with the feedback needed to fine tune your design.

There are many tools available, from Oracle Corporation as well as other vendors, to aid in the development and implementation of your application. Your first task is to evaluate the available tools and select those that are most appropriate.

Using Available Features

You must next determine how to implement your requirements using the features available in Oracle, as well as any other tools and utilities that you selected in the previous step. The features and tools that you choose to use to implement your application can significantly affect the performance of your application. The more effort you put into designing an efficient application, the less time you will have to spend tuning the application once it is complete.

Several of the more useful features available to Oracle application developers are listed below. Each of these topics is discussed in detail later in this book.

Integrity Constraints

Integrity constraints allow you to define certain requirements for the data that can be included in a table, and to ensure that these requirements are met regardless of how the data is entered. These constraints are included as part of the table definition, and require no programming to be enforced; see Chapter 6 for instructions on their use.

Stored Procedures and Packages

Commonly used procedures can be written once in PL/SQL and stored in the database for repeated use by applications. This ensures consistent behavior among applications, and can reduce your development and testing time.

Related procedures can be grouped into packages, which have a package specification separate from the package body. The package body can be altered and recompiled without affecting the package specification. This allows you to make changes to the package body that are not visible to end-users, and that do not require objects referencing the specification to be recompiled. See Chapter 7 for additional information.

Database Triggers

Complex business rules that cannot be enforced using declarative integrity constraints can be enforced using triggers. Triggers, which are similar to PL/SQL anonymous blocks, are automatically executed when a triggering statement is issued, regardless of the user or application. See Chapter 9 for additional information.

Database triggers can have such diverse uses as performing value-based auditing, maintaining derived data values, and enforcing complex security or integrity rules. By moving this code from your application into database triggers, you can ensure that all applications behave in a uniform manner.

Cost-Based Optimizer

The cost-based optimization method uses statistics about tables, along with information about the available indexes, to select an execution plan for SQL statements. This allows even inexperienced users to submit complex queries without having to worry about performance.

As an application designer, there may be times when you have knowledge of the data in your table that is not available to the optimizer, and that allows you to select a better execution path. In these cases, you can provide hints to the optimizer to allow it to select the proper execution path. See the Oracle7 Server Tuning manual for more information.

Shared SQL

Shared SQL allows multiple users to share a single runtime copy of procedures and SQL statements, significantly reducing memory requirements. If two identical SQL statements are issued, the shared SQL area used to process the first instance of the statement is reused for the processing of the subsequent instances of the same statement.

You should coordinate with your database administrator (DBA), as well as other application developers, to establish guidelines to ensure that statements and blocks that perform similar tasks can use the same shared SQL areas as often as possible. See your Oracle7 Server Tuning manual for additional information.

National Language Support

Oracle supports both single and multi-byte character encoding schemes. Because language-dependent data is stored separately from the code, you can easily add new languages and language-specific features (such as date formats) without altering your application code. Refer to the Oracle7 Server Reference manual for more information on national language support.

Locking

By default, Oracle provides row-level locking, allowing multiple users to access different rows of the same table without lock contention. Although this greatly reduces the chances of deadlocks occurring, you should still take care in designing your application to ensure that deadlocks do not occur.

Online transaction processing applications--that is, applications with multiple users concurrently modifying different rows of the same table--benefit the most from row-level locking. You should design your application with this feature in mind.

Oracle locks are also available to you for use within your applications. These locks are provided as part of the DBMS_LOCK package, which is described in Chapter 3.

Profiles

Profiles can be used to enforce per-query and per-session limits on resource use. When designing your applications, you might want to consider if any users have been denied access to the system due to limited resources. Profiles make it possible to allow these infrequent users limited access to the database. If you choose to allow access to these users, you must consider their requirements when formulating your design. Profiles are generally controlled by the database administrator. Consult your database administrator to determine if access can be granted to additional users and to identify this audience.

Sequences

You can use sequence numbers to automatically generate unique keys for your data, and to coordinate keys across multiple rows or tables. The sequence number generator eliminates the serialization caused by programmatically generating unique numbers by locking the most recently used value and then incrementing it. Sequence numbers can also be read from a sequence number cache, instead of disk, further increasing their speed.

Industry Standards Compliance

Oracle is designed to conform to industry standards. If your applications must conform to industry standards, you should consult the Oracle7 Server SQL Reference manual for a detailed explanation of Oracle's conformance to SQL standards.

Using the Oracle Call Interface

If you are developing applications that use the Oracle Call Interface (OCI), you should be aware that the OCI for Oracle7 release 7.3 offers many new calls. These provide

Contents Index Home Previous Next