This discussion is archived
9 Replies Latest reply: Dec 28, 2012 4:42 AM by ranit B RSS

Sql Queries performance

RajeshKanna Newbie
Currently Being Moderated
Hi,

I am writing below two queries,to display the employee details having managers


select *
from emp e
where exists ( select 'x'
from emp e1
where e.empno = e1.mgr)

select ename
from emp
where empno in (select mgr
from emp)

Performance wise which query is faster.i am checking the explain plan both the queries showing same result.

can any one explain which one is better with proper reason?

Regards,
Rajesh
  • 1. Re: Sql Queries performance
    BluShadow Guru Moderator
    Currently Being Moderated
    What do you get if you look at the explain plan for both?

    When I run it on my database, the explain plans are identical.
  • 2. Re: Sql Queries performance
    BluShadow Guru Moderator
    Currently Being Moderated
    969052 wrote:
    Performance wise which query is faster.i am checking the explain plan both the queries showing same result.
    Ah, just seen you've already looked.

    In that case, the optimiser is determining that they are essentially the same and they will both likely perform the same.
  • 3. Re: Sql Queries performance
    RajeshKanna Newbie
    Currently Being Moderated
    most of the people said that correlated subqueries are more faster than normal subqueries.
    but here no differance in the explainplan
  • 4. Re: Sql Queries performance
    BluShadow Guru Moderator
    Currently Being Moderated
    969052 wrote:
    most of the people said that correlated subqueries are more faster than normal subqueries.
    but here no differance in the explainplan
    As with most things... it depends. Your example query is very basic, and easy for the optimiser to change for optimum performance. More complex queries it may not change as easily, and it also depends on the data on the tables (emp is a very small table and not suitable for performance testing) and the indexes and statistics available.
  • 5. Re: Sql Queries performance
    Karthick_Arp Guru
    Currently Being Moderated
    969052 wrote:
    most of the people said that correlated subqueries are more faster than normal subqueries.
    but here no differance in the explainplan
    IN and EXIST both uses SEMI-JOIN. So both does not make any difference. So you might think why have two statements. The reason is NOT IN and NOT EXIST are totally different thing. Thats because the way they IN and EXISTS process NULL are quiet different.
  • 6. Re: Sql Queries performance
    981337 Newbie
    Currently Being Moderated
    Hi,

    For Hierarchial relations better to use start with and connect by prior clause which have better performance,

    SELECT employee_id, last_name, manager_id
    FROM employees
    CONNECT BY PRIOR employee_id = manager_id
    order by manager_id;

    To get level
    SELECT employee_id, last_name, manager_id, LEVEL
    FROM employees
    CONNECT BY PRIOR employee_id = manager_id;

    Thanks!
  • 7. Re: Sql Queries performance
    BluShadow Guru Moderator
    Currently Being Moderated
    978334 wrote:
    Hi,

    For Hierarchial relations better to use start with and connect by prior clause which have better performance,
    That wasn't the OP's question though.
  • 8. Re: Sql Queries performance
    APC Oracle ACE
    Currently Being Moderated
    969052 wrote:
    Performance wise which query is faster.
    As is often the case with tuning questions, the answer is "it depends". Specifically it depends on the data volumes - especially the number of employees who are also managers - and the presence of indexes.

    If the number of distinct managers is small and the MGR column has an index it is likely that the IN sub-query will perform better. But if you have a highly vertical organisation where most people manage somebody it is very possible that the EXISTS will be faster.
    i am checking the explain plan both the queries showing same result.
    If you're using the canonical SCOTT.EMP table (or something similarly toy-sized) the small number of rows is unlikely to make a difference in execution times for different plans.

    Cheers, APC
  • 9. Re: Sql Queries performance
    ranit B Expert
    Currently Being Moderated
    IN and EXIST both uses SEMI-JOIN. So both does not make any difference.
    So, do u mean IN and EXIST always perform equally ?
    I didn't get this point exactly.
    The reason is NOT IN and NOT EXIST are totally different thing. Thats because the way they IN and EXISTS process NULL are quiet different.
    They both look for records not present in the table...right? So, then how it handles NULL differently.

    Could you please explain this??

    Thanks,
    Ranit B.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points