This content has been marked as final. Show 1 reply
Expression Filter feature uses Java stored procedures to parse the conditional expressions stored in the table (CREATE INDEX implementation). It does not use Java for expression evaluation (EVALUATE operator). The runtime behavior is implemented in C and PL/SQL. So, java pool size and other parameters may not help you improve the performance.
You said that the performance is relative to the number of expressions. Could you comment on the selectivity of the expressions ? That is, as you increase the number of expressions in the set, how is the result set size for the query with EVALUATE operator. Note that the query performance is expected to be relative to the result set size when an index is used (especially because there will be as many I/Os for fetching the corresponding rows from the table).
You mentioned that the Expression Filter index is created for the column storing expressions. Could you please check the execution plan for the query with EVALUATE operator and confirm that the index is used ? If the index is being used for the query, could you please collect statistics on the predicate table (obtained from PREDICATE_TABLE column of the USER_EXPFIL_INDEXES view) and send them to me? Given your expression set, the EXF$SPARSEPRED column of the predicate table should have no values. If this not the case, then the index is not created using the expression statistics.
The anydata vs getVarchar note was based on some in-house testing. For attribute sets with a few attributes, we are able to confirm this. But we realize that there are many other factors that impact the performance. For example, the getVarchar performance degrades as the number of attributes in the attribute set increases. We have removed the recommendation about getVarchar in the latest version of the doc.