1 / 15

Expression Filters (10 g )

Expression Filters (10 g ). What is an expression filter ? Feature to manage and evaluate conditional expressions that describe users interests in data Allows you to store conditional expressions in a column, which you can then use in the WHERE clause of a database query

lok
Download Presentation

Expression Filters (10 g )

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Expression Filters (10g) • What is an expression filter ? • Feature to manage and evaluate conditional expressions that describe users interests in data • Allows you to store conditional expressions in a column, which you can then use in the WHERE clause of a database query • Expression Filter includes: • Expression datatype, • SQL EVALUATE operator, • Expression indexing(Enterprise Edition only) • Administrative utilities

  2. Expression Filters (10g) • The Expression Attribute Set • Defined as special Oracle Object Type that carries all valid variables for the set • Implicitly includes all the built-in functions • Possible to add user-defined functions • May contain XPath predicates defined on XMLType attributes • Expressions must adhere to SQL Where Clause format • Subqueries are not allowed! • Not required to use all the defined elementary attributes (null is acceptable) • Two ways to create an attribute set • use dbms_expfil.create_attribute_set in combination with dbms_expfil.add_elementary_attribute (automatically creates object) • use an existing object type to create an attribute set dbms_expfil.create_attribute_set(attr_set => 'car4sale',from_type => 'yes'))

  3. Expression Filters (10g) • Regular Expression datatype • Always a varchar2 column (converted into column of expression datatype when assigning expression attribute set to it) • How to define Expression column ? assign an attribute set to varchar2 column and it will create an expression column • The attribute set determines which elementary attributes an user-defined functions can be used in the expression set • Possible to create multiple columns of Expression datatype in the same table and in other tables in the same schema • Checked for correct syntax and constrained to use attributes and functions defined in the corresponding attribute set • Expression data can be loaded using SQL-loader!

  4. Expression Filters (10g) • Basic steps to create and use an expression column • Define an attribute set • Create user table with expression column (or add new varchar2 column to existing table) • Assign attribute set to the expression column in the table • Insert expressions in the table • Apply the SQL EVALUATE operator • Applying the SQL evaluate Operator • 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.

  5. Steps to set up Expression Filters • Create an object type as source for the Expression Attribute Set • Defining an attribute set based on the type • CREATE OR REPLACE TYPE Car4Sale AS OBJECT • ( Model VARCHAR2(20) • , Year NUMBER • , Price NUMBER • , Mileage NUMBER); • BEGIN • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET ( attr_set => 'Car4Sale‘ • , from_type => 'YES‘ • ); • END;

  6. Steps to set up Expression Filters • Create user table with expression column • Assign attribute set to the expression column in the table • CREATE TABLE Consumer (Id NUMBER • , Zipcode NUMBER • , Phone VARCHAR2(12) • , Interest VARCHAR2(200) • ); • BEGIN • DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET ( attr_set => 'Car4Sale' • , expr_tab => 'Consumer' • , expr_col => 'Interest' • ); • END;

  7. Steps to set up Expression Filters • Insert expressions into expression column • INSERT INTO Consumer VALUES (1, 32611, '917 768 4633‘,'Model=''Taurus'' and Price < 15000 and Mileage < 25000'); • INSERT INTO Consumer VALUES (2, 03060, '603 983 3464‘,'Model=''Mustang'' and Year > 1999 and Price < 20000');

  8. Expression Filters (10g) • The SQL EVALUATE operator has two arguments: • the name of the column storing the expressions • the data item to which the expressions are compared. • In the data item argument, values MUST provided for all elementary attributes in the attribute set associated with the expression column. Null is an acceptable value. • Sample Queries • Data Item Formatted as a String: • SELECT * FROM Consumer WHERE • EVALUATE (Consumer.Interest, • 'Model=>''Mustang'', • Year=>2000, • Price=>18000, • Mileage=>22000‘ ) = 1;

  9. Expression Filters (10g) • Batch Evaluation of Expressions • For each inventory item,return the number of consumers whose Car interest matches the item • SELECT DISTINCT Inventory.Model, count(*) as Demand • FROM Consumer, Inventory • WHERE EVALUATE ( Consumer.Interest, • , Car4Sale.getVarchar ( Inventory.Model • , Inventory.Year • , Inventory.Price • , Inventory.Mileage • ) ) = 1 • GROUP BY Inventory.Model • ORDER BY Demand DESC

  10. Expression Filters (10g) • Application Example: list all consumers with an interest in the newly inserted (or updated) inventory item – and contact them? • CREATE TRIGGER activechk AFTER insert OR update ON Inventory • FOR EACH ROW • DECLARE • cursor c1 (ditem VARCHAR2) is • SELECT c.id, c.phone FROM Consumer c WHERE EVALUATE (c.Interest, ditem) = 1; • ditem VARCHAR2(200); • BEGIN ditem := Car4Sale.getVarchar ( :new.Model, :new.Year , :new.Price, :new.Mileage); • for cur in c1(ditem) loop • DBMS_OUTPUT.PUT_LINE (' For Model '||:new.Model||' Call '||cur.Id||'@'||cur.phone); • end loop; • END;

  11. Typical Usage of Expression Filters • To screen incoming data • Find matches with expressed interests or conditions • We have found an item that may be exactly what you’re looking for • A suspect has just entered the country • Find non-matches • This new piece of data does not meet one of (y)our standards • This record does not adhere to this business rule • To screen existing data for new interests, conditions, standards or rules • Because of this new EU regulation, we have to redesign these products…

  12. Business Rule example Deptno Emp • CREATE OR REPLACE TYPE BusinessRuleConditions AS OBJECT • ( Condition1 VARCHAR2(20) • , Condition2 VARCHAR2(20) • , Condition3 VARCHAR2(20) • , Condition4 VARCHAR2(20) • , Condition5 VARCHAR2(20) • ); deptno dname loc empno ename job sal Business Rules • BEGIN • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET • ( attr_set => 'BusinessRuleConditions' • , from_type => 'YES' • ); • END; label table msg expression EMPDEPTEMPDEPTBONUS EMP1DPT1EMP2DPT2BOS1 001011002012021 • BEGIN • DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET • ( attr_set => 'BusinessRuleConditions' • , expr_tab => 'Business_Rules' • , expr_col => 'br_expression' • ); • END;

  13. Inserting the business rules as Expressions Deptno Emp deptno dname loc empno ename job sal • INSERT INTO Business_rules VALUES ('EMP1', '001','EMP', 'Condition1 =''SALESMAN'' or Condition2 is null'); • INSERT INTO Business_rules VALUES ('EMP2', '002','EMP', '(sysdate - to_date(condition3)) <8500');INSERT INTO Business_rules VALUES ('DPT1', '001','DEPT', 'Condition2 <>''SALES'' or Condition3 <> ''PARIS'' '); Business Rules label table msg expression EMPDEPTEMPDEPTBONUS EMP1DPT1EMP2DPT2BOS1 001011002012021 Condition1 =''SALESMAN'' or Condition2 is nullCondition2 <>''SALES'' or Condition3 <> ''PARIS''(sysdate - to_date(condition3)) <8500012021

  14. Find violations of the Business Rules • create or replace type • ename_type as table of varchar2(30) • / • SELECT br.br_expression Business_Rule • , count(e.empno) • , cast( collect(ename) as ename_type ) enames • FROM Business_Rules br • , Emp e • WHERE EVALUATE • ( br.br_expression • , BusinessRuleConditions.getVarchar • ( e.job • , e.comm • , e.hiredate • , e.deptno • , e.mgr • ) • ) = 0 • AND br.br_tbl = 'EMP' • GROUP BY br.br_expression • BUSINESS_RULE • COUNT(E.EMPNO) • ENAMES • -------------------- (sysdate - to_date(condition3)) <8500 • 5 • ENAME_TYPE('SMITH', 'ALLEN', 'WARD', 'JONES', 'BLAKE')

  15. Find violations of the Business Rules • SELECT br.br_expression Business_Rule • , count(d.deptno) • , cast( collect(dname) as ename_type ) dnames • FROM Business_Rules br • , Dept d • WHERE EVALUATE • ( br.br_expression • , BusinessRuleConditions.getVarchar • ( d.deptno • , d.dname • , d.loc • , null • , null • ) • ) = 0 • AND br.br_tbl = 'DEPT' • GROUP BY br.br_expression

More Related