29/36

A Managing Expressions Defined on One or More Database Tables

An Expression column can store expressions defined on one or more database tables. These expressions use special elementary attributes called table aliases. The elementary attributes are created using the EXF$TABLE_ALIAS type, and the name of the attribute is treated as the alias to the table specified through the EXF$TABLE_ALIAS type.

For example, there is a set of expressions defined on a transient variable HRMGR and two database tables, SCOTT.EMP and SCOTT.DEPT.

hrmgr='Greg' and emp.job='SALESMAN' and emp.deptno = dept.deptno and
    dept.loc = 'CHICAGO'

Create the attribute set for this type of expression as shown in the following example:

BEGIN
  -- Create the empty Attribute Set --
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET('hrdb');
  
  -- Add elementary attributes to the Attribute Set --
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE('hrdb','hrmgr','VARCHAR2(20)');
 
  -- Define elementary attributes of EXF$TABLE_ALIAS type --
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE('hrdb','emp',
                                        EXF$TABLE_ALIAS('scott.emp'));
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE('hrdb','dept',
                                        EXF$TABLE_ALIAS('scott.dept'));
END;
/
 

The table HRInterest stores the expressions defined for this application. Configure the Expression column in this table as shown in the following example:

CREATE TABLE HRInterest (SubId number, Interest VARCHAR2(100));
 
BEGIN
  DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET('hrdb','HRInterest','Interest');
END;
/
-- insert the rows with expressions into the HRInterest table --

You can index the expressions that use one or more table alias attributes similar to those not using the table alias attributes. For example, the following CREATE INDEX statement configures stored and indexed attributes for the index defined on the Expression column:

CREATE INDEX HRIndex ON HRInterest (Interest) INDEXTYPE IS EXFSYS.EXPFILTER
  PARAMETERS ('STOREATTRS (emp.job, dept.loc, hrmgr)
               INDEXATTRS (emp.job, hrmgr)');

When you evaluate the expression, the values for the attributes defined as table aliases are passed by assigning the ROWIDs from the corresponding tables. You can evaluate the expressions stored in the HRInterest table for the data (rows) stored in EMP and DEPT tables (and a value of HRMGR) with the following query:

SELECT empno, job, sal, loc, SubId, Interest
   FROM emp, dept, HRInterest 
   WHERE emp.deptno = dept.deptno AND 
    EVALUATE(Interest, hrdb.getVarchar('Greg',emp.rowid,dept.rowid)) = 1;

You can add additional predicates to the previous query if the expressions are evaluated only for a subset of rows in the EMP and DEPT tables:

SELECT empno, job, sal, loc, SubId, Interest
   FROM emp, dept, HRInterest 
   WHERE emp.deptno = dept.deptno AND 
         emp.sal > 1400 AND 
       EVALUATE(Interest, hrdb.getVarchar('Greg',emp.rowid,dept.rowid)) = 1;