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;