Help on SQL needed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Help on SQL needed

  1. #1
    Join Date
    May 2001
    Posts
    17
    I have a query like this :

    select * from emp_salary a, department b
    where location = 3
    and b.deptid= 10
    and a.deptid = b.deptid;

    Which condition gets executed first? the one with location =3 or a.deptid=b.deptid or b.deptid=10 ???

  2. #2
    Join Date
    Apr 2001
    Posts
    14
    location=3 first and then in that order
    Anil
    anil_tj@yahoo.com

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    Hi

    on 8.0.... "and a.deptid = b.deptid" will be excuted first. the way is buttom up in the where part of the sql.

  4. #4
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    According to me ,ur query will be executed in the following order

    1. a.deptid=b.deptid
    2. location=3
    3. b.deptid=10


    In case of any help needed please ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    hi rohit

    could you tell me why this is so.coz i feel where clause should be executed bottoms up.is this specific to a version????

    regards
    hrishy

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi rohitsn

    why? can you give me a referrence in ther documentation where i can read about your logic? ican only tell you that my answer is based on testing, but only simple tests.

  7. #7
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    to be more generic, I'd say that it depends on your optimizer mode, on the freshness of the stats, and on the indexes ...

  8. #8
    Join Date
    Jul 2000
    Posts
    243
    hi pipo

    you are rihgt, but let's try this scenario. no hint's and no cost optimizer. what will be the sequence then?

  9. #9
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, testing on MP and DEPT demo tables, without any index, I get the explain plan :

    Operation Option Obj Name
    ----------------------------------- ----------- --------------------
    SELECT STATEMENT
    MERGE JOIN
    SORT JOIN
    TABLE ACCESS FULL DEPT
    SORT JOIN
    TABLE ACCESS FULL EMP

    that is to say : FULL on EMP, FULL on DEPT where LOC=3 and DEPTNO=10, and then merge join the 2 temporary results on deptno ...

  10. #10
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Questions:

    1 - Why not just run it and get the plan?

    2 - Why would you still be doing RBO anyway?

    Note: With multiple-table statements, prefix *every* column with either a table name or alias:
    "where location = 3" is bad form.


    - Chris

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