-2

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!

6
  • 2
    Run both and compare whatever metrics determine efficiency for you.
    – mustaccio
    Commented Jan 20, 2022 at 17:31
  • I only have a small table to run with so both return the same amount of time, just wanted to hear from the db experts based on their experience. But thanks for the -1
    – punsoca
    Commented Jan 20, 2022 at 17:48
  • 2
    What prevents you from inserting more data into your table? Please consider reading this advice
    – mustaccio
    Commented Jan 20, 2022 at 18:22
  • Hi, and welcome to dba.se! You could always run an EXPLAIN PLAN - not sure of the exact Oracle syntax, but that should give you a clue as to which might be better - it may be that you'll only get a single plan for the two - depending on how Oracle may (or may not) rewrite the queries?
    – Vérace
    Commented Jan 20, 2022 at 19:55
  • As mentioned, you should run an EXPLAIN PLAN. Be aware, however, that a small table may very well produce a different plan than a very large one. Thus, which query is "most efficient" could change with the size of the tables. Personally, I'd go with the one that is syntactically the simplest or most intuitive, then adjust if and only if performance becomes unacceptable. Do not allow yourself to contract a case of Compulsive Tuning Disorder.
    – EdStevens
    Commented Jan 20, 2022 at 20:33

1 Answer 1

1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.