Index-Only Tables

Index-only tables (IOTs), introduced in Oracle8, are indexes in which non-indexed columns are stored adjacent to the index key value. As of Oracle8, IOTs cannot be partitioned. They must specify a primary key.

IOTs were created to reduce I/O associated with keyed lookups. In situations where the index and table are separated, I/O occurs for the index pages as well as the data pages, whereas with IOTs, I/O only occurs for the index pages. Like clusters, IOTs greatly benefit random access and hurt sequential access.

Only use IOTs in situations where random operations (reads or writes) are the majority of the load. I tend to use IOTs instead of clusters in situations where tables are only joined some of the time. As with clusters, I don’t use IOTs in situations where tables grow or shrink rapidly during processing. Depending on the growth rate of the IOT, you may have to drop, re-create, and reload it periodically.

Objects and Embedded Objects

If you use objects instead of tables for large OLTP applications, do not incorporate methods within the objects. As we’ll discuss later in the chapter, PL/SQL is discouraged for large OLTP applications. With the effort that Oracle Corporation is putting into object orientation, I have no doubt that object-oriented features of Oracle will have a place in large OLTP applications at some point in the future.

Use of Transaction Processing Monitor Software

Transaction Process (TP) monitor software provides a communication wrapper around TCP/IP networks. Applications using TP monitors write service requests that reside on the server and obtain data through input from the client. Usually these service requests are written in a 3rd-generation language, such as C or C++. The TP monitor manages client/server communications, not SQLNet.

On the server, TP monitors consolidate service requests into request routers or brokers. Each request router handles transactions from many clients. The number of request routers or brokers is configurable. In this way, TP monitors are similar to SQLNet’s MultiThreaded Server, which uses one process to service multiple clients. Although there is overhead involved in running the request brokers, it reduces the number of processes on the server (which is desirable in a large OLTP application).

TP monitoring packages (such as BEA’s Tuxedo) improve performance for large OLTP applications in several ways. First, due to their architecture, they limit the number of processes on the server so that MTS does not have to be used. Second, they force the use of 3GL code for access, which is more efficient than other types of access. It should be noted that Oracle8 makes several improvements in XA, which can be used to improve performance when using TP monitors such as Tuxedo.

XA is Oracle’s support for the X/Open-compliant applications. It’s an application programming interface (API) standard that governs complex interplatform transactions and is supported by many TP monitors including Tuxedo. By providing XA, Oracle-based transactions can be part of a multiplatform transaction.

For example, in an X/Open transaction, I can update customer information located within DB2/MVS as well as an Oracle database implemented on a UNIX platform as a single unit of work. The X/Open API provides a way to roll back or commit the transaction transparently on both platforms. This is often referred to as a two-phased commit. Furthermore, the X/Open API permits me to rehost any part of the transaction without having to change client code.

The additional advantages and disadvantages of using the X/Open API, as well as details on how to use it, are beyond the scope of this chapter. You can find more information about BEA’s Tuxedo and its support for X/Open transactions from BEA’s Web site at www.beasys.com.

Oracle8 makes several improvements over V7.3 in XA. The two most important improvements are that it now supports OPS and no longer uses cached sessions. Cached sessions were supposed to improve the performance of XA applications by retaining memory allocated by an XA transaction. Memory retention would enhance performance in subsequent executions of the same transaction. As those of you who have worked with XA in applications with large amounts of transactions know, the increased number of sessions and the characteristics of these cached sessions cause enough problems to outweigh the benefits.