This appendix describes examples of applications using the Expression Filter.
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 Section 11.2.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 Section 11.2.2, 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;
You can also use the join semantics of the EVALUATE operator to maintain complex N-to-M (many-to-many) relationships between data stored in multiple tables.
Consider an application that manages IT support resources based on the responsibilities (or duties) and the workload of each representative. In this application, you can capture the responsibilities of the representatives 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.