Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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
where (
(i='DAY' and d=to_char(sysdate, 'DY')
or
(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...

Hi.

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. :)

Cheers

Tim..

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.

Cheers

Tim...

DO NOT ask technical questions here, that's what my forum is for!

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)