> SELECT FIRST 20 SKIP 0 * from ASSET_LIST_GET_PRC();
>
> The query is taking 34 minutes to run, which clearly isn't going to

work.

Tell your users they are being impatient :)

> Here is the query:
>
> SELECT
> ASSET.ASSET_ID,
> ASSET.ASSET_SERIAL_NO,
> CLIENT.CLIENT_NAME,
> ASSET.PURCHASE_DATE,
> ASSET_TYPE1.AT_NAME,
> MODEL.MODEL_NO,
> ASSET.DESCRIPTION
> FROM
> ASSET
> INNER JOIN ASSET_TYPE ASSET_TYPE1 ON (ASSET.AT_ID =
> ASSET_TYPE1.AT_ID)
> INNER JOIN CLIENT ON (ASSET.FK_CLIENT_ID = CLIENT.CLIENT_ID)
> INNER JOIN CLIENT_SITE ON (ASSET.FK_SITE_ID =

CLIENT_SITE.SITE_ID)

> INNER JOIN MODEL ON (ASSET.MODEL_ID = MODEL.MODEL_ID),
> ASSET_TYPE
> order by ASSET.ASSET_SERIAL_NO ASCENDING

What is the plan generated by this query?

Given that you have no 'where' clause, you probably want to do an
indexed walk, so make sure you have an ascending index on ASSET
(ASSET_SERIAL_NO_ASCENDING).

> Should I be doing a separate query first to get the candidate rows,
> and then somehow joining these resulting rows back to this query to

get just

> those records with the joined values?

Well if you have an efficient method of getting the candidate rows,
that is prefered, but if you look at your query, it may *complete*
faster if it starts with the largest table in natural order and joins
out from there, then sorts in memory. The downside is that it must do
every join before it can return the first 20 record. Because you are
only interested in a tiny proportion of records, it makes sense to
read in order of ASSET_SERIAL_NO_ASCENDING, even if it would take
longer to complete the entire table. Locating the first 20 records is
very quick, but returning them all would be slower.

Adam