Progress
Database Design
Guide
Background and Terminology
This section provides a concise, abbreviated summary of the concepts and terminology required to discuss how Progress chooses the most efficient indexes to satisfy a query. For the purposes of index selection, there are three general types of WHERE clause:
For example:
For example:
For example:
The optional BY field clause imposes a sort order on returned records and is called a sort match. A searchExpr typically has one of the following forms:
BY field Sort Match equality match range match range match equality (simple string)
range (wildcard string)
none (>1 string, joined logically)
For more information, see the Record Phrase and FOR statement reference entries in the Progress Language Reference.
Because these expressions effectively select the records to return—and the indexes to use—they are called search conditions. Commonly, but not always, field is an indexed field. Also, a searchExpr can include other searchExpr’s joined by ANDs and ORs, forming arbitrarily complex queries.
The Compiler constructs a logical tree from a query and evaluates both sides of each AND or OR, looking for index criteria. Progress counts equality, range, and sort matches (for OR) and uses them to select and bracket indexes. The precise rules are numerous and complex, and it is not important to fully understand their details. The next sections outline the rules in sufficient detail to help you develop a feel for index usage. In addition, you should experiment by coding various queries, compiling them with the XREF option, and examining index usage as reported in the SEARCH lines of the XREF output file.
The index selection examples that follow are based on the sports database.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |