DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle 8.1.7 SQL Correlated Subqueries

  1. #1
    Join Date
    Jul 2003
    Location
    Pennsylvania, USA
    Posts
    2

    Oracle 8.1.7 SQL Correlated Subqueries

    I'm doing a query similar to this in structure, and it currently works fine in Oracle 8.1.7:

    Code:
    SELECT tableOne.name, tableTwo.nameAgain,
           (SELECT MIN(tableThree.createDate)
            FROM tableThree, tableFour
            WHERE tableThree.tableFourID = tableFour.tableFourID
            AND tableFour.nameType = 'Historical_Event'
            AND tableThree.tableTwoID = tableOne.tableTwoID) oldestEvent,
           (SELECT SUM(COUNT(DISTINCT tableFive.specialID))
            FROM tableFive, tableSix
            WHERE tableFive.tableSixID = tableSix.tableSixID
            AND tableSix.nameType = 'Open_Encounter'
            AND tableFive.tableFiveID = tableOne.tableFiveID
            GROUP BY tableFive.specialID) numOfEncounters
    FROM tableOne, tableTwo
    WHERE tableOne.statusID = tableTwo.statusID
    AND tableTwo.nameType = 'Status_Open'
    AND tableOne.name != 'system_account'
    ORDER BY tableOne.loginState DESC, tableOne.name
    
    
    The problem is, I tried adding another subquery to the select area that itself has a subquery, example (just the subquery that fits into the query above):
    (SELECT TO_CHAR(ABS(TRUNC(24*(firstDate - secondDate)))*60) +
            LTRIM(TO_CHAR(ABS(TRUNC(24*60*(firstDate - secondDate)) -
            (60*TRUNC(24*(firstDate - secondDate)))), '00')) minutesElapsed
     FROM
          (SELECT tableSeven.anotherID,
                  (SELECT NVL(MAX(tableEight.createDate), sysdate)
                   FROM tableEight, tableNine
                   WHERE tableEight.tableNineID = tableNine.tableNineID
                   AND tableNine.nameType = 'Special Chars'
                   AND tableEight.aBigID = tableSeven.aBigID) firstDate,
                  (SELECT tableTen.createDate
                   FROM tableTen, tableEleven
                   WHERE tableTen.tableElevenID = tableEleven.tableElevenID
                   AND tableEleven.nameType = 'Special Chars Again'
                   AND tableTen.aBigID = tableSeven.aBigID) secondDate,     
           FROM tableSeven
           WHERE tableSeven.someIDAgain = tableOne.someIDAgain
           AND tableSeven.createdDate > TO_DATE('07/01/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))
    But my tableOne 'outer query' reference is now two levels deep and Oracle complains.

    But, I've been told that this is horrible Oracle SQL syntax and should be recoded anyways.

    Problem is, in the Oracle documentation they talk about subqueries and correlated subqueries (Which I think I have since an inner query references an outer column) but none of them put the subqueries into the select portion (where it seems to just make sense).
    I just don't see how this can be combined into a single statement because the seperate subqueries are doing a lot of aggregate functions on their own.

    I could really use some help in this matter. How should I be writing this thing?

  2. #2
    Join Date
    Jul 2003
    Location
    Pennsylvania, USA
    Posts
    2
    Maybe I should simplify it better:
    I'm doing something like this:

    select e.ename, (select count(*) from dept where dep.deptno = e.deptno) theCout
    from emp e

    But I'm doing it more advanced, and several of them.
    It works fine as shown.

    But, if the subselect itself contains ANOTHER subselect, which references a column from a table from the main first statement, then it gives an error.

    See what I mean now?

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Horrible Oracle SQL syntax? I'd say it's nice but you misuse it horribly.
    Look at this:
    Code:
    select dept.deptno,
    (select count(*) from emp where emp.deptno=dept.deptno) TheCount
    from dept;
    
        DEPTNO   THECOUNT
    ---------- ----------
            10          2
            20          5
            30          5
            40          2
            25          0
    This is an example of using correlated subquery. Works fine, counts employees for each department. The drawback is that it need to execute 6 queries instead of just one. If I'd write such query, I'd get rid of subquery:
    Code:
    select dept.deptno, count(emp.deptno)
    from emp, dept
    where emp.deptno(+)=dept.deptno group by dept.deptno;
    
    
        DEPTNO COUNT(EMP.DEPTNO)
    ---------- -----------------
            10                 2
            20                 5
            25                 0
            30                 5
            40                 2
    One query, same results, readable code. Rewrite the query, do not use correlated subquery unless you really have to.
    I mean something like that, put outer joins where appropriate:
    Code:
    SELECT tableOne.name, tableTwo.nameAgain,
           MIN(tableThree.createDate) oldestEvent,
           SUM(COUNT(DISTINCT tableFive.specialID)) numOfEncounters
    FROM tableOne, tableTwo ,tableThree, tableFour, tableFive, tableSix
    WHERE tableOne.statusID = tableTwo.statusID
    AND tableTwo.nameType = 'Status_Open'
    AND tableOne.name != 'system_account'
    AND tableThree.tableFourID = tableFour.tableFourID
    AND tableFour.nameType = 'Historical_Event'
    AND tableThree.tableTwoID = tableOne.tableTwoID
    AND tableFive.tableSixID = tableSix.tableSixID
    AND tableSix.nameType = 'Open_Encounter'
    AND tableFive.tableFiveID = tableOne.tableFiveID
    GROUP BY tableOne.name, tableTwo.nameAgain
    ORDER BY tableOne.loginState DESC, tableOne.name
    Correlated subquery might be real performnace killer - just this week I came accross a similar query which had two-level correlated subquery. Executing on tables about 10,000 records took 20+ minutes. After rewriting in manner I suggested above, less than one second.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width