Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: The difference between two queries

    Hi Guys,

    I have somewhat of a noob db question. I have two queries and they are behaving very differently in different scenarios. It has left me wondering the difference of exactly how Oracle behaves when it is given two queries that ultimately give the same result.

    Query 1 uses embedded selects:

    select so.* from sampleoperations so
    where so.sampleid in
    (select s.sampleId from ordersamples os
    inner join samples s on os.OrderSampleId = s.OrderSampleId
    where os.ordersampleid in
    (select os.ordersampleid from ordersamples os where os****norderid = 4202))
    ORDER BY so.SampleOperationId


    Query 2 uses a bunch of AND statements:

    SELECT so.* FROM SampleOperations so, Samples s, OrderSamples os
    WHERE so.SampleId = s.SampleId
    AND s.OrderSampleId = os.OrderSampleId
    AND os****nOrderId = 4202
    ORDER BY so.SampleOperationId


    In my scenario, query 1 runs at .3 seconds, and query 2 at over 20 seconds. In other scenarios, they run the exact same amount of time. Are there certain situations where query 2 will outperform query 1? Does Oracle perform internal optimization that should make these queries identical? Thanks!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Oracle perform internal optimization that should make these queries identical? Thanks!!
    Obviously not always because
    >In my scenario, query 1 runs at .3 seconds, and query 2 at over 20 seconds.

    >Are there certain situations where query 2 will outperform query 1?
    Obviously, yes
    >In other scenarios, they run the exact same amount of time.

    Why do you ask questions when you already know the answers?

    ALWAYS
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version;
    Last edited by anacedent; 09-21-10 at 20:22.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    Thanks for your response, dickface. I asked if it SHOULD be optimizing them to be identical, not IS IT making them identical. Also I said that query 1 was faster than query 2. Please explain to me how it is obvious that query 2 is faster in some situations. Whats the point of being a complete asshole? Does it make you feel better about your miserable life?

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    xi2elic,

    but you are aware, that your communication style and the ability of getting help here are mutually exclusicve ?
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

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