Implementing dynamic paged queries - POJOs in Action: Developing Enterprise Applications with Lightweight Frameworks - page 415

Using optimizer hints
One important way to improve the performance of a query is to use optimizer
hints, which are a database-specific way of influencing how the database executes
a SQL statement. They are useful when the database's query optimizer is unable to
automatically determine the best way to execute a query. Using them in a JDBC or
i BATIS application is quite straightforward but in section 11.3, we will see that
Hibernate and JDO object queries do not support optimizer hints, which can
sometimes force you to use SQL queries instead.
Each database has a different way of writing optimizer hints, and we'll provide
an overview of how they work in Oracle. An Oracle optimizer hint is a specifically
formatted comment in a SQL statement that tells Oracle how to execute the state-
ment. Oracle provides several kinds of optimizer hints. The FIRST_ROWS(N) hint is
a good way to improve the performance of queries when the application only dis-
plays the first N rows of a result set, as is typically the case with search screens. By
default, the Oracle query optimizer assumes that an application wants all of the
rows returned by a query and aims to maximize throughput by, for example, min-
imizing the amount of I/O required to execute the query. Therefore, Oracle will
sometimes process the entire result set before returning the first rows back to the
application, which can be inefficient if the application only needs the first few rows.
It also results in a poor response time. The FIRST_ROWS(N) hint tells Oracle to exe-
cute the query in a way that minimizes the time to return the first N rows to the appli-
cation. This improves the response time and is often more efficient if the
application only wants the first N rows. The following query uses this hint to tell Ora-
cle to execute the query in a way that minimizes the time to return the first 20 rows:
SELECT /*+ FIRST_ROWS(20) */ o.*, r.name
FROM PLACED_ORDER o, RESTAURANT r
WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
AND o.DELIVERY_TIME > (SYSDATE - 30)
ORDER BY o.ORDER_EXT_ID DESC
This query executes considerably faster than the original query shown earlier.
However, one downside of using this hint is that sometimes it has the opposite
effect and reduces the performance of the query. It is important to experiment.
Denormalizing the schema
If you can change the database schema, then another way to improve query perfor-
mance is to eliminate expensive joins by denormalizing the schema. For example,
we can eliminate the need to use a join between the PLACED_ORDER and RESTAU-
RANT tables by storing the restaurant's name in the PLACED_ORDER table. A data-
base trigger would maintain consistency by updating the RESTAURANT_NAME column