Oracle10g Example use of the EVALUATE Operator
The following example query uses the VARCHAR form of data item
generated by the getVarchar( ) function:
SQL> SELECT * FROM Boaters WHERE
2 EVALUATE (Boaters.Interest,
3 Boat4Sale(’Sea ray’, 2002, 9000, 0).getVarchar
()) = 1;
BID ZIPCODE PHONE INTEREST
---- ------- ------------
----------------------------------------------------
1 32611 917 768 4633 Model=>'Sea ray' and
Price<10000 and EngineHours<250
For the previous query, the data item can be passed in the
AnyData form with the following syntax:
SQL> SELECT * FROM Boaters WHERE
EVALUATE (Boaters.Interest,
AnyData.convertObject (
Boat4Sale (’Sea ray’,2002,9000,0))) = 1;
BID ZIPCODE PHONE INTEREST
---- ------- ------------
----------------------------------------------------
1 32611 917 768 4633 Model=>'Sea ray' and
Price<10000 and EngineHours<250
When a large set of Expressions are stored in a table, the
table storing the Expressions can be joined with the table storing
data items to be evaluated. For example, let's say we have a used
boats table that looks like so:
SQL> DESC USED_BOATS
Name Datatype
--------- ----------
SELLER_ID NUMBER
MODEL VARCHAR2(20)
YEAR NUMBER
PRICE NUMBER
ENGINEHOURS NUMBER
LENGTH NUMBER
We could compare our list of Boaters and their expressed
desires to our list of used boats using the following syntax:
SELECT u.seller_id, b.bid, b.Phone
FROM Boaters c, Used_boats u
WHERE
EVALUATE (b.Interest,
Boat4Sale(u.Model, u.Year, u.Price, u.EngineHours).getVarchar()) =
1
ORDER BY u.seller_id;
Of course, if we have a bunch of data and a bunch of
Expressions, the comparisons can get pretty complicated and
performance can suffer. To help with this, the Expressions can be
indexed. Lets look at the concept of the Expression Index next. |
