Which sql statement is more efficient (Oracle SQL)?



  • Create a query to get the customer with the most numbers of orders - am wondering which one is a more efficient query?

    SELECT c.customer_id, c.first_name || ' ' ||  c.last_name as Name, 
           count(o.customer_id) as Orders
    FROM customer c
    JOIN customer_order o
    ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
    ORDER BY 3 DESC
    FETCH FIRST 1 ROWS ONLY;
    

    OR

    SELECT c.customer_id, c.first_name || ' ' ||  c.last_name as Name,
       ORDER_COUNT 
       FROM (
          SELECT customer_id, count(customer_id) order_count
          FROM  customer_order
          GROUP BY customer_id
          ORDER BY order_count DESC
          FETCH FIRST 1 ROWS ONLY) o
    JOIN customer c
    ON c.customer_id = o.customer_id;
    

    Thank you!



  • The question is: Is Oracle smart enough to delay the join on customer_order in the first query? If it has good statistics to work with, I think the CBO is going to come through for you. If that's the case, then it's doing the same work in each, and cost should be roughly the same. Of course, this is speculation. If you have test data, I would use something like autotrace to see how they were actually executed. Here's a quick reference to using autotrace if you're not familiar with it: https://www.youtube.com/watch?v=fJSRYCC2nX4 Best of luck!




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2