0 Replies Latest reply: Nov 30, 2010 8:27 AM by Centinul RSS

    Expression Filter Performance Issues / Misuse?

    Centinul
      I'm currently evaluating the Expression Filter functionality for a new requirement. The basic idea of the requirement is that I have a logging table that I want to get "interesting" records from. The way I want to set it up is to exclude known, "uninteresting", records or record patterns.

      So as far as an implementation I was considering a table of expressions that contained expression filter entries for the "uninteresting" records and checking this against my logging table using the EVALUATE operator and looking for a 0 result.

      In my testing I wanted to return results where the EVALUTE operator is equal to 1 to see if my expressions are correct. In doing this I was experiencing significant performance issues. For example my test filter matches 72 rows out of 61657 possible entries. It took Oracle almost 10 minutes to evaluate this expression. I tried it with and without an Expression Filter index with no noticeable change in execution time. The test case and query is provided below.

      Is this the right use case for Expression Filter? Am I misunderstanding how it works? What am I doing wrong?

      Test Case:

      Version
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      Objects & Query:
      CREATE TABLE expressions( white_list VARCHAR2(200));
      
      CREATE TABLE data
      AS
      SELECT OBJECT_ID
           , OWNER
           , OBJECT_NAME
           , CREATED
           , LAST_DDL_TIME
      FROM   DBA_OBJECTS
      ;
      
      BEGIN
        -- Create the empty Attribute Set --
        DBMS_EXPFIL.CREATE_ATTRIBUTE_SET('exptype');
       
        -- Define elementary attributes of EXF$TABLE_ALIAS type --
        DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE('exptype','data',
                                              EXF$TABLE_ALIAS('test_user.data'));
      END;
      /
      
      BEGIN
        DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET('exptype','expressions','white_list');
      END;
      /
      
      INSERT INTO expressions(white_list) VALUES('data.owner=''TEST_USER'' AND data.created BETWEEN TO_DATE(''08/03/2010'',''MM/DD/YYYY'') AND TO_DATE(''08/05/2010'',''MM/DD/YYYY'')');
      
      exec dbms_stats.gather_table_stats(USER,'EXPRESSIONS');
      exec dbms_stats.gather_table_stats(USER,'DATA');
      
      CREATE INDEX expIndex ON Expressions (white_list) INDEXTYPE IS EXFSYS.EXPFILTER
        PARAMETERS ('STOREATTRS (data.owner,data.object_name,data.created)
                     INDEXATTRS (data.owner,data.object_name,data.created)');
      
      SELECT /*+ gather_plan_statistics */ data.* FROM data, expressions WHERE EVALUATE(white_list,exptype.getVarchar(data.rowid)) = 1;
      
      
      DROP TABLE expressions PURGE;
      
      BEGIN
              DBMS_EXPFIL.DROP_ATTRIBUTE_SET(attr_set => 'exptype');
      END;
      /
      
      DROP TABLE data PURGE;