More slow cases
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' )) endas 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…)