Given this simple SQL statement select e.emp_no, e.full_name from employee e where e.emp_no = coalesce(value1,value2 )
the plan varies depending on the following : 1. where e.EMP_NO = COALESCE(?, ? ) -- PLAN (E INDEX (RDB$PRIMARY7)) 2. where e.EMP_NO = COALESCE(2, ? ) -- PLAN (E INDEX (RDB$PRIMARY7)) 3. where e.EMP_NO = COALESCE(?, NULL ) -- PLAN (E INDEX (RDB$PRIMARY7)) 4. where e.EMP_NO = COALESCE(2, NULL ) -- PLAN (E INDEX (RDB$PRIMARY7)) 5. where e.EMP_NO = COALESCE(NULL, 2 ) -- PLAN (E INDEX (RDB$PRIMARY7)) 6. where e.EMP_NO = COALESCE(NULL, e.emp_no ) -- PLAN (E NATURAL) 7. where e.EMP_NO = COALESCE(2, e.emp_no ) -- PLAN (E NATURAL) 8. where e.EMP_NO = COALESCE(?, e.emp_no ) -- PLAN (E NATURAL) Examples 1 and 2 are correct. The index on EMP_NO exists, so use it. Example 3 is more interesting. The coalesce may evaluate to a value or to NULL. So even if NULL is evaluated there is no harm in choosing an index. Examples 4 and 5 will always evaluate to a value, so obviously an index should be used. Example 6 is interesting. As each row is selected emp_no will always evaluate to itself so the result set is the entire table. In this case a natural scan is correct. Example 7 certainly seems to have chosen the wrong plan. We already know the value of the expression when the statement is prepared. And examples 2, and 4 seem to prove that the optimiser is capable of evaluating the COALESCE during prepare and choosing an index. Example 8 could be considered correct if and only if ? evaluates to NULL. However, it seems to assume that the parameter will always be NULL and thus a natural scan should be used. However we have seen in examples 1 and 3 that it is equally capable of choosing an index when evaluating the coalesce even if the final evaluation may be null. And so here are the questions - is the plan produced in example 8 wrong or right ? - the above examples seem to lack coherence so how much is COALESCE evaluated during a prepare ? - should the optimiser assume that the first parameter will always resolve to a value - how does the optimiser consider COALESCE when this construct is used in a stored procedure? Paul -- Paul Reeves http://www.ibphoenix.com Supporting users of Firebird ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
