As the cost of memory drops, 500 MB Oracle regions will not be uncommon. Until that time, however, the DBA must carefully consider the ramifications of pinning a package in the SGA.

Oracle Triggers

Many database systems now support the use of triggers that can be fired at specific events. The insert, modification, or deletion of a record may fire a trigger, or business events such as place_order may initiate a trigger action. Oracle Corporation claims that the design of its triggers closely follow the ANSI/ISO SQL3 draft standard (ANSI X3H6), but Oracle triggers are more robust in functionality than the ANSI standard. Triggers are defined at the schema level of the system, and will “fire” whenever an SQL select, update, delete, or insert command is issued. Remember that a trigger is always associated with a single DML event.

Deciding When To Use A Trigger

The choice of when to use a trigger and when to use a stored procedure can have a profound impact on the performance of the system. In general, triggers are used when additional processing is required as a row is inserted into a table. For example, assume that whenever a customer row is added, the system is required to look for the customer in the bad_credit table. If the customer appears in the bad_credit table, then its shipping_status column is set to ‘COD’. In this case, a trigger on INSERT OF CUSTOMER can fire the PL/SQL procedure to do the necessary lookup and set the shipping_status field to its appropriate value.

Oracle triggers have the ability to call procedures and a trigger may include SQL statements, thus providing the ability to “nest” SQL statements. Oracle triggers are stored as procedures that may be parameterized and used to simulate object-oriented behavior. For example, assume that we want to perform a behavior called CHECK_TOTAL_INVENTORY whenever an item is added to an order. (See Figure 2.3.)

Figure 2.3  The relationship between objects, triggers, and SQL.

The trigger definition would be:

CREATE TRIGGER CHECK_TOTAL_INVENTORY
           AFTER  SELECT OF ITEM
FOR EACH ROW
   SELECT COUNT(*) INTO :count
   FROM QUANTITY WHERE ITEM_# = :myitem:

IF :count < ITEM.TOTAL THEN
     ......
END IF;

Triggers could also be combined to handle multiple events, such as the reordering of an ITEM when the quantity-on-hand falls below a predefined level:

CREATE TRIGGER REORDER BEFORE UPDATE ON ITEM
   FOR EACH ROW WHEN (new.reorderable = 'Y')
        BEGIN
               IF new.qty_on_hand + old.qty_on_order < new.minimum_qty
               THEN
                       INSERT INTO REORDER VALUES (item_nbr,                        reorder_qty);
                       new.qty_on_order := old.qty_on_order +                        reorder_qty;
               END IF;
END;

Oracle Hash Tables

Oracle7 now supports the concepts of hash clusters. A hash cluster is a construct that works with Oracle clusters and uses the HASHKEYS command to allow fast access to the primary key for the cluster. Oracle relies upon a “hashing algorithm,” which takes a symbolic key and converts it into a row ID (ROWID), as in Figure 2.4. The hashing function ensures that the cluster key is retrieved in a single I/O, which is faster than reading multiple blocks from an index tree. Because a hashing algorithm always produces the same key each time it reads an input value, duplicate keys have to be avoided. In Oracle, these “collisions” result when the value of HASHKEYS is less than the maximum number of cluster key values. For example, if a hash cluster uses the customer_nbr field as the key, and we know that there will be 50,000 unique customer_nbr values, then we must be sure that the value of HASHKEYS is set to at least 50,000. Also, you should always round up your value for HASHKEYS to the next highest prime number. Here is an example of a hash cluster:

Figure 2.4  A sample hashing routine.

CREATE CLUSTER my_cluster (customer_nbr    VARCHAR(10))
   TABLESPACE user1
        STORAGE (initial 50K next 50K pctincrease 1)
        SIZE 2K
        HASH IS customer_nbr HASHKEYS 50000;

Now a table is defined within the cluster:

CREATE TABLE CUSTOMER (
        customer_nbr  NUMBER PRIMARY KEY )
CLUSTER my_cluster (customer_nbr);

The SIZE parameter is usually set to the average row size for the table. Oracle recommends using a hash cluster when the following is true:

•  Use hash clusters to store tables that are commonly accessed by WHERE clauses that specify equalities.

•  Only use a hash clusters when you can afford to keep plenty of free space on each database block for updates. This value is set by the PCTFREE statement in the CREATE TABLE parameter.

•  Only use hash cluster if you are absolutely sure that you will not need to create a new, larger cluster at a later time.

•  Do not use a hash cluster if your table is commonly accessed by full-table scans, especially if a great deal of extra space from future growth has been allocated to the hash cluster. In a full-table scan, Oracle will read all blocks of the hash cluster, regardless of whether or not they contain any data rows.

•  Do not use a hash cluster if any of the hash cluster keys are frequently modified. Changing the value of a hash key causes the hashing algorithm to generate a new location, and the database will migrate the cluster to a new database block if the key value is changed. This is a very time-consuming operation.

Keep in mind that the total size of the index columns must fit inside a single Oracle block. If the index contains too many long values, additional I/O will be required and UPDATEs and INSERTs will cause serious performance problems. Note the sample hashing routine shown in Figure 2.4.

The database designer may choose to make the buffer blocks large to minimize I/O if the application “clusters” records on a database page. If a customer record is only 100 bytes, we will not gain by retrieving 32,000 bytes in order to get the 100 bytes that we need. However, if we cluster the orders physically near the customer (on the same database page), and if I/O usually proceeds from customer to order, then we will not need further I/O to retrieve orders for the customer. They will already reside in the initial read of 32,000 bytes. (See Figure 2.5.)

Figure 2.5  A sample Oracle cluster.