Kbase P64458: SORT-ACCESS within XREF file generated
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
16/10/2008 |
|
Status: Unverified
GOAL:
Why a lot more DB Requests with an additional WHERE clause?
GOAL:
SORT-ACCESS within XREF file generated
GOAL:
Comparing DB Requests for
FOR EACH order NO-LOCK BY salesrep
and for
FOR EACH order NO-LOCK WHERE ordernum > 0 BY salesrep
FIX:
Compiled with XREF the query
FOR EACH order NO-LOCK BY salesrep
shows:
ACCESS sports2000.Order SalesRep
SEARCH sports2000.Order SalesRep WHOLE-INDEX
The index Salesrep is used to retrieve all records already sorted in the desired order.
Compiled with XREF the query
FOR EACH order NO-LOCK WHERE ordernum > 0 BY salesrep
shows:
ACCESS sports2000.Order Ordernum
ACCESS sports2000.Order SalesRep
SEARCH sports2000.Order OrderNum
SORT-ACCESS sports2000.Order SalesRep
The index OrderNum is used to fetch every record, the field Salesrep is retrieved along with the ROWID. All values are placed in a result list, which is then sorted in the desired order.
After that, in a second pass records are retrieved again, using the sorted ROWIDs within the result list.
If we know, as within this sample, that the WHERE clause retrieves most records, all the records in this sample, we have interest to add a USE-INDEX option.
Compiled with XREF the query
FOR EACH order NO-LOCK WHERE ordernum > 0 USE-INDEX salesrep BY salesrep
shows:
ACCESS sports2000.Order Ordernum
ACCESS sports2000.Order SalesRep
SEARCH sports2000.Order SalesRep WHOLE-INDEX