Oracle10g EVALUATE Operator
The EVALUATE operator is used to evaluate an Expression set for
a given data item. The Expressions to be evaluated must be stored
in a column of Expression datatype, which is created by
associating an attribute set to a VARCHAR2 column in a user table,
as we saw in the previous sections.
Format for EVALUATE
EVALUATE (expression_column,
<dataitem>)
<dataitem> := <varchar_dataitem> | <anydata_dataitem>
<varchar_dataitem> := attribute_name => attribute_value
{, attribute_name => attribute_value}>
<anydata_dataitem> := AnyData.convertObject(attribute_set_instance)
|
KEYWORDS AND PARAMETERS |
DESCRIPTIONS |
|
expression_column |
Name of the column storing the
expressions. |
|
attribute_name |
Name of an attribute from the
corresponding attribute set. |
|
attribute_value |
Value for the attribute. |
|
attribute_set_instance |
Instance of the object type
associated with the corresponding attribute set. |
Table 6.1 EVALUATE Keywords and Attributes
The EVALUATE operator returns 1 for an expression that
evaluates to true for the data item and 0 otherwise.
The EVALUATE operator is used in the WHERE clause of a standard
SQL query or DML (UPDATE or DELETE) statement. The name-value
pairs for a data item can be generated using the getVarchar()
method, defined for the object type associated with the
corresponding attribute set.
When an Expression Filter index is defined on a column storing
expressions (only in Enterprise versions), the EVALUATE operator
on such a column may use the index for the expression set
evaluation based on its usage cost. The EVALUATE operator can be
used as a join predicate between a table storing expressions and a
table storing corresponding data items.
The VARCHAR form of data item cannot be used for an attribute
set with one or more binary typed attributes. For example, if one
of the attributes is of CLOB type or an object type (embedded
object), the AnyData form of the data item should be used.
Related views:
- user_expfil_attribute_sets
- user_expfil_attributes
- user_expfil_expression_sets
Some example queries against these views are:
SQL> select * from
user_expfil_attribute_sets;
ATTRIBUTE_SET_NAME
--------------------
BOAT4SALE
BOAT4SALE2
SQL> select * from user_expfil_attributes;
ATTRIBUTE_SET_NAME
ATTRIBUTE DATA_TYPE ASSOCIATED_TABLE
-------------------- ------------ ---------------
----------------
BOAT4SALE MODEL
VARCHAR2(20)
BOAT4SALE YEAR
NUMBER
BOAT4SALE PRICE
NUMBER
BOAT4SALE ENGINEHOURS
NUMBER
BOAT4SALE2 MODEL
VARCHAR2(20)
BOAT4SALE2 YEAR
NUMBER
BOAT4SALE2 PRICE
NUMBER
BOAT4SALE2 ENGINEHOURS
NUMBER
8 rows selected.
SQL> col expr_table format a10
SQL> col expr_column format a12
SQL> column attribute_set format a15
SQL> select * from user_expfil_expression_sets
EXPR_TABLE EXPR_COLUMN
ATTRIBUTE_SET LAST_ANAL NUM_EXPRESSIONS PREDS_PER_EXPR
NUM_SPARSE_PREDS
---------- ------------ --------------- ---------
--------------- -------------- ----------------
BOATER INTEREST BOAT4SALE
30-AUG-03 3 2 0
Let's look at an example using the EVALUATE operator.
|
