|Dynamic Binds Using Query Transformation - Simplify dynamic variable binds within dynamic SQL using query transformation.|
michaelallen said...this would work nice, except that ORACLE doesnt guarantee that it will always short-circuit a where clause.
michaelallen said...we have seen proof that if the optimizer feels it best, it will evaluate parts of your where clause you never thought it should get to, because it the long run it thought it would be better.
michaelallen said...for exameple, this table:
create table a (i varchar2(10), d varchar2(10));
insert into table a('DATE', '20120101');
insert into table a('DAY', 'MON');
select * from a
(i='DAY' and d=to_char(sysdate, 'DY')
(i='DATE' and to_date(d, 'YYYYMMDD')=trunc(sysdate))
depending on size of table, might still evaluate to_date(d, 'YYYYMMDD') for EVERY row, even if i='DAY' is true
This is not runtime short-circuit evaluation. This is query rewrite at parse time. "1=1" is known at parse time. Your comparisons are not. Very different situations. You misunderstood the point of the article.
Oracle use this method internally. It is recommended by the likes of Tom Kyte and Bryn Llewellyn. It will be valid longer than your code will. :)
Tim... said...I added a note specifically to emphasize that this is not runtime short-circuit evaluation of the WHERE clause, in case any other people misunderstood the technique in the same way.
It definitely works. You can count on it.