SQL$HELP_OLD72.HLB  —  SET_QUERY, Arguments

1  –  CONFIRM

    Lets you preview the cost of a query, in terms of I/O, before any
    rows are actually returned. For example:

    SQL> SELECT * FROM EMPLOYEES;
    Estimate of query cost: 52 I/O s, rows to deliver: 100
    Do you wish to cancel this query (No)? YES
    %SQL-F-QUERYCAN, Query cancelled at user s request

    Some queries can result in Oracle Rdb performing a large number
    of I/O operations, retrieving a large number of rows, or both.
    The SET QUERY CONFIRM statement causes SQL to display estimated
    query costs. If the cost appears excessive, you can cancel the
    query by answering No; to continue, answer Yes.

    The SET QUERY CONFIRM statement is only available for interactive
    SQL.

2  –  EXECUTION_LIMIT

    This option imposes elapsed and CPU time limits on executing
    queries. This command affects all subsequent queries executed
    within the Rdb server process. You must be attached to a database
    to execute this statement. This statement affects all attaches
    for the current process, not just the current connection.

    o  CPU TIME time_value [ SECONDS | MINUTES ]

    o  ELAPSED TIME time_value [ SECONDS | MINUTES ]

    You can restrict the amount of elapsed time or CPU time used to
    execute a query. If the query is not complete before the elapsed
    or CPU time limit is reached, an error message is returned.

    The default is unlimited time for the query execution. If you
    omit the SECONDS and MINUTES keyword then SECONDS is the default.
    Dynamic SQL options are inherited from the compilation qualifier
    for the module.

                                   NOTE

       Specifying a query time limit can cause application failure
       in certain circumstances. For instance, an application that
       runs successfully during off-peak hours may fail when run
       during peak hours due to the load on the database.

    Use a positive integer for the number of seconds or minutes;
    negative integers are invalid and zero means no limits. If an
    established limit is exceeded, the query is canceled and an error
    message is displayed. When you set a CPU time limit, elapsed time
    limit and a row limit (using SET QUERY LIMIT), whichever value is
    reached first stops the query.

    Database administraors and application developers can use
    this feature to prevent users from overloading the system by
    executing long running, and probably unproductive queries. The
    database administrator can manage system performance and reduce
    unnecessary resource usage by setting option limits.

3  –  EXECUTION_NOLIMIT

    This option removes a limit imposed by the SET QUERY EXECUTION
    LIMIT command.

    Use one of the following options.

    o  ELAPSED TIME

    o  CPU TIME

    EXECUTION NOLIMIT is equivalent to assigning a limit of zero to
    any of the options using SET QUERY EXECUTION LIMIT.

4  –  LIMIT

    Sets limits to restrict the output generated by a query.

    The mechanism used to set these limits is called the query
    governor. The following gives you three ways to set limits using
    the query governor:

    o  ROWS rows_value

       You can restrict output by limiting the number of rows a query
       can return. The optimizer counts each row returned by the
       query and stops execution when the row limit is reached.

       The default is an unlimited number of row fetches. Dynamic SQL
       defaults are inherited from the compilation qualifier for the
       module.

    o  TIME time_value [ SECONDS | MINUTES ]

       You can restrict the amount of time used to optimize a query
       for execution. If the query is not optimized and prepared for
       execution before the total elapsed time limit is reached, an
       error message is returned.

       The default is unlimited time for the query compilation. If
       you omit the SECONDS and MINUTES keyword then SECONDS is the
       default.

                                      NOTE

          Specifying a query time limit can cause application
          failure in certain circumstances. For instance, an
          application that runs successfully during off-peak hours
          may fail when run during peak hours due to the load on
          the database.

    o  CPU TIME time_value [ SECONDS | MINUTES ]

       You can restrict the amount of CPU time used to optimize
       a query for execution. If the query is not optimized and
       prepared for execution before the CPU time limit is reached,
       an error message is returned.

       The default is unlimited CPU time for the query compilation.
       If you omit SECONDS and MINUTES keyword then SECONDS is
       the default. Dynamic SQL options are inherited from the
       compilation qualifier for the module.

    Use a positive integer for the number of rows and the number of
    seconds; negative integers are invalid and zero means no limits.
    If an established limit is exceeded, the query is canceled and
    an error message is displayed. When you set both a time limit and
    the row limit, whichever value is reached first stops the output.

    Application developers can use this feature to prevent users from
    overloading the system. The database administrator can manage
    system performance and reduce unnecessary resource usage by
    setting option limits.

5  –  NOCONFIRM

    Disables the query confirm dialog that was previously enabled
    using SET QUERY CONFIRM. The SET QUERY NOCONFIRM statement is
    only available for interactive SQL.

6  –  NOLIMIT

    This option removes a limit imposed by the SET QUERY LIMIT
    command.

    Use one of the following options.

    o  ROWS

    o  TIME

    o  CPU TIME

    NOLIMIT is equivalent to assigning a limit of zero to any of the
    options using SET QUERY LIMIT.

7  –  rows_value

    This argument represents the number of rows specified for the
    SET QUERY argument. It can be a numeric literal, a parameter name
    (for interactive SQL), or a parameter-marker (for dynamic SQL).

8  –  time_value

    This argument represents the number of seconds or minutes
    specified for the SET QUERY statement. It can be a numeric
    literal, a parameter name (for interactive SQL), or a parameter-
    market (for dynamic SQL).
Close Help