Optimization goals
Optimizing total query time and optimizing user-response time are two optimization goals for improving query performance.
Total query time is the time it takes to return all rows to the
application. Total query time is most important for batch processing
or for queries that require all rows be processed before returning
a result to the user, as in the following query:
SELECT count(*) FROM orders
WHERE order_amount > 2000;User-response time is the time that it takes for the database server
to return a screen full of rows back to an interactive application.
In interactive applications, only a screen full of data can be requested
at one time. For example, the user application can display only 10
rows at one time for the following query:
SELECT * FROM orders
WHERE order_amount > 2000;Which optimization goal is more important can have an effect on the query path that the optimizer chooses. For example, the optimizer might choose a nested-loop join instead of a hash join to execute a query if user-response time is most important, even though a hash join might result in a reduction in total query time.