B Application Examples

This appendix describes examples of applications using the Expression Filter.

Active Application

In an active database system, the server performs some actions when certain criteria are met. For example, an application can monitor changes to data in a database table and react to these changes accordingly.

Consider the Car4Sale application described in Chapter 1. In this application, the Consumer table stores the information about consumers interested in buying used cars. In addition to the Consumer table described in Chapter 1, assume that there is an Inventory table that stores information about all the used cars available for sale, as defined in the following example:

CREATE TABLE Inventory (Model   VARCHAR2(20), 
                        Year    NUMBER, 
                        Price   NUMBER, 
                        Mileage NUMBER);

Now, you can design the application such that the system reacts to any changes made to the data in the Inventory table, by defining a row trigger on the table:

CREATE TRIGGER activechk AFTER insert OR update ON Inventory
  FOR EACH ROW
  DECLARE
    cursor c1 (ditem VARCHAR2) is
      SELECT CId, Phone FROM Consumer WHERE EVALUATE (Interest, ditem) = 1;
    ditem VARCHAR2(200);
 BEGIN
  ditem := Car4Sale.getVarchar(:new.Model, :new.Year, :new.Price, :new.Mileage);
  
  for cur in c1(ditem) loop
    DBMS_OUTPUT.PUT_LINE('  For Model '||:new.Model||' Call '||cur.CId||
                         ' @ '||cur.Phone);
  end loop;
END;
/
 

This trigger evaluates the expressions for every row inserted (or updated) into the Inventory table and prints a message if a consumer is interested in the car. An Expression Filter index on the Interest column can speed up the query on the Consumer table.

Batch Evaluation of Expressions

To evaluate a set of expressions for a batch of data items, you can perform a simple join of the table storing data items and the table storing expressions. You can join the Consumer table with the Inventory table to determine the interest in each car, as shown in the following example:

SELECT DISTINCT Inventory.Model, count(*) as Demand
   FROM  Consumer, Inventory
   WHERE EVALUATE (Consumer.Interest, 
                Car4Sale.getVarchar(Inventory.Model, 
                                    Inventory.Year, 
                                    Inventory.Price, 
                                    Inventory.Mileage)) = 1 
   GROUP BY Inventory.Model
   ORDER BY Demand DESC;

The EVALUATE operator's join semantics can also be used to maintain complex N-to-M (many-to-many) relationships between data stored in multiple tables.

Resource Management

Consider an application that manages IT support resources based on the responsibilities (or duties) and the workload of each representative. In this application, the responsibilities of the representatives are captured as expressions defined using variables such as the priority of the problem, organization, and the environment.

Create a table named ITResource to store information about all the available representatives, as shown in the following example:

-- Create the object type and the attribute set for ticket description --
CREATE OR REPLACE TYPE ITTicket AS OBJECT (
                       Priority       NUMBER,
                       Environment    VARCHAR2(10),
                       Organization   VARCHAR2(10));
/
BEGIN
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                   from_type => 'Yes');
END;
/

-- Table storing expressions --
CREATE TABLE ITResource (RId        NUMBER,
                         Duties     VARCHAR2(100));

BEGIN
  DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                   expr_tab => 'ITResource',
                                   expr_col => 'Duties');
END;
/

INSERT INTO ITResource (RId, Duties) VALUES
   (1, 'Priority <= 2 and Environment = ''NT'' and Organization =
                                                   ''Research''');

INSERT INTO ITResource (RId, Duties) VALUES
   (2, 'Priority = 1 and (Environment = ''UNIX'' or Environment = ''LINUX'')
        and Organization = ''APPS''');

Create a table named ITProblem to store the problems filed, as shown in the following example:

CREATE TABLE ITProblem (PId             NUMBER,
                        Description     ITTicket,
                        AssignedTo      NUMBER);

The AssignedTo column in the ITProblem table stores the identifier of the representative handling the problem.

Now, use the following UPDATE statement to assign all the previously unassigned problems to capable IT representatives:

UPDATE ITProblem p SET AssignedTo =
             (SELECT RId FROM ITResource r
              WHERE EVALUATE(r.Duties, p.Description.getVarchar()) = 1
                    and rownum < 2)
    WHERE AssignedTo IS NULL;

The previous UPDATE operation can benefit from an Expression Filter index defined on the Duties column of the Resource table.