After you understand how the query is processed, look for ways to obtain the same output with less effort. The following suggestions can help you rewrite your query more efficiently:
Sorting is not necessarily a liability. The sort algorithm is highly tuned and extremely efficient. It is as fast as any external sort program that you might apply to the same data. You need not avoid occasional sorts or sorts of relatively small numbers of output rows.
Avoid or reduce the scope of repeated sorts of large tables. The optimizer avoids a sort step whenever it can produce the output in its proper order automatically from an index:
To verify that the index will provide the proper output, use the EXPLAIN ON AVOID_EXECUTE statement.
For some queries, you can avoid large sorts by creating temporary tables, as the following section describes. If a sort is necessary, look for ways to simplify it. The sort is quicker if you sort on fewer or narrower columns.
Building a temporary, ordered subset of a table can speed up a query. It can help to avoid multiple-sort operations and can simplify the work of the optimizer in other ways.
For example, suppose that your application produces a series of reports on customers who have outstanding balances, one report for each major postal area, ordered by customer name. In other words, a series of queries occur, each of the following form:
SELECT cust.name, rcvbles.balance, ...other columns...
FROM cust, rcvbles
WHERE cust.customer_id = rcvbles.customer_id
AND rcvbls.balance > 0
AND cust.postcode LIKE '98_ _ _'
ORDER BY cust.name
This query reads the entire cust table. For every row with the requested postal code, the database server searches the index on rcvbles.customer_id and performs a nonsequential disk access for every match. It writes the rows to a temporary file and sorts them. For more information on temporary files, refer to Temporary Tables That the Database Server Creates.
This procedure is acceptable if the query is performed only once, but this example includes a series of queries, each of which incurs the same amount of work.
If the customer table is fragmented by hash, an alternative is to select all customers with outstanding balances into a temporary table ordered by customer name, and to fragment the temporary table by hash so that the database server can eliminate table fragments as it processes the query. The following example shows how to create the temporary table:
SELECT cust.name, rcvbles.balance, other columns
FROM cust, rcvbles
WHERE cust.customer_id = rcvbles.customer_id
AND cvbls.balance > 0
INTO TEMP cust_with_balance
fragment by HASH (cust_id) in customer_dbslc;
You can direct queries against the temporary table in this form, as the following example shows:
SELECT * FROM cust_with_balance WHERE postcode LIKE '98_ _ _' ORDER BY cust.name
Each query reads the temporary table sequentially, but the table has fewer rows than the primary table.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]