1 – EDIT_USING
Syntax options:
EDIT USING edit-string|EDIT USING domain-name
Associates an edit string with a value expression. This clause
overrides any EDIT STRING defined for the columns or variables in
the query. This clause is only permitted for interactive SQL.
2 – FOR_UPDATE_OF
Specifies the columns in a cursor that you or your program might
later modify with an UPDATE statement. The column names in the
FOR UPDATE clause must belong to a table or view named in the
FROM clause.
You do not have to specify the FOR UPDATE clause of the SELECT
statement to later modify rows using the UPDATE statement:
o If you do specify a FOR UPDATE clause with column names and
later specify columns in the UPDATE statement that are not
in the FOR UPDATE clause, SQL issues a warning message and
proceeds with the update modifications.
o If you do specify a FOR UPDATE clause but do not specify
any column names, you can update any column using the UPDATE
statement. SQL does not issue any messages.
o If you do not specify a FOR UPDATE clause, you can update
any column using the UPDATE statement. SQL does not issue any
messages.
The FOR UPDATE OF clause in a SELECT statement provides UPDATE
ONLY CURSOR semantics by locking all the rows selected.
3 – OPTIMIZE AS query name
Assigns a name to the query. You can define the RDMS$DEBUG_FLAGS
logical name or use SET FLAGS with the option 'STRATEGY' to see
the access methods used to produce the results of the query. The
following example shows how to use the OPTIMIZE AS clause:
SQL> DELETE FROM EMPLOYEES E
cont> WHERE EXISTS ( SELECT *
cont> FROM SALARY_HISTORY S
cont> WHERE S.EMPLOYEE_ID = E.EMPLOYEE_ID
cont> AND S.SALARY_AMOUNT > 75000)
cont> OPTIMIZE AS DEL_EMPLOYEE;
Leaf#01 FFirst RDB$RELATIONS Card=19
.
.
.
~Query Name : DEL_EMPLOYEE
.
.
.
7 rows deleted
4 – OPTIMIZE_FOR
Specifies the preferred optimizer strategy for statements that
specify a select expression. The following options are available:
o FAST FIRST
A query optimized for FAST FIRST returns data to the user as
quickly as possible, even at the expense of total throughput.
If a query can be cancelled prematurely, you should specify
FAST FIRST optimization. A good candidate for FAST FIRST
optimization is an interactive application that displays
groups of records to the user, where the user has the option
of aborting the query after the first few screens. For
example, singleton SELECT statements default to FAST FIRST
optimization.
If the optimization level is not explicitly set, FAST FIRST is
the default.
o TOTAL TIME
If your application runs in batch, accesses all the records in
the query, and performs updates or writes a report, you should
specify TOTAL TIME optimization. Most queries benefit from
TOTAL TIME optimization.
The following examples illustrate the DECLARE CURSOR syntax
for setting a preferred optimization mode:
SQL> DECLARE TEMP1 TABLE CURSOR
cont> FOR
cont> SELECT *
cont> FROM EMPLOYEES
cont> WHERE EMPLOYEE_ID > '00400'
cont> OPTIMIZE FOR FAST FIRST;
SQL> --
SQL> DECLARE TEMP2 TABLE CURSOR
cont> FOR
cont> SELECT LAST_NAME, FIRST_NAME
cont> FROM EMPLOYEES
cont> ORDER BY LAST_NAME
cont> OPTIMIZE FOR TOTAL TIME;
o SEQUENTIAL ACCESS
Forces the use of sequential access. This is particularly
valuable for tables that use the strict partitioning
functionality.
When the storage map of a table has the attribute PARTITIONING
IS NOT UPDATABLE, the mapping of data to a storage area is
strictly enforced. This is known as strict partitioning.
When queries on such tables use sequential access, the
optimizer can eliminate partitions which do not match the
WHERE restriction rather than scan every partition.
The following example shows a query that deletes selected rows
from a specific partition. This table also includes several
indexes, which may be chosen by the optimizer. Therefore, the
OPTIMIZE clause forces sequential access.
SQL> delete from PARTS_LOG
cont> where parts_id between 10000 and 20000
cont> and expire_date < :purge_date
cont> optimize for sequential access;
Note that all access performed by such queries will be
sequential. Care should be taken that the I/O being used is
acceptable by comparing similar queries using index access.
5 – OPTIMIZE USING outline name
Explicitly names the query outline to be used with the select
expression even if the outline ID for the select expression and
for the outline are different.
The following example is the query used to create an outline
named WOMENS_DEGREES:
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
By using the OPTIMIZE USING clause and specifying the WOMENS_
DEGREES outline, you can ensure that Oracle Rdb attempts to use
the WOMENS_DEGREES outline to execute a query even if the query
is slightly different as shown in the following example:
SQL> SELECT E.LAST_NAME, E.EMPLOYEE_ID, D.DEGREE, D.DEGREE_FIELD, D.YEAR_GIVEN
cont> FROM EMPLOYEES E, DEGREES D WHERE E.SEX = 'F'
cont> AND E.EMPLOYEE_ID = D.EMPLOYEE_ID
cont> ORDER BY LAST_NAME
cont> LIMIT TO 10 ROWS
cont> OPTIMIZE USING WOMENS_DEGREES;
~S: Outline WOMENS_DEGREES used <-- the query uses the WOMENS_DEGREES outline
.
.
.
E.LAST_NAME E.EMPLOYEE_ID D.DEGREE D.DEGREE_FIELD D.YEAR_GIVEN
Boyd 00244 MA Elect. Engrg. 1982
Boyd 00244 PhD Applied Math 1979
Brown 00287 BA Arts 1982
Brown 00287 MA Applied Math 1979
Clarke 00188 BA Arts 1983
Clarke 00188 MA Applied Math 1976
Clarke 00196 BA Arts 1978
Clinton 00235 MA Applied Math 1975
Clinton 00201 BA Arts 1973
Clinton 00201 MA Applied Math 1978
10 rows selected
See the CREATE OUTLINE statement for more information on creating
an outline.
6 – OPTIMIZE_WITH
Selects one of three optimization controls: DEFAULT (as used by
previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller
numbers of rows will be selected), and SAMPLED (which uses
literals in the query to perform preliminary estimation on
indices).
7 – select-expr
See the Select_Expressions HELP topic for a detailed description
of select expressions.