8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Dynamic Binds 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
where (
(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

Tim... said...


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.



DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.