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;
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;
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!