Allows programmatic control over the execution of a query in a DataServer application. This phrase is available for the DataServers; it is not available for queries of OpenEdge databases.
QUERY-TUNING ( {[ ARRAY-MESSAGE | NO-ARRAY-MESSAGE ] [ BIND-WHERE | NO-BIND-WHERE ] [ CACHE-SIZE integer ] [ DEBUG { SQL | EXTENDED diag-option }| NO-DEBUG ] [ INDEX-HINT | NO-INDEX-HINT ] [ JOIN-BY-SQLDB | NO-JOIN-BY-SQLDB ] [ LOOKAHEAD | NO-LOOKAHEAD ] [ ORDERED-JOIN ] [ REVERSE-FROM ] [ SEPARATE-CONNECTION | NO-SEPARATE-CONNECTION ] } ) |
The following descriptions are general. For more detailed information, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server and OpenEdge Data Management: DataServer for Oracle).
The default is ARRAY-MESSAGE.
Specifies whether the DataServer uses ORACLE bind variables or literals in WHERE clauses. If you use NO-BIND-WHERE, the DataServer uses literals. Bind variables can improve performance, but ORACLE produces some unexpected results for some data types.
The default is BIND-WHERE.
The default is 1024 for standard cursors and 8192 for lookahead cursors.
If you use the byte option, the byte maximum is 65535 bytes and the byte minimum specifies the number of bytes contained in a single record. For joins, you must specify the number of bytes contained in two records.
If you use the row option, the row maximum equals the maximum number of records that can be fit in 65535 bytes. The row minimum is 1 row for a single table and 1 rows for a join.
The default is 30000.
The SQL option prints the SQL executed by the DataServer against the non-OpenEdge DBMS. The extended option prints additional information, such as cursor statistics. The information you get when you use the EXTENDED option can be helpful in setting your parameters.
The default is NO-DEBUG.
For more information, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server and OpenEdge Data Management: DataServer for Oracle).
Specifies the ORACLE hint syntax that the DataServer passes directly to the ORACLE DBMS as part of the query. This allows you to control which hints are passed as opposed to the index hints that the DataServer passes when appropriate.
Specifies whether the DataServer provides index hints to the ORACLE DBMS. INDEX-HINT places index hints in the generated SQL; NOINDEX-HINT prevents the use of index hints.
The default is INDEX-HINT.
The default is JOIN-BY-SQLDB.
The default is LOOKAHEAD, except with statements that use an EXCLUSIVE lock.
The following code fragment illustrates a QUERY-TUNING phrase in a FOR EACH statement. In this example, the DataServer uses lookahead cursors with a cache size of 32K and records debugging information:
For the DataServer for ORACLE, all options of the QUERY-TUNING phrase are effective at both compile and run time, except INDEX-HINT, NO-INDEX-HINT, JOIN-BY-SQLDB, and NO-JOIN-BY-SQLDB, which are only effective at compile time.
For more information on the QUERY-TUNING phrase, see the OpenEdge DataServer Guides (OpenEdge Data Management: DataServer for Microsoft SQL Server and OpenEdge Data Management: DataServer for Oracle).