Kbase 21100: Using XREF Within Indexes Explained
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/16/2009 |
|
Status: Verified
GOAL:
Use of the Compiler's XREF option to find what index or indexes are used in a query.
GOAL:
How to use XREF Compiler option to find what index or indexes are involved in a query?
FACT(s) (Environment):
Progress 8.x
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
Xref specifies the file where the Application Compiler writes cross-reference information between source files and database objects.xref. For each object reference, the .xref file contains one unformatted and blank-separated line containing the following:
1) Procedure name
2) Source filename
3) Line number
4) Reference type
5) Object identifier
-- List of tags the XREF compile option generates:
SEARCH:
Indicates an index bracket or look up will be used. The logical database name, table name, and index names are listed. When multiple brackets and indexes are used for the same query, you will see one search line for each bracket.
SEARCH ... WHOLE-INDEX:
Indicates that a suitable bracket could not be constructed and an index scan over the entire table will be performed using the index noted.
SORT-ACCESS:
Indicates that the query result is to be ordered by a particular column value and no suitable index exists. A sort of the query result on the noted column value is required.
ACCESS:
Indicates that the specified table and field value is used at this point in the program.
CREATE:
Indicates that a record is created at this location in the program.
DELETE:
Indicates that a record is deleted at this location in the program.
UPDATE:
Indicates that the specified field value of a table is updated at this location in the program.
See the COMPILE statement in the Progress Language Reference for more information on the cross-reference file.
-- Which Indexes are Used
To learn what index or indexes the compiler will use, COMPILE with the XREF option. This creates a cross-reference listing with a SEARCH label indicating which indexes will be used. The presence of multiple SEARCH labels for the same statement indicates that multiple indexes or brackets will be used for the query.
Here are some examples:
Example 1:
FIND customer WHERE (ROWID(customer) = rowid-customer).
SEARCH tmp.Customer RECID
Recid (Rowid) is used directly.
Example 2:
FIND customer WHERE (custnum = 46) USE-INDEX custnum.
SEARCH tmp.Customer CustNum
Index CustNum is used.
Example 3:
FIND customer WHERE (custnum = 46) USE-INDEX name.
SEARCH tmp.Customer Name WHOLE-INDEX
Index Name is used. WHOLE-INDEX after the index name indicates that no racketing is possible. No bracketing also means that the entire index is bracketed. This will result in a full index scan, reading every index entry, to retrieve the rows.
Example 4:
FOR EACH customer:
SEARCH tmp.Customer CustNum WHOLE-INDEX
Index CustNum is used. WHOLE-INDEX after the index name indicates that no bracketing is possible.
Example 5:
FOR EACH customer WHERE (name = "Martin") AND (Salesrep = "Jim"):
SEARCH tmp.Customer Name
SEARCH tmp.Customer SalesRep
Two entries with the SEARCH label appear in the xref listing for the same listing statement number. This indicates that two indexes, Name and SalesRep are being used.
Example 6:
FOR EACH customer WHERE (custnum < 99) OR (name = "John") OR (name = "Martin"):
SEARCH tmp.Customer CustNum
SEARCH tmp.Customer Name
SEARCH tmp.Customer Name
Three entries with the SEARCH label appear in the xref listing for the same list.ing statement number. In this case index Custnum and Name are used. Name appears twice indicating that two separate brackets on Name are used..