0642-0644

Previous Table of Contents Next

Page 642

Listing 25.3. continued

 WHERE  A.ID = B.Individual_ID        AND B.Company_ID = C.ID        AND B.Individual_ID = D.Individual_ID        AND B.Company_ID = D.Company_ID        AND B.Individual_ID = E.Individual_ID        AND B.Company_ID = E.Company_ID        AND D.Address_Type = F.ID    AND E.Phone_Type = G.ID 

TIP

Aliasing table names with a single letter can save a lot of typing.

Additional denormalization could improve performance further, but probably at the cost of flexibility.

The previous example can can be created on single or multiple columns and might or might not be unique. When you create an index on multiple columns, the order in which the columns are declared is particularly important, because Oracle treats the values of such an index as an aggregate. The column that will be used the most should be declared first in a multicolumn index. In the previous example, the Employee_Company_Relation table is a prime candidate for an index. If both columns are indexed in aggregate, the table itself should never be read. Individual_ID should be declared as the first column in the index because it is used for one more join. The DDL to create this column and its index might look like the script in List- ing 25.4.

Listing 25.4. DDL script that creates a table and an aggregate index.

 CREATE TABLE Individual_Company_Relation (       Individual_ID        NUMBER(10)    NOT NULL      ,Company_ID           NUMBER(10)    NOT NULL ) TABLESPACE Contact_Main STORAGE (INITIAL       50K          NEXT               10K          MAXEXTENTS     10  ); CREATE INDEX Indiv_Co       ON Individual_Company_Relation (Individual_ID, Company_ID) TABLESPACE Contact_Index STORAGE (INITIAL        50K          NEXT           10K          MAXEXTENTS      10); 

Page 643

Note that the MINEXTENTS parameter to the STORAGE clause was not used. The default value of 1 is acceptable in most circumstances.

The creation of indexes should be planned very carefully , because improper use of indexes can have a damaging effect on performance. Even where indexes improve the performance of SELECT statements, they have a negative impact on INSERTs and UPDATEs, because the indexes must be modified in addition to the tables.

The column attributes play a role in performance as well. Wherever possible, integers should be used as keys because integers can be compared faster than any other data type. Column and table constraints should be avoided because they must be checked whenever a value is inserted or updated. Although these constraints are often necessary, integrity should be enforced by other means when it is possible to do so safely.

Rollback segments also play an important role in the overall performance of the database. As the name would imply, Oracle uses rollback segments as temporary storage for data needed to reverse a transaction. This data must be stored until the transaction is committed. Rollback segments must be large enough to store this data for all transactions occurring at a given time. If rollback segments are not large enough, transactions will fail.

To properly estimate the size of the rollback segments needed, the designer must know how many users will be submitting transactions and the maximum size of the rows affected by a single transaction. In many large databases, transactions are initiated by batch processes used to load and update data from external sources, or to create summary tables. These batch processes often generate much larger transactions than the user community and should be considered when planning rollback segments. A rollback segment, like other database objects, can be created with a script, as demonstrated in Listing 25.5.

Listing 25.5. DDL script that creates a rollback segment and brings it online.

 CREATE PUBLIC ROLLBACK SEGMENT contact_rbs1       TABLESPACE contact_rb_segs       STORAGE (INITIAL            100K                NEXT               100K                OPTIMAL            500K                MAXEXTENTS          100); ALTER ROLLBACK SEGMENT contact_rbs1 ONLINE; 

NOTE

When a rollback segment is created, it is not immediately available for use. The ALTER ROLLBACK SEGMENT command must be issued to bring the rollback segment ONLINE before it can be used.

Page 644

The OPTIMAL parameter to the STORAGE clause indicates that when extents have been created, they will not be deallocated below this value. This, in effect, sets the minimum size of the rollback segment after that threshold is reached.

Rollback segments are typically created in a separate tablespace. The size of the data files in this tablespace should be sufficient to hold the rollback segments at their maximum extents.

Another performance consideration relates to the creation of temporary segments. Temporary segments are similar to rollback segments, except that they are used to store result sets rather than transaction information. When a SELECT statement produces a result set that is too large to be stored in memory, a temporary table is created to store the results until the cursor is closed. Temporary tables may also be created by Oracle to store temporary result sets for complex joins or unions. As with rollback segments, these temporary segments must be sufficiently large to store this data, or SELECT statements can fail.

Temporary segments must be assigned to users explicitly. If no temporary segment is assigned, the SYSTEM tablespace is used by default. It is preferable to create a separate tablespace for these temporary segments, and assign it to users using the TEMPORARY TABLESPACE clause of the CREATE USER command. When designing temporary tablespaces, keep in mind any batch processes that may create large cursors . These, too, will require the use of temporary segments.

It might be preferable to create separate segments (both temporary and rollback) for different groups of users, based on the transactions and result sets generated by different groups of users.

Other performance considerations relate to the physical layout of files on disk. Proper use of multiple disks and controllers, clustering, table partitioning, and tablespace striping can improve performance greatly in certain situations.

In the index-creation example (refer to Listing 25.4), notice that the table and the index were created in separate tablespaces. The example assumes that the tablespaces were created on separate disks, using separate controllers. Keeping indexes on separate physical devices with separate controllers allows the index and the tables to be read almost simultaneously and minimizes the movement of the read-write heads. In the sample SQL statement, this would allow the read-write head of one drive to continue reading the index while a separate controller reads the Addresses and Phones tables to find the corresponding values. If the index were on the same disk, either the whole index would have to be read into memory before the table or the heads would have to move back and forth, reading part of the index and part of the table.

The use of separate controllers and disks also applies to rollback and temporary segments. In an ideal configuration, tables, indexes, rollback segments, and temporary segments would all be on separate disks using separate controllers. This configuration would greatly improve overall performance, particularly for batch processes such as the creation of summary tables. In practice, however, this configuration is rarely possible. Regardless of the actual hardware configuration, the designer should carefully consider how these data files will be accessed. The design should attempt to minimize the movement of read/write heads for the most common or mission-critical database operations.

Previous Table of Contents Next