2 Replies Latest reply: Dec 7, 2006 8:14 AM by 516251 RSS

    10g Expression Filter

    487456
      We are trying to use expression filters to match rows with rules. Table A contains transactions [~ 1.5 million rows], Table B contains the rules [testing with 5 rules, we expect to have a few hundred to a thousand rules]. SQL queries do a cartesian join between table A and table B using the evaluate function to identify all rules that a transaction satisfies. While the process works as expected it is extremely slow. We see a lot of time being spent in the <attribute_set>.dynamiceval function. We also used to see a significant amount of time spent in the getVarchar() function but we got around that by creating the name-value pair string of parameters in the sql itself using the to_char function and the concatenation operator. There is no infomation in the manuals -- App Dev or Performance Tuning -- on optimizing queries using evaluate. We understand that indexing the criteria can provide some benefits and we have experimented with that but we are too slow [1 row per second on a lightly loaded 4-cpu hp superdome with 8gb of memory] with even a single rule in our rules table. All tables are appropriatley analyzed. We had also observed that the plan chosen by the optimizer would use a nested loop between tables A and B with the rules table as the driver full scanning the transaction table for every rule. We hinted this behavior away.

      I will be happy to share the test code if someone with experience on this feature wants to take a look at it to offer an opinion.

      Thanks.
        • 1. Re: 10g Expression Filter
          442199
          Hello,

          When the EVALUATE operator is used as a join predicate, an ORDERED hint may be required to force a nested-loop join with the table storing expressions as the inner table. This is a side-effect from the extensible indexing behavior. Please refer to article "Filter Data Before You Get it" under "Code listing 9" in the May'05 issue of Oracle magazine for exact syntax.

          http://www.oracle.com/technology/oramag/oracle/05-may/o35sql.html

          Since the data used for the expression evaluation is from another table, you may want to use a table alias attribute in the attribute set(using EXF$TABLE_ALIAS).

          Please let me know if you need additional help.

          Thanks,
          -Aravind.
          • 2. Re: 10g Expression Filter
            516251
            I'd love to see how you are doing this. I am evaluating the expression filter feature and your approach sounds interesting. I'm trying to figure out how it could be used to match multiple event/attributes on multiple tables.

            Greg