Re: Calculated Item which includes a sub query

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 15 Aug 2003 00:34:29 -0700
Message-ID: <1a75df45.0308142334.5d81cb90@posting.google.com>

"The Chad" <funkstylin_at_hotmail.com> wrote

> In Cognos reports I often create items which use IF-THEN-ELSE logic and
> include a sub query.
> e.g. IF (employee# in (Dataset)) THEN 'Good' ELSE 'Bad' .... where Dataset
> relates to another report where employee# is returned.
>
> Now I want to do the same kind of thing in Oracle SQL.

Yep. You can use DECODE or CASE.

> I thought using the DECODE function may have worked, but it doesnt seem to
> allow a subquery to be used inside it.

No - that is not allowed. Also will be potentially a huge performance problem. Imaging processing a million rows and aggregating it to something like a 100 rows.

If you add a subquery like that in the SELECT, it means that for *every* single row in that million row dataset, this subquery will execute. Now you have a million subselects on your hand. Even if that subselect only runs in 1ms (very unlikely), it will add over 15 minutes of runtime to you query. More likely you will be looking at an hour+ increase in runtime.

The number #1 assumption in SQL and performance FUBARs? The data volume is small, and will remain small. Almost every single slow performing SQL I have ever dealt with in Production is because of that assumption - it was fast when the developer tested it with his 1000 row mini dataset.. so it should work fast on Production too..

> e.g. decode (employee#, (select employee#, perform_review from employee
> where award = 'GEN'), TO_CHAR(perform_review), 'not found') AWARD
> So, if the employee# is found in the subquery, return the corressponding
> date (perform_review field) or else return the text 'not found'.
>
> Any ideas how I can build this kind of item in Oracle SQL ?

In Oracle this will look something like this:

SELECT

  DECODE( award,            // if award
      NULL, 'not found,     //   is null then return 'not found'
      'abc', 'brilliant',   //   is abc  then return 'brillant' 
      perform_rev )         //   else return column perfom_rev

FROM employee

The CASE expression allows a more clearer IF-THEN-ELSE definition. The Oracle SQL Reference Manual explains the syntax and usage.

The thing to remember is not to have the IF-THEN-ELSE logic looking up data (as in doing subqueries). Even if possible, because of the performance impact. Very often you will need multiple IF-THEN-ELSE expressions accessing the same row's columns. If that row is retrieved with a subquery, you will have mutiple subqueries retrieving the _same_ row (different columns) in each of the IF-THEN-ELSE expressions.

Instead, structure the SQL in such a manner that _all_ the relevent data is available (using joins) when applying the IF-THEN-ELSE logic.

Consider the projection of the SELECT SQL (the part between the SELECT and FROM keywords we used to call the SQL Projection in the old days) as the inside of a FOR loop, e.g.

FOR row in data set
column expressions <- the SQL projection END How do you make FOR loops fast? By keeping the inside of the loop as quick and tight as possible. This also applies to SQL statements (keeping in mind that the impact of the <FROM table> and <WHERE clause> must also be considered).

--
Billy

Received on Fri Aug 15 2003 - 02:34:29 CDT