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'

The attribute set for this type of expression is created 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. The Expression column in this table is configured 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 --

The expressions that use one or more table alias attributes can be indexed 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 the expression is evaluated, the values for the attributes defined as table aliases are passed by assigning the ROWIDs from the corresponding tables. The expressions stored in the HRInterest table can be evaluated 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;

Additional predicates can be added 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;