|Oracle® Database Application Developer's Guide - Expression Filter
10g Release 1 (10.1)
Part Number B10821-01
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 could monitor changes to data in a database table and react to these changes accordingly.
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
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;
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.
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);
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