Kbase P12969: 4GL Query concepts (FOR EACH, FIND, GET, INDEX)
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
9/24/2009 |
|
Status: Verified
GOAL:
4GL query concepts (FOR EACH, FIND, GET, INDEX)
GOAL:
What are 4GL queries?
GOAL:
What are the elements of query execution
GOAL:
Characteristics of FOR EACH queries
GOAL:
Characteristics of PRESELECT queries
GOAL:
Characteristics of FIND queries
GOAL:
Characteristics of GET queries
FACT(s) (Environment):
OpenEdge Category: Language (4GL/ABL)
Progress/OpenEdge Product Family
All Supported Operating Systems
FIX:
4GL Query concepts ( FOR EACH , FIND , GET , INDEX )
CONTENTS
========
1. Introduction
2. What are 4GL queries?
3. The elements of query execution
4. Execution strategy
5. Characteristics of FOR EACH queries
6. Characteristics of PRESELECT queries
7. Characteristics of FIND queries
8. Characteristics of GET queries
INTRODUCTION
============
****** PLEASE NOTE: The contents of this document are accurate as of the time of its writing. Due to ongoing development it is likely that certain details may change without notice. Programmers are advised to test thoroughly to verify any changes made in accordance to the information given here. This kbase should not be treated as a functional specification and is provided for informational purposes
only. ******
This document describes the functionality and behavior of PROGRESS 4GL queries. Its purpose is to help in understanding the peculiarities of PROGRESS 4GL and the requirements they impose on the query engine.
This document concentrates on those features that affect the PROGRESS server. It discusses 4GL semantics and client features to the extent that they are needed to understand how queries are executed. It does not describe the complete query semantics as they are manifested in 4GL programs. It does not address PROGRESS SQL queries or queries for foreign data managers (DataServers).
It is assumed that the reader is somewhat familiar with the concepts of indexes, index cursors, client buffers and some other basic terms related to the PROGRESS database.
WHAT ARE 4GL QUERIES?
=====================
4GL Queries are data manipulation operations that are used to retrieve or change data stored in the database. In PROGRESS version 6 and earlier, 4GL queries came in two main flavors: FIND queries and block oriented queries (FOR EACH and PRESELECT). In version 7, the OPEN QUERY/GET flavors of queries were added, with the expectation that they would replace FIND queries in most cases.
GET queries were added because although FIND queries provide very useful functionality, they are very difficult to optimize exactly because of that functionality. There was a need to provide better performance, but also to maintain existing functionality, and the best way to achieve that was by adding new flavors of queries with somewhat different functionality.
The 4GL language constructs used with the three query types are:
- FOR EACH/PRESELECT
- FOR EACH
- REPEAT PRESELECT
- DO PRESELECT
- FIND
- FIND [FIRST | NEXT | LAST | PREV]
- FIND
- FIND CURRENT
- FIND record WHERE ROWID(record) = expression
- CAN-FIND ([FIRST | LAST])
- CAN-FIND (record WHERE ...)
- GET
- DEFINE QUERY
- OPEN QUERY
- GET [FIRST | NEXT | LAST | PREV]
- GET CURRENT
Other 4GL language constructs related to queries
- BY - specifies result ordering via an index or sort
- USE-INDEX - forces use of a particular index. Ensures
order if no BY clause used
- SCROLLING - attaches a result list to a query
- CACHE - attaches a query cache and a result list to a
query
- REPOSITION - moves the current position of a query
- INDEXED-REPOSITION - allows repositioning of a query which uses a
single index by directly repositioning the
index cursor
- NO-WAIT - prevents query from waiting if record is
locked, and returns control to the 4GL
program immediately
- CONTAINS - query through word-index
- FIELDS - defines which fields to retrieve
THE ELEMENTS OF QUERY EXECUTION
===============================
This section describes the basic concepts, structures and operations involved in the execution of queries.
The table below, from the demo(sports) database, is used in many of the examples. There is an index defined on the cust-num column, another on the name column, and another on the zip column.
CUST.-NUM NAME STATE ZIP
======== ======================================= ===== =====
1 Second Skin Scuba AZ 85369
2 Match Point Tennis TX 75431
3 Off The Wall PA 15632
4 Pedal Power Cycles MA 02145
5 Flying Fat Aerobics NY 14728
6 Lift Line Skiing MA 02114
7 Fallen Arch Running FL 32010
8 Butternut Squash Inc. CA 92243
9 Spike´s Volleyball NV 89411
12 Batter Up Baseball KY 42088
13 Blue Line Hockey ME 04474
14 Birdy´s Badminton OK 73048
15 Hoops Croquet Co. MA 02111
20 StickyWicket Cricket MN 56468
21 Ship Shape Yachting CT 06612
22 Pocket Billiards Co. CA 92371
23 Sub Par Golf CO 80482
24 On Target Rifles MS 39657
INDEX CURSORS
An index cursor is a structure which is used to maintain a position within an index. It can be moved to the next index entry, the previous entry or a specific entry, and can be used to read the record at the current entry.
Index cursors are managed by the server on behalf of clients. A cursor is opened, closed, repositioned and used to fetch records for a specific client, at the client´s request.
Multiple index cursors can be opened on one or more indexes at any time. Any interaction between them (such as repositioning of related cursors) is initiated by the client; as far as the server is concerned there is no relationship between them.
INDEX BRACKETS
An index bracket is a set of consecutive entries in an index. A bracket is defined by the indexidentifier (index number), a lowest key value (the low limit) and a highest key value (the high limit). All index entries starting with the low value and up to the high value are included in the bracket. A fundamental method for retrieving data is by means of a bracket scan, an operation in which all the index entries from the low limit to the high limit are examined.
There are two classes of brackets: equality brackets and range brackets. Equality brackets define a set of consecutive index entries that have an equality match on a key (or on part of a key). The low and high limit key values are the same. Range brackets define a set of entries from the low key value to the high key value. The entries in a range bracket often have many different key values.
To evaluate a query, PROGRESS always used at least one index bracket.
The 4GL compiler analyzes the query to see if it can use the elements of a WHERE clause, OF, USING, etc., the available indexes and key components to form brackets. The most restrictive brackets will be the most efficient to apply first. If the query does not define any brackets, the compiler will supply a default bracket, usually a table´s primary index.
The expression
(name BEGINS "M")
describes a set of entries where the name starts with the letter "M", which is part of the name key. This forms a range bracket.
The expression
((name > "Off The Wall") and (name < "Quick Toss Lacrosse"))
describes a set of consecutive entries by the name index. This formsa range bracket.
USING ONE INDEX BRACKET TO RETRIEVE RECORDS
To retrieve records using an index bracket, the client opens a new cursor, or uses an already open one. It then sends the server a request that includes the cursor identifier and the bracket range (low and high key values), and asks for the next, previous, first or last records in the bracket.
The following queries each use the default bracket on the entire
index:
for each customer: /* entire table desired, no where clause */
end.
for each customer by state: /* sort, no index on state */
end.
for each customer
where (state = "OH"): /* no index on state */
end.
The following queries each use a single bracket on part of one index:
for each customer
where (zip = 12345):
end.
for each customer
where (name = "Off The Wall"):
end.
for each customer
where (name begins "B"):
end..
for each customer
where (zip > 50000) and (zip < 60000):
end.
for each customer
where (name = "Off The Wall") and (zip > 50000):
end.
USING MORE THAN ONE INDEX BRACKET TO RETRIEVE RECORDS
Multi-bracket queries are used by the GET, FOR EACH and PRESELECT statements in version 7 and later. They provide functionality similar to that of index cursors, but can use multiple index brackets and multiple index cursors and provide better performance. They combine index brackets by applying algorithms which take advantage of the index structure, and improve performance for queries with OR and AND operators. For example, if a single index bracket were to be used to execute the query
for each customer
where (cust-num <= 10) or (name = "Mary"):
end.
the whole customer table would have to be scanned, because no smaller bracket will include all of the desired records. Using two brackets, many fewer records need to be accessed.
The following queries use two brackets on one index.
for each customer
where (zip = 12345) or (zip > 40000):
end.
for each customer
where ((zip > 50000) and (zip < 60000)) or
((zip > 70000) and (zip < 80000)):
end.
The following query uses three brackets on two indexes.
for each customer
where (name = "Off The Wall") or
(zip = 17030) or
(name = "StickyWicket Cricket"):
end.
The following query uses four brackets on two indexes.
for each customer
where ((name = "Off The Wall") and (zip = 01824)) or
((name = "StickyWicket Cricket") and (zip = 22070)):
end.
Multi-bracket queries are typically more efficient (faster) than single index cursors in retrieving records, but they can not be repositioned like index cursors: multi-bracket queries can only be moved forward to the next record, and operations such as GET PREV must be supported by the client via a result list; single-bracket queries can be repositioned by repositioning the underlying index cursor, a feature which is accessible to the 4GL via the INDEXED-REPOSITION
option.
RECORD SELECTION
Record selection means determining whether a record satisfies a query by evaluating an expression involving its contents rather than finding it via an index. The checking is done by evaluating E-code - code which returns true or false - against the record. For example, in the query
for each customer
where (cust-num < 10) and (city = "Boston"):
end.
where the cust-num field is indexed and the city field is not, PROGRESS does the following:
- fetches all records where cust-num < 10 via an index bracket on
the cust-num index
- performs a selection on each retrieved record to find out whether
it contains the value "Boston" in the city field.
In version 6 and earlier, selection is done exclusively by the client. As a result, a remote version 6 server may send many records to the client which the client checks and discards. A version 7 server is capable of doing most selections, and sends only those records which fully satisfy the query to the client.
It is important to note that there are some selection operations that the server cannot do, either because they require access to program variables in the client, or because they are not implemented on the server (the most important such function is CAN-FIND which is not yet implemented on the server). In such a case, the server sends the records to the client along with an indication that it cannot perform the selection, and the client must do it.
QUERY BY WORDS
Queries by words are supported through the 4GL CONTAINS clause. They use word-indexes, which have the same structure as regular indexes, but contain an entry for each word in a character field rather than one entry for the whole field, as regular indexes do.
Evaluating CONTAINS clauses differs from evaluating other. index expressions in two ways:
- a CONTAINS clause is viewed as a single index bracket by the
client, but may actually use multiple brackets, depending on the
expression. For example, the query
for each claim
where (description contains "lawyer | attorney"):
end.
requires two brackets. The decision of how many brackets and how to use them is done by the server, at run-time. This allows the 4GL program to not only change the words in the clause, but also the operators between the words.
- the CONTAINS clause cannot be evaluated during selection - a
word-index must always be used. As a result, the client cannot
deal with it, and the server must evaluate it using the word-index.
FINDING RECORDS BY ROWID
A query of the form
find customer where (rowid(customer) = <some rowid value>)
does not require the use of any index. The ROWID identifies the exact location of the record in the database, and the server can retrieve it without using any index bracket.
UNIQUE FIND QUERIES
A FIND query without a NEXT, PREV, FIRST or LAST performs an operation which is different from other FIND operations: it not only locates a record, but also ensures that it is the only record which satisfies the query; if more than one record is found, it returns an error. In order to achieve this, PROGRESS must find the first record, and then look for the next record and not find one. The second step can be very costly in some cases: for example, if the city field is not indexed, the query
find customer where (city = "Boston")
requires PROGRESS to scan the customer table until such a record is found, and then continue scanning the rest of the table to ensure that no other such record exists.
Whenever possible, the verification is done by the server, but if the server cannot execute the query, the client must verify the record´s uniqueness in addition to performing the selection.
JOINS
PROGRESS translates joins into multiple single-table queries, which are the only types of queries the server can execute today. For example, the query
for each customer, each order of customer:
is executed by the client by opening to queries: one the customer and one for order. After each customer record is received, the client opens the second query
for orders where order.cust-num = customer.
cust-num, and retrieves all the orders for the customer. When it is done with the orders it goes to the next customer, and so on.
FIELD LISTS
The FIELDS and EXCEPT clauses instruct PROGRESS to retrieve only selected fields from a record. This reduces the number of bytes sent over the network when accessing a remote server.
Field lists do not reduce the amount of database I/O performed by a query. Records are always read into the server´s buffers in their entirety. The server then discards any unnecessary fields from it.
In single-user or self-serving mode, reducing record sizes does not improve performance. Nevertheless, PROGRESS does discard the unwanted fields in order to help detect 4GL programming errors which would occur when running the same program with a remote server.
PREFETCH - MULTIPLE RECORDS INTO NETWORK MESSAGES
In order to reduce network traffic, a remote PROGRESS server can package multiple records into each network message when executing a query. It packages up to the size of one network message, which is controlled by the value of the -Mm startup parameter.
This is called prefetch, because it requests the server to fetch additional records before the client requests them.
The initiative for this operation comes from the client. When the client sends a query request to the server, it can set to flags in the request: the first indicates that it would prefer to receive more than one record in each message when possible; the second indicates that, in addition, it wants .each message to contain as many records as will fit. If only the first flag is set, the server will balance the goal of reducing the number of network messages with the need to provide reasonable response time, and may send less than full messages. If both are set, the server will not respond until it has completely filled a message packet.
The client sets both flags when executing a PRESELECT or when pre-sorting query results. For example, in:
do preselect each customer: /* client asks for rowids only */
... /* in full messages */
end.
the client reads all the customer records and stores their rowids in a result list before entering the loop. It does not proceed until it has all the records, so it doesn´t benefit if the first message comes back faster and is only partially full.
The client sets the first flag when executing a FOR EACH or scrolling query with the NO-LOCK (unless it contains a NO-PREFETCH qualifier).
For example,
for each customer no-lock: /* client asks for multiple records */
... /* per message if possible */
end.
This is limited to NO-LOCK because it requires fetching and locking more records than the 4GL asked for: in this example, when the FOR EACH loop asks for the 1st record, the client might get back four records. If a lock is used, the client will have obtained locks on the extra records, which are not yet available to the 4GL - they are stored in the network buffer - and if one of them causes a deadlock, the client cannot release it, because its 4GL program isn´t aware
that it got it.
RESULT LISTS
Result lists are lists of rowids of a query´s retrieved records. They are kept by a client and updated as the results of a query are received from a server. They are used to allow the client 4GL program to execute operations such as GET PREV on multi-index bracket queries, where the server can only do GET NEXT. In addition, they are used by PRESELECT queries, and by queries where the results must be sorted (see sorting, below).
SORTING QUERY RESULTS
When a BY clause is used with a query, PROGRESS must produce the query results in the specified order. It does so in one of two ways:
- if an index that is in the desired order exists, and a single
bracket on it can be used to execute the query while retrieving
the minimum number of records, it is used.
- otherwise, the query is executed in a two pass process. First
every record is fetched, and the fields required to determine the
order are retrieved, along with the ROWIDs. These values are
placed in a result list, which is then sorted in the desired order.
In the second pass records are retrieved again, using the sorted
ROWIDs in the result list.
QUERY REPOSITIONING
Queries with result lists can be repositioned via the REPOSITION statement. Most such operations are executed by the client using the result list, and don´t require server support. For example:
define query q for customer scrolling.
open query q preselect each customer.
/* the next statement will simply reposition the query within */
/* the result list */
reposition q to row 3.
/* the next statement will read customer 3 via its rowid */
/* stored in the result list */
get next q.
The REPOSITION statement may be handled by the server only when INDEXED-REPOSITION is used on a single index bracket, single table query. For example:
define query q for customer scrolling.
open query q for each customer indexed-reposition.
find customer where ... xxx = rowid(customer).
/* the next statement will cause the serve to reposition the */
/* index cursor used by the query */
reposition q to rowid xxx.
THE QUERY CACHE
A query cache is a client mechanism that keeps the most recently read records of a query in the client´s buffer pool. This speeds up browsing, when GET PREVIOUS and GE.T NEXT requests are issued repeatedly.
A query cache is created by specifying the CACHE option in the DEFINE QUERY statement. It is created by default for queries which are browsed by the 4GL browser.
The following program fragment shows how it can be used:
define query q for customer cache 10.
open query q for each customer no-lock.
get next q. /* gets cust 1 */
get next q. /* gets cust 2, leaves cust 1 in the cache */
get prev q. /* gets cust 1 from cache without access to */
/* database or server */
Query caches can only be used with queries that use NO-LOCK.
EXECUTION STRATEGY
==================
For almost all queries, there will be a variety of different ways to retrieve the desired data. Among the choices that must be made are:
- which index brackets to use
- how to use the index brackets in conjunction with each other
- what expression to use for record selection
This is decided by the PROGRESS 4GL compiler when a program is
compiled. The resulting structures describing the execution
strategy are stored in the generated r-code. For example, if the
cust-num and zip fields are indexed and the city field is not, the
query
for each customer
where ((cust-num <= 10) and (city = "Boston")) or (zip > 01824):
end.
is compiled by generating the following data and storing it into the
r-code:
- index bracket r-code, which includes two index brackets, one on the
cust-num index and one on the zip index, combined via an OR
operator
- selection e-code, which contains the whole WHERE clause
Whenever possible, the compiler will use multiple index brackets.
For WHERE clauses that contain subexpressions connected by an OR
operator, multiple brackets will be used when there are usable
brackets on both sides of the OR.
For WHERE clauses that contain subexpressions connected by an AND
operator, multiple brackets will be used when all the key components
are used in equality matches, and the index on one side is not
unique.
The compiler uses the following rules for ranking the available
indexes when analyzing brackets and deciding which indexes to make
use of:
1) An index that was specified in USE-INDEX
2) A unique index when all key components are used for equality
matches.
3) The index with the most equality matches
4) The index with the most range matches
5) Word indexes referenced by the contains operator
6) The index with the most sort matches (BY)
7) A table´s primary index
8) The index that comes first alphabetically, by index name
You can determine which indexes will be used by a particular query by
examining the output produced by the cross-reference (XREF) option
of the 4GL compiler. Each index bracket that will be used will
produce one line marked with the tag "SEARCH". When a default
breacket on an entire index is being used, it will be marked
"WHOLE-INDEX". Sorts required for a BY <column> when there is no
suitable index will be marked with the tag "SORT-ACCESS".
Below is an extract from the cross-reference produced by compiling
some of the examples used in this section. Only those lines
relevant to queries are shown.
1 COMPILE p3.p
1 CPINTERNAL iso8859-1
1 CPSTREAM ibm850
3 SEARCH demo.customer cust-num WHOLE-INDEX
6 SEARCH demo.customer cust-num WHOLE-INDEX
6 SORT-ACCESS demo.customer Phone
9 SEARCH demo.customer cust-num WHOLE-INDEX
15 SEARCH demo.customer zip
19 SEARCH demo.customer name
23 SEARCH demo.customer name
27 SEARCH demo.customer zip
31 SEARCH demo.customer name
37 SEARCH demo.customer zip
37 SEARCH demo.customer zip
41 SEARCH demo.customer zip
41 SEARCH demo.customer zip
48 SEARCH demo.customer name
48 SEARCH demo.customer zip
48 SEARCH demo.customer nameR>56 SEARCH demo.customer name
56 SEARCH demo.customer zip
56 SEARCH demo.customer name
56 SEARCH demo.customer zip
CHARACTERISTICS OF FOR EACH EACH QUERIES
========================================
- SORT : yes
- JOIN : yes
- DIRECTION : forwards only
- FIELD LISTS : yes
- PREFETCH : with no-lock; at presort
- CACHE : no
- VARIABLE BINDING: once, before entering block
- QBW : yes
EXECUTION
In version 6, FOR EACH queries use a single index cursor and a single
index bracket, and their performance is similar to that of FIND
queries. In version 7 and later, they use server queries, and
utilize multiple index brackets and multiple indexes when possible.
There are two ways to force FOR EACH queries in version 7 to behave
like they did in version 6: by using the -v6q startup parameter, or
by using the USE-INDEX clause.
BLOCK ORIENTATION
FOR EACH queries are executed within an iterating 4GL block; the next
record is always fetched at the top of the block.
RECORD ORDERING
The order of retrieved records is "random" when multiple indexes are
used. Otherwise it is based on the index that was chosen. If
multiple indexes are possible, the BY phrase and USE-INDEX can be used
to guarantee a particular order. In version 6, with USE-INDEX or
when using -v6q the order is guaranteed to be that of the index used.
POSITIONING
FOR EACH queries always move forward. The next record is fetched at
the top of each iteration, and no navigation is allowed. There is,
however, an exception to this rule in version 6, or when using the
USE-INDEX or -v6q in later versions: the index cursor can be
repositioned via a FIND into the same buffer in a sub-procedure; see
the later section about FIND queries.
VARIABLE BINDING
FOR EACH queries evaluate variables used in the WHERE clause once,
before entering the block. For example, the program fragment
i = 5.
for each customer where (cust-num > i):
display cust-num.
i = 1.
end.
gets all customers where cust-num > 5. The fact that the value of
the variable i is changed inside the loop doesn´t affect the WHERE
clause or the outcome of the query.
QUERY CACHE
FOR EACH queries do not use the query cache. A cache is useful only
when accessing records more than once, and FOR EACH queries
normally do not.
PREFETCH
With NO-LOCK, prefetch is the default for FOR EACH queries. It can
be turned off by specifying NO-PREFETCH. If a presort is done, the
presort always uses prefetch and field lists, retrieving only the
nesessary fields and using network messages containing as many
records as possible.
CHARACTERISTICS OF PRESELECT QUERIES
====================================
- SORT : yes
- JOIN : yes
- DIRECTION : first, next, prev, last
- FIELD LISTS : yes
- PREFETCH : only at pass 1
- CACHE : no
- VARIABLE BINDING: once, before entering block
- QBW : yes
EXECUTION
As far as the server is concerned, PRESELECT queries execute exactly
like FOR EACH queries.
On the client, PRESELECT queries work in a two pass sequence, similar
to that of a sorting FOR EACH query: first all records that satisfy
the query are read, and a complete result list, with all the ROWIDs,
is prepared, and, if necessary, sorted. Then, in response to FIND
statements, the records are read again, this time via their ROWIDs
from the result list, and passed to the 4GL program.
For example:
do preselect each customer: /* prepares complete result list */
find next customer. /* gets rowid from list, reads rec*/
find last customer. /* gets last rowid from list, */
/* reads record */
end.
BLOCK ORIENTATION
A PRESELECT query is specified in the header of a 4GL block - either
a REPEAT block or a DO block. The preselect pass (the first pass) is
completed before the block is entered. Records are read within the
block via FIND statements.
RECORD ORDERING
The order of retrieved records is not guaranteed unless a BY or
USE-INDEX clause is used. In version 6, without USE-INDEX or when
using -v6q, the order is guaranteed to be that of the index used.
POSITIONING
A PRESELECT query can move to the next, previous, first, or last
record in the result list. The query cannot be positioned by any
other means.
VARIABLE BINDING
PRESELECT queries evaluate variables used in the WHERE clause once,
before entereing the block, exactly like a FOR EACH.
QUERY CACHE
Not used.
PREFETCH
Prefetch is used in the first pass, reading the ROWID and, if
necessary, sort fields, with messages that contain as many records
as possible. It cannot be turned off.
CHARACTERISTICS OF FIND QUERIES
===============================
- SORT : no
- JOIN : no
- DIRECTION : first, next, prev, last, auto
- FIELD LISTS : no
- PREFETCH : no
- CACHE : no
- VARIABLE BINDING: for every record
- QBW : no
EXECUTION
FIND queries always use a single index cursor and single index
bracket. The WHERE clause, and optionally a USE-INDEX clause,
determine which index is used. The index and buffer used by the
query define which index cursor is used. Thus, if two queries use
the same buffer and the same index, they use the same index cursor.
For example, the following three queries all use the same index
cursor:
find first customer where (cust-num > 10) use-index cust-num
find next customer where (name = "Mary") use-index cust-num
find prev customer where (cust-num < 100)
Thus, and index cursor is not owned by a specific FIND query, and can
be used my multiple FINDs. As a result, a FIND query can change the
position of an index cursor used by another query and thus affect
the result returned by the other query.
BLOCK ORIENTATION
Cursors used by find queries are scoped to the scope of the
associated buffer.
RECORD ORDERING
The order of returned records is determined by the index used, which
can be specified using a USE-INDEX clause. A BY clause is not
allowed.
POSITIONING
In addition to allowing FIND NEXT, PREV, FIRST and LAST, FIND queries
also get repositioned automatically by other queries: when a FIND or
FOR EACH query fetches a record, all the index cursors which are used
by FIND queries for the same buffer are positioned to the same
record. For example, if a customer table contains rows with the
following values for the columns cust-num and name:
CUST-NUM NAME
======== =======
1 Mary
2 Amnon
3 Chip
4 Sue
5 Jane
The following program fragment, which uses two index cursors - one on
the cust-num index and one on the zip index - displays customer
number 4:
find first customer where (name = "chip")
find next customer where (cust-num > 0)
display cust-num name
The first FIND finds customer number 3, and causes the index cursor
on the cust-num index to be positioned to the same record. The next
statement uses the cust-num index, which is now positioned on
customer 3, and finds customer number 4. If we omit the first FIND,
the program will find and display customer number 1.
The automatic cursor repositioning is performed by the client, and
involves the following steps:
- finding all the index cursors used by other FIND queries for the
same buffer. This includes cursors used for FOR EACH queries in
version 6, or in later versions if the FOR EACH included a
USE-INDEX clause, or was compiled with the -v6q flag.
- extracting from the record, for each cursor, the keys used by its
index, and building a copy of t.he entry for the record in that
index.
- sending FIND requests to the server using each cursor and the
respective key. This positions the cursor on the index entry for
that record. In version 6, the FIND request was sent to the server
as soon as the original record was found; in version 7 and later,
this is postponed until the cursor is used again. This can
improve performance significantly for some applications because a
cursor may be repositioned multiple times before it is used, it
may never be used again, or its next usage may not depend on its
current position (e.g., FIND FIRST).
If a buffer is shared my multiple .P´s, the cursor will be shared
only if the top level .p has a FIND NEXT on that cursor.
VARIABLE BINDING
In FIND queries, variables used in the WHERE clause are evaluated
each time before they are executed. For example, the program
fragment
i = 5.
repeat:
find next customer where (cust-num > i):
display cust-num.
i = 53.
end.
displays customers 6 and 54. Since the value of i in "cust-num > i"
is evaluated each time the FIND is executed, the query looks for
customers with cust-num > 53 in the second iteration.
QUERY CACHE
Not used.
PREFETCH
Not used.
CHARACTERISTICS OF GET QUERIES
==============================
- SORT : yes
- JOIN : yes
- DIRECTION : first, next, prev, last, reposition
- FIELD LISTS : yes
- PREFETCH : with scrolling no-lock; at presort
- CACHE : yes
- VARIABLE BINDING: once, before opening query
- QBW : yes
EXECUTION
GET queries use multi-bracket queries, and utilize multiple index
brackets when possible.
BLOCK ORIENTATION
None. GET queries can span multiple blocks, procedures and modules.
RECORD ORDERING
Not guaranteed unless a BY or USE-INDEX clause is used.
POSITIONING
In addition to allowing GET NEXT, GET PREV, GET FIRST and GET LASR
operations, GET queries also support the REPOSITION operation, which
moves the current queryposition to an arbitrary row within the query
result.
GET NEXT statements are supported for any query.
GET PREV, GET LAST and GET FIRST statements are supported if either
of the following is true:
1) the SCROLLING option is defined on the query
2) the query uses a single index cursor
The REPOSITION statement is supported only if SCROLLING is defined,
since it requires a result list. It can move the query some number
of rows forward or backward, to a specific row in the result list,
or to a record with a specific ROWID.
Most of these operations are performed by the client, using the
result list. The server can perform the following operations:
1) all queries; getting the next record
2) queries using a single index bracket: getting the previous,
last and first record; repositioning the index bracket to a
specific record. The latter is similar to FIND query
repositioning, and is used only for REPOSITION TO ROWID when
the desired record is not yet in the result list, and
INDEXED-REPOSITION is specified.
It should be noted that the result list operations are sometimes
time-consuming. For example, if the result list is not complete, a
GET LAST on a multi-index query requires fetching all the remaining
records in the query to complete the result list. When used
correctly, however, the result list provides excellent navigation
capabilities for browsing.
VARIABLE BINDING
Variables referenced in the WHREE clause or a GET query are
evaluated once, when the query is opened. For example, the program
fragment
i = 5.
open query q for each customer where (cust-num > i).
get next q.
display cust-num.
i = 1.
get next q.
display cust-num.
displays customers 6 and 7. The change in the value. of i doesn´t
affect the query.
QUERY CACHE
Used when the CACHE option is specified, or, by default, when a
browser is defined on the query.
PREFETCH
When SCROLLING (or a CACHE) is specified, and NO-LOCK is used,
prefetch is the default, but can be turned off via a NO-PREFETCH
option. If a presort or PRESELECT is done, the presort always used
prefetch and field lists, retrieving only the necessary fields with
messages that contain as many records as possible..