More slow cases
February 17th, 2006 by Peter Scott
Yesterday I had a small rant about the use of case statements in where clauses. In a reply to this parkerpg suggested this was perhaps generated by the use of query environment such as Cognos. In this case we are not using Cognos but I think the principle holds good and we are seeing inappropriate use of ‘drag and drop’ (or is it droop?) reporting tools
Still, once you see one example you are on a roll and find other examples in slow running queries. Today’s example is
case
when upper(( ’160206′ )) = ‘TODAY’
then trunc(sysdate)
when upper(( ’160206′ )) = ‘YESTERDAY’
then trunc(sysdate) – 1
when length(trim(translate(( ’160206′ ),’+-.0123456789′,’ ‘))) is null
then sysdate – to_number(( ’160206′ ))-1
else
to_date(( ’160206′ ))
end
as one of the date values in a between predicate. I can suspect that the constant ‘160206’ is supplied as an argument to a prompt window. So if the user enters TODAY it is translated to sysdate and so on.
Did you know that 160206 days ago it was 22nd June 1567? And that the user’s query brings back a tad too much data (well it would if it completed…)
