Forcing predicate order in SQL

Forcing query predicate order can be very useful if Oracle seems to be making the wrong decision on evaluating the cost of each predicate. You can read an article on the default order of predicate evaluation here.

For example,

    select count(*) 
    from   my_table 
    where  simple_col = 123
    and    plsql_function(other_column) = 'YES';

To influence the order of predicate evaluation, you may feel inclined to use an inline view.

    select count(*)
    from (
      select * 
      from   my_table 
      where  simple_col = 123
    )
    and    plsql_function(other_column) = 'YES';

However, Oracle may choose to push your outer predicate into the inline view anyway - to avoid this, put something in the inline view to force it to be evaluated in isolation. For example

    select count(*)
    from (
      select * 
      from   my_table 
      where  simple_col = 123
      and    rownum > 0
    )
    and    plsql_function(other_column) = 'YES';

Using the rownum predicate prevents Oracle from pushing the PL/SQL predicate into the inline view

All of this drama is removed once you migrate to Oracle 8i, where you can use the ORDERED_PREDICATES hint in SQL.

See the original newsgroup post here