Results 1 to 10 of 10

Thread: Optimize SQL

  1. #1
    Join Date
    Aug 2009
    Posts
    17

    Unhappy Unanswered: Want to avoid union to Optimize SQL.. but not able to do this

    Dear All,

    I have following View.
    Which I am using in my SQL.
    Can anyone optimize this sql so that Union can be removed?


    SELECT ename,
    dname
    max(sal)
    FROM emp,dept
    WHERE ( emp.status = 'Confirmed' AND emp.inactive_ind = 'N' )
    AND ( emp.deptno = dept.deptno )
    group by ename, dname
    union
    SELECT ename,
    dname
    max(sal)
    FROM emp,dept
    WHERE ( emp.status <> 'Confirmed' AND emp.inactive_ind = 'N' )
    AND ( emp.deptno = dept.deptno )
    group by ename, dname
    Last edited by ssutar; 04-05-10 at 08:16.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ssutar View Post
    Which I am using in my SQL.
    Can anyone optimize this sql so that Union can be removed?
    Not tested:
    Code:
    SELECT ename,
           dname
           max(sal)
    FROM  emp, dept
    WHERE ( emp.deptno = dept.deptno )
      AND ( 
            (emp.status = 'Confirmed' AND emp.inactive_ind = 'N' ) OR  
            (emp.status <> 'Confirmed' AND emp.inactive_ind = 'N') 
          )
    GROUP BY ename, dname

  3. #3
    Join Date
    Aug 2009
    Posts
    17

    Optimise SQL

    Thanks shammat,

    Can you please check the execution plans.
    I think Query 1 execution plan is quite better than query 2.

    I know this but am not much confident (as I am too new to this technology). Please confirm.


    query 1. When using your provided solution
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1674 Card=22805 Bytes=524515)

    1 0 HASH (GROUP BY) (Cost=1674 Card=22805 Bytes=524515)
    2 1 HASH JOIN (Cost=1510 Card=22805 Bytes=524515)
    3 2 TABLE ACCESS (FULL) OF 'emp' (TABLE) (Cost=2 Card=24 Bytes=168)
    4 2 TABLE ACCESS (FULL) OF 'dept' (TABLE) (Cost=1 507 Card=24705 Bytes=395280)


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    6836 consistent gets
    530 physical reads
    0 redo size
    153713 bytes sent via SQL*Net to client
    5517 bytes received via SQL*Net from client
    457 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    6840 rows processed

    Query 2. My existing SQL
    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3334 Card=23046 Bytes=530058)
    1 0 SORT (UNIQUE) (Cost=3334 Card=23046 Bytes=530058)
    2 1 UNION-ALL
    3 2 HASH (GROUP BY) (Cost=1508 Card=868 Bytes=19964)
    4 3 NESTED LOOPS (Cost=1505 Card=2618 Bytes=60214)
    5 4 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=7)
    6 5 INDEX (UNIQUE SCAN) OF 'indx1' (INDEX (UNIQUE)) (Cost=1 Card=1)
    7 4 TABLE ACCESS (FULL) OF 'dept' (TABLE) (Cost=1504 Card=2618 Bytes=41888)
    8 2 HASH (GROUP BY) (Cost=1826 Card=22178 Bytes=510094)
    9 8 HASH JOIN (Cost=1509 Card=22178 Bytes=510094)
    10 9 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=24 Bytes=168)
    11 9 TABLE ACCESS (FULL) OF 'dept' (TABLE) (Cost=1506 Card=24026 Bytes=384416)

    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    6838 consistent gets
    1103 physical reads
    0 redo size
    152390 bytes sent via SQL*Net to client
    5517 bytes received via SQL*Net from client
    457 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    6840 rows processed

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ssutar View Post
    Thanks shammat,

    Can you please check the execution plans.
    I think Query 1 execution plan is quite better than query 2.
    Correct, as it only does two full table scans, whereas the other includes an additional index scan and does a full table scan on emp two times.

    Depending on how many employees have emp.inactive_ind = 'N' an index on inactive_ind might improve performance.

    Please use [code] tags when posting execution plans and SQL statements.
    It makes reading that stuff a lot easier (and will presever indention)

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Aren't the 2 queries above pretty much the same as

    Code:
    SELECT ename,
           dname,
           max(sal)
    FROM  emp, dept
    WHERE ( emp.deptno = dept.deptno )
      AND emp.inactive_ind = 'N' 
      AND emp.status IS NOT NULL
    GROUP BY ename, dname
    Asking for emp_status = 'Confirmed' or emp_status != 'Confirmed' ... pretty much everything ... only leaves out those records where emp_status is null (it's like saying, bring me every pen in the building that writes in blue, or any color that's not blue).

    And since both of those conditions also require that inactive_ind = 'N', there's no need to separate that piece of criteria out.

    Maybe what you want is to add the status into the WHERE clause?

    Code:
    SELECT ename,
           dname,
           emp.status,
           max(sal)
    FROM  emp, dept
    WHERE ( emp.deptno = dept.deptno )
      AND emp.inactive_ind = 'N' 
      AND emp.status IS NOT NULL
    GROUP BY ename, dname

    Otherwise I'm having a tough time understanding what you're trying to answer with this query.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuck_forbes View Post
    Aren't the 2 queries above pretty much the same as

    Asking for emp_status = 'Confirmed' or emp_status != 'Confirmed' ... pretty much everything ... only leaves out those records where emp_status is null (it's like saying, bring me every pen in the building that writes in blue, or any color that's not blue).
    I agree
    I realized that after I posted my solution.
    I simply copied and pasted the original criterias from the UNION into the OR condition but didn't really look into the values

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    You answered the question spot-on, I was addressing the OP. Your assistance on the Forum is always appreciated, shammat.

    Something else that comes up in that original query with the UNION, since you're asking for mutually exclusive sets of data, you should go with a UNION ALL instead of a UNION, before comparing performance.

    --=Chuck

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes View Post
    Aren't the 2 queries above pretty much the same as

    Code:
    SELECT ename,
           dname,
           max(sal)
    FROM  emp, dept
    WHERE ( emp.deptno = dept.deptno )
      AND emp.inactive_ind = 'N' 
      AND emp.status IS NOT NULL
    GROUP BY ename, dname
    More like
    Code:
    SELECT ename,
           dname,
           case emp.status case 'Confirmed' then 'Confirmed' else 'Not confirmed' end,
           max(sal)
    FROM  emp, dept
    WHERE ( emp.deptno = dept.deptno )
      AND emp.inactive_ind = 'N' 
      AND emp.status IS NOT NULL
    GROUP BY ename, dname, 
    case emp.status case 'Confirmed' then 'Confirmed' else 'Not confirmed' end

  9. #9
    Join Date
    Aug 2009
    Posts
    17
    Shammat,

    Thanks for sujetions.
    In future I will post [code] tags.


    Chuck Forbes

    Yes, it wd b difficult to understand the purpose of the query.


    Query is looking for maximum salary of the confirmed employee
    as well as maximum out of remaining employees like Temporary, Retired, Probation etc.


    I'll implement your suggestions to Inactive_ind

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    Looks like n_j's solution is the one you need then, using a CASE statement to differentiate between the 2 groups. --=cf

Posting Permissions

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