IDS 9.14, UDR's & the Optimizer

In a select statement with a WHERE clause that contains a reference to a user defined (datablade) C routine, e.g., a WHERE clause of the form: ... where (col1 = VALUE) or (UserDefinedCRoutine(...) = ...) will the engine always evaluate both sides of the "or", or will the optimizer arrange that the second clause is evaluated only if the value of the first clause is False? Will the answer depend on the declared cost of UserDefinedCRoutine? on whether UserDefinedCRoutine is defined as "not variant"? How does one ensure that the UserDefinedCRoutine is evaluated only if it really needs to be? Are the answers different depending on whether you're running IDS/UDO 9.14 or Informix 2000 (aka 9.2)? Thanks in advance,... Mike

Hi, Mike, Let's address what I think is the easiest question first: Will the answer depend ... on whether UserDefinedCRoutine is defined as "not variant"? 'not variant' routines may get executed during query compile time if all arguments passed to it are constants. The UDR result then replaces the UDR call in the query expression tree. The "How Queries Execute" tech note describes what happens during compile time ('not variant' routines get executed during phase 2): http://www.informix.com/idn-secure/DataBlade/Library/query_dispatch.htm Now, given this query: select .... from table1 t1, table2 t2 where (col1 = VALUE) or (UserDefinedCRoutine(...) = ... Quite a few things contribute to how it actually executes and in what order the predicates get called: - Availability of statistics for table1 and table2 - Availability of indices that support the predicates in the WHERE clause. Also: + 9.1 does not schedule a vii index scan for complex OR qualifications + 9.2 does schedule a vii index scan for complex OR qualifications - The cost of user-defined routines 9.2 implements expensive function optimization, which means that the optimizer reorders predicates so that expensive UDRs get evaluated last. See chapter 13 in the "Extending Informix Dynamic Server.2000" guide for new 9.2 options that let you specify routine cost. - I bet I missed some factors Taking the query above as an example, I can think of several scenarios. 1. If there are no indices, the server evaluates one predicate first. If it returns TRUE, the entire complex OR qualification is TRUE regardless of the second predicate's return value, so the server does not execute the second predicate. The second predicate only gets executed if the first one returns FALSE. (Now, which predicate actually gets executed first isn't guaranteed, but depends on the other factors, such as cost.) 2. If the two predicates execute on columns in different tables, the query could hypothetically take advantage of one index for each table in the FROM clause. So, if there were an index on col1 and a functional index on UserDefinedCRoutine(...), the server will do two index scans simultaneously, then do a union operation. 3. If the two predicates execute on columns in the same table, it could hypothetically use a 2-column index. If it is a multi-column vii index, #1 occurs in 9.1 (optimizer does not schedule an index scan). In 9.2, the optimizer does schedule a vii index scan, and it is up to the vii method to evaluate the entire complex OR qualification. Finally, I think the best way to ensure that UserDefinedCRoutine() only gets executed if it really needs to be is to set a high cost. 9.2 will delay its execution so that it gets called on the fewest intermediate results. -jean Mike & Lynda Dunham-Wilkie wrote: > In a select statement with a WHERE clause that contains a reference to a > user defined (datablade) C routine, e.g., a WHERE clause of the form: > > ... where (col1 = VALUE) or (UserDefinedCRoutine(...) = ...) > > will the engine always evaluate both sides of the "or", or will the > optimizer arrange that the second clause is evaluated only if the value > of the first clause is False? > > Will the answer depend on the declared cost of UserDefinedCRoutine? on > whether UserDefinedCRoutine is defined as "not variant"? How does one > ensure that the UserDefinedCRoutine is evaluated only if it really needs > to be? Are the answers different depending on whether you're running > IDS/UDO 9.14 or Informix 2000 (aka 9.2)? > > Thanks in advance,... > Mike