The SET QUERY statement is used to control query execution within
a SQL session.
1 – Environment
You can use the SET QUERY statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
Note that some options for the SET QUERY command may only be used
in interactive SQL.
2 – Format
set-query =
--> SET QUERY -+-> CONFIRM ------------------------------------------------------------------+-->
+-> NOCONFIRM ------------------------------------------------------------------+
+-> LIMIT -+--> ROWS <rows_value> ----------------------+-----------------------+
| +--> TIME <time_value> -----+-+------------+-+ |
| +--> CPU TIME <time_value> -+ +-> SECONDS -+ |
| +-> MINUTES -+ |
+-> NOLIMIT -+--> ROWS -----+---------------------------------------------------+
| +--> TIME -----+ |
| +--> CPU TIME -+ |
+-> EXECUTION LIMIT -+-> CPU -----+---> TIME ---> <time_value> -+------------+--+
| +-> ELAPSED -+ +-> SECONDS -+ |
| +-> MINUTES -+ |
+-> EXECUTION NOLIMIT -+-> CPU -----+-> TIME -----------------------------------+
+-> ELAPSED -+
3 – Arguments
3.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.
3.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.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.
3.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.
3.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.
3.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.
3.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).
3.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).
4 – Examples
Example 1: Shows the syntax for establishing a row limit within
an interactive SQL session.
SQL> set query limit rows 10000;
SQL> show query limit;
Query limit Time is OFF
Query limit Row count is 10000 rows
Query limit CPU time is OFF
Execution limit CPU time is OFF
Execution limit Elapsed time is OFF
Execution limit Row count is OFF
SQL> set query nolimit rows;
SQL> show query limit;
Query limit Time is OFF
Query limit Row count is OFF
Query limit CPU time is OFF
Execution limit CPU time is OFF
Execution limit Elapsed time is OFF
Execution limit Row count is OFF
Example 2: Uses SET QUERY to establish a two second elapsed time
limit for a query, and shows the error message that is displayed.
SQL> set query execution limit elapsed time 2 seconds;
SQL> delete from EMPLOYEES;
%RDB-E-EXQUOTA, Oracle Rdb runtime quota exceeded
-RDMS-E-MAXTIMLIM, query governor maximum timeout has been reached
SQL> set query execution nolimit elapsed time;